LEN returning strange result
I getting an unexpected result when using the following formula, I'm
receiving #VALUE when the formula in J5 returns nothing (the formula in J5
uses "" double quotes to stop an error being shown).
What formula would return nothing i.e. the "" double quotes would come into
Watch your parentheses:
I'd do this, too, but it doesn't matter to excel:
I just find it less confusing.
> ...What formula would give a value of a last negative value in a range?
What formula would give a value of a last negative value in a range? I want
to calculate a pay-back period from a cumulative cash-flow or a cash-flow.
one way: use the following array formula (entered with
>What formula would give a value of a last negative value
in a range? I want
>to calculate a pay-back period from a cumulative cash-
flow or a cash-flow.
Excellent, thank you so much, Frank
"Frank Kabel" <frank.kabel@fr...Sort by Large, Offset from Match, Duplicate values problem
I have a large list of dynamic data, which I am organizing in different
ways. I realize this can all be done in PivotTables, but we have to do this
the olde fashioned way. The table I am drawing the info from looks roughly
Player Buy-In Cash-Out Difference # Times entered
Joe SHMOE $930 $900 $30 1
Sam DOG $0 $0 $0 0
Peter BLAH $100 $1000 $900 22
Doug FINN $500 $0 $500 22
I am using =LARGE, =SMALL on the columns to sort the data, and then MATCH...Named range row/column reference #2
thanks. understood, and that does work well.
I would still like to be able to reference within named ranges usin
offsets from the beginning of the named range. Once I have the offset
they would serve as references into other named ranges of a simila
*Is there a way to get vStudentNbr and vTestWeek as relative reference
to the beginning of their respective named ranges? *
(yes I am a VBA newbie!! and I am not yet writing the most efficien
code LOL :)
Urklnme's Profile: http://www.e...up down bar value (difference)
I have a line chart with two lines. I have included down bars from the upper
line. I would like the difference of value between the two lines (i.e. the
height of the down bars) to be shown within the down bars.
Is this possible?
one way to do it, (If I understand what you want) on the spread sheet
calculate the difference in say D1:D4
on the chart start a text box and in the formula bar enter = and point to
the cell with the difference.
format the text box for the alignment and fill colors you want and place it
down bar for each data point pair.
Can this be shown in the Internet Headers of Exch 5.5 Server clients ? If so how can it be enabled ?
When you say Exch 5.5 server clients, you mean Outlook? View > Options will
display this header.
"RichieRich" <firstname.lastname@example.org> wrote in message
> Can this be shown in the Internet Headers of Exch 5.5 Server clients ? If
so how can it be enabled ?
That information is inserted by sending side (it's mail client),
Exchange does not control that.
> Yes it...Sum values that precede todays date
I am trying to find a way to sum the values of payments made to date?
So if it was today it would sum all values preceding the figure for July?
Is there an easy way to do this using a formula? I have tried using the
offset and match formula but then i can't get it to sum all previous payments.
Many thanks for your help
(the...insert all column(x) values in a single textbox
Hi to everyone,
does anyone to know how can I insert in an unbounded texbox, separated
from a ";" ALL the values coming from the column(x) of a combobox?
On my form I have a combobox with its row source to a multicolumn
query and it's bounded with an ID field.
What I have to be able to do is that:
to insert for each row, with same ID, of the combo the value with
respect to its (x) column.
Hi hope to have been clearly...
Many thanks, Stefano.
On 13 Feb, 08:08, ricc...@tin.it wrote:
> Hi to everyone,
> does anyone to know how can I insert in an unbounded texbox, separated
&...how to add a cell value to a letter to reference another cell
Is it possible to take the value of a cell (say the value of 83 that's
in cell A1) and append it to a letter (Z) so that a subsequent formula
can get a value that's in Z83. Hope this make's sense. Thanks in
advance for the help!
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
"Mac Macaroo" <Mac.Macaroo.email@example.com> wrote in message news:Mac.Macaroo.vo1rn...clearing values of cells in named range(s) so the cells are empty
I am working on project where i paste data-set into a worksheet
(sheet1) , do several calculations and put the results specific cells
on sheet2. I am using named ranges for the cells in sheet2 that
receive the data. WHat i am trying to figure out is a way to set the
cells in the named ranges on sheet2 to 0 or "" before starting the
routine that does all the calculaions. This way I can be sure that the
data on sheet2 belongs to current set of calculations. Thank you in
advance for any advise. BRC
Assuming one of the named ranges is name SomeRangeName, then this state...Changing formula to value/result
How do I go about changing the cell formula so that it becomes the
result of the formula. Thank you!
sutats's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24565
View this thread: http://www.excelforum.com/showthread.php?threadid=387125
Here are 2 ways to convert formulas to their values:
For 1 cell:
Select the cell, then...
Press [F2] (to edit)
Press [F9] (to convert to the value)
Press [Enter] (to commmit the value)
Or (for multiple cells)
Select the cells, ...Allow for user-defined labels to be changed in Customer Addresses
In Customer Address Maintenance, I would like to change the labels for the
user defined fields using a setup and not using modifier.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default....VBA sort range with certain fontcolor
Ik column K there will be a continuous range of which all cells have a
certain fontcolor, the font color code is, say, 5. The range can be 1 cell up
to 30 (or so), perhaps even zero. There are no cells in column K outside that
range that also have font color code 5.
While executing the code it is not (yet) known what the rownumbers of the
first and last cells in that range are.
I need code that will sort (descending) the following range:
from row of first cell (in column K) with font color code 5 and column A
row of last cell (in column K) with font color code 5...How to locate differences between two sheets/ranges?
Well, here's my problem: each day here at work I receive a file with updated info on specific data. Form is always the same -specific number of columns with the same type of data, only the number of rows may change. However, every day I have to go through the data to see what has been changed and/or what has been added/dropped.
Is there any way to do this easily?
Have a look here for some ideas. I'm sure one of them will suit:
"Gryzor" <Gryzor@discussions.microsoft.com> wrote in message
news:...Hide Rows or Columns with +/- button above row/column label headin
on some excel files, I have seen a +/- button located between the toolbar
and the cell label headings. The button hides/reveals rows or columns. How do
I add this button for rows or columns of my choice?
Thanks for your help.
Data>Group and Outline.
Gord Dibben MS Excel MVP
On Wed, 18 Nov 2009 14:16:01 -0800, WA <WA@discussions.microsoft.com> wrote:
>on some excel files, I have seen a +/- button located between the toolbar
>and the cell label headings. The button hides/reveals rows or columns. How do
>I add this button for ...Click Image and call label Click event
I have a menu form with labels and images. I have highlight and unHighlight
code functioning fine (switching images, fonts, etc). All of my labels have
_click() events that are currently the standard Private Sub cmdName_click()
events that all work correctly. I created a function Function ImageClick
(cmdName as string). For each Image, the Click event calls the function
=ImageClick("cmdName"). So, when you click the image, it sends the related
cmdName and should run its click event. I cannot get it to work. Here is an
example of one image and label scenario.
Label nam...formula to return value from a matrix
Filtering is not an option :)
I have a table that shows skill titles across the columns e3:cz3 and staff
names down c4:c103, for each name and skill there will be a value between 1 &
4 see example below
I need to insert the finding into a bespoke report that if I select a skill
(from a drop down list) the report will list the names and associated value
Skill title drop down is k5
name is k8, skill value is j8
Cheers in advance
Indicatively, perhaps a basic Index(area, match(row),match(col))
I have 2 tables with exactly the same fields and the same number of
Every day those 2 tables (updated) have to match. How can I proceed to
ensure myself that there is no difference?
When defining a numeric key, it appears that there are some
differences, even as both records appear to be equal.
Can someone tell how to proceed when I want to maintain this numeric
value as a valuable criteria to investigate if there are any
There is seldom a good reason to have the same data in two different tables.
But assuming that you have one, here goes.
Fi...PLEASE HELP??? copy every other cell value to a range of cells
My table data
Qty1 10 10
Qty2 1 2
Balance 9 17
Qty1 10 10
Qty2 5 2
Balance 5 13
I am trying to copy and paste a balance formula for each customer group. The
formula is basically Qty1 (Previous cell) - Qty2(Previous cell) ...setting value for combo boxes
i have a combo box bound to a table that when 'not in list' opens a
form to add a new entry to the bound table. once the user adds an
entry to the bound table via the newly opened form, i'd like this
value to populate the original form control, which is still open. and
not to get to greedy, but i was hoping it could be global in that i'd
like one code or macro so i don't have to do it for every combo box in
any help is appreciated
On Feb 23, 8:52 pm, "lefty" <richardpo...@gmail.com> wrote:
> i have a combo box bound to a table th...Enter "Parameter Value"
AccessXP on WinXP
Attempting to update a series of records from a LINKED table. The following
SQL statement results from the design view settings to replace classnumber
field in the datainport table with the ClassID filed contents in teh linked
table tblClasses. This returns a Enter PArameter VAlue dialog box. I am
accustomed to getting this dialog when I have misspelled a variable name, but
I used the list of tables and fields to verify that I have the correct
What suggestions can you offer to the sql below?
UPDATE dataimport1 SET dataimport1.[Class Number] = ([tblCl...Can you change the length of an excel chart data label?
The label is cutting off the number and I will need to change the length or
scrap the label all together if the functionality is not available.
Thanks for your help!
A know bug. Some people have this problem other do not. Workaround: add some
dots to the end of the title then format the dots to be invisible (in case
others view the chart and do not have the 'bug') by making the font colour
match the label's background colour (generally white)
Bernard V Liengme
remove caps from email
"Alia" <Alia@discussions.microsoft.c...how do I attach files but not within the document
I want to atach severeal files to a report. I don't want them within the
report but want to reference them so the reader can see them. Any help?
Have you considered using hyperlinks?
Microsoft Word MVP
"DLW" <DLW@discussions.microsoft.com> wrote in message
>I want to atach severeal files to a report. I don't want them within the
> report but want to reference them so the reader can see them. Any help?
...convert formula to its value
I have column made up of formulas that I want to convert
the formula to it's value. Is this an option in Excel
Copy the range, then edit, paste special, values.
"Andrea" <firstname.lastname@example.org> wrote in message
>I have column made up of formulas that I want to convert
> the formula to it's value. Is this an option in Excel
Highlight the range of cell, select Copy.
From the Edit menu, select Paste Special. Choose Values.
Rob van Gelder - htt...Need to make a drop down list of 3 items that input a value in ano
I have a savings calculator that i am working on. we have three products
that i would like the user to be able to select from. basis their selection
from the drop down list, i would like a corresponding value to go into a
how do i do this???
To create the dropdown list:
- select the cell where you want it to appear
- select Validation from the Data menu
- choose List
- you can either type in the list of items, separated by commas, or use the
grid button next to the textbox to select a range of cells that has the list
- click OK
To populate the other c...