IF statement can't return a NAMED RANGE
Richard Palarea
|
| |
|
Band 2 |
|
| Next Day Air | 8.00% |
| Next Day Saver | 8.00% |
| Ground 1 lb - 10 lb | 6.00% |
| Ground 11 lb - 20 lb | 10.70% |
| Ground 20 lb - 70 lb | 14.80% |
| Ground 71 lb and over | 6.00% |
Ron Rosenfeld
<rpal...@comcast.net> wrote:
>I have an IF statement that returns the values within a multi-column/multi-row named range if true. The way that the statement is written, I'm getting a #VALUE error when true.
>
>I'm sure it has to do with some limitation Excel has with returning a named range that is greater than one cell into a location where the function is within one cell.
>
>I've copied the statement syntax and an example of one of the possible named ranges that can be returned. Is there a way to do this or another function that can use the named range the way I have it?
Your named range is called an 'array' in XL. In order to return an array,
you must enter the formula as an array formula. To do so, hold down
<ctrl><shift> while hitting <enter>. XL will place curly brackets {...}
around the formula.
When you do this, with a formula like yours which returns multiple items,
you will only see the first item in the array: "Next Day Air" in this
case. However, the contents of the cell is really an array constant which
would look like:
{"Next Day Air",0.08;"Next Day Saver",0.08;"Ground 1 lb - 10
lb",0.06;"Ground 11 lb - 20 lb",0.107;"Ground 20 lb - 70 lb",0.148;"Ground
71 lb and over",0.06}
Commas separate the columns and semicolons separate the rows.
What are you trying to do?
If you are trying to get prices based on inputs, a lookup table would
probably be a more efficient way to do this.
--ron
Richard Palarea
First, thanks for the quick response!
Second, I'm trying to return one of four arrays, based on another formula
that is doing the lookup. The "band" values are just sitting inside of a
hidden set of rows and columns and really are just being returned as a help
to the report reader verify that the actual data is, indeed, meeting a
certain service level.
The percentages in the "band" named field are the service levels. Rather
than verify every record for meeting a presecribed service level, we're
taking the data as an aggregate and verifying that the service levels fall
within a "band".
Kind of hard to explain, but does that help? I'm going to try the array
formula you suggested. Thanks!
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:maaq5vsc6lj4u6qq0...@4ax.com...
Todd
I would try several things first, since the error is only
occuring with the If true, check to make sure the
formatting is consistant through out the cells where your
formula is pulling from.
Next, Use the following If(Trim(C19)=Trim(Band 2)",Band2,""
This will compare to make sure you do not have any spaces,
The other thing I noticed is you have 2 different named
ranges Band 2 and Band2,
You are not recieving a #REF message so the formula is
problaly able to recognize the named range,
Maybe you might also want to use a VLOOKUP to accomplish
your goal.
Todd
MOUS(EXCEL)(ACCESS)
Richard Palarea
In this case, cell C19 is actually holding a value that is coming from a
lookup. The value can be Band 2 (band space 2), Band 3, Band 4 or Band 5.
The named ranges are Band2 (no space), Band3, Band4, Band5.
The problem seems to be coming in where Excel can't return a named range
that is more than one cell. I want to bring in that block of labels and
values (at the bottom of this message) as some reference text for the
reader. Although I've put the text into columns and rows, it doesn't need to
be since it is only for viewing and not for calculations. I used columns and
rows because it was easy to keep things looking neat.
The block of labels and values needs to change, based on the value of cell
C19 so that the reader is seeing the appropriate reference.
I tried the array formula, but it didn't seem to do the trick. It calculated
fine without presenting a #VALUE error, but I wanted the result to be the
entire block of text located within that range to be displayed. It only
displayed the top/left value.
Hmmmmm.....
Thanks,
Rich
"Todd" <Todd...@aol.com> wrote in message
news:06b301c2dddc$9bafd290$3001...@phx.gbl...
Ron Rosenfeld
<rpal...@comcast.net> wrote:
>First, thanks for the quick response!
>
>Second, I'm trying to return one of four arrays, based on another formula
>that is doing the lookup. The "band" values are just sitting inside of a
>hidden set of rows and columns and really are just being returned as a help
>to the report reader verify that the actual data is, indeed, meeting a
>certain service level.
>
>The percentages in the "band" named field are the service levels. Rather
>than verify every record for meeting a presecribed service level, we're
>taking the data as an aggregate and verifying that the service levels fall
>within a "band".
>
>Kind of hard to explain, but does that help? I'm going to try the array
>formula you suggested. Thanks!
I'm not sure I understand entirely. It sounds like what you want to do is
merely display the contents of the named range 'bandn' for someone to look
at?
If you can do it in multiple cells, that's fairly easy, especially if all
of the ranges are the same size.
1. Select the 2 x 6 range where you want the "Band" to appear.
2. Array-enter the formula =IF(C19="Band 2",Band2)
The formula will be entered simultaneously into all of the cells, and will
display the results of the Band table.
If the arrays are of different sizes, you will get #N/A errors in the cells
outside of the particular band's range. I think the simplest method of
handling this would be with conditional formatting, to turn error messages
to a white (or background) color. Let me know if that is an issue.
If using multiple cells for the display is not an option, then you could
display the result in a text box -- but this will require programming in
VBA. Let me know if this is required, and I'll try to help.
--ron
Richard Palarea
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message
news:s2vq5v4i5asrcogk7...@4ax.com...