Multiple Selection of Non-Continuous Data

[bgetson]

I posted this earlier in the programming group, but I think that this
is the right place.

I am attempting to create a named range for a graph to display data
from the end of each month. Ideally, the data for the range would come
from the following set:

A Jan Feb Mar Apr
B g2 g8 g12 #N/A

where each cell in row B contains a cell reference for the date of the
last workday of a month. For months that have not occured yet, the
formula I created returns the #N/A error. My problem is creating the
named range that uses the INDIRECT() function to read in each cell
reference that exists.

The data needs to remain a cell reference (instead of the value it is
referring to) so that I can later use an OFFSET() function on the named
range to grab the performance values for each month.

Any comments or suggestions would be greatly appreciated.
- BGetson

 

[vezerid]

I don;t know if this will help you. The union operator is the comma.
Thus you can refer to a non-contiguous range by (A2,B5,G8). But I don't
know if this can be put to use for what you have in mind.

Have you considered using another range of cells with the function
INDIRECT(B2) copied to the right? Then you can base your chart on it.

Maybe you can post in .charting.

HTH
Kostis Vezerides

 

[bgetson]

My main problem is creating a base function that can recognize if I
receive the #N/A error. As you mentioned, the solution might be
creating additional rows to only hold the data that I want/exists, but
I don't know how I would get that to work.

I guess in pseudocode (or pseudo-function) I want the named range to
look something like:

for B2:B5
=IF B2 <> #N/A, INDIRECT(B2)

I tried using something like this earlier using the union operator for
each cell I wanted to analyze (12), but it became a mess and was unable
to figure out where excel was giving me an error.

Also, I posted here instead of in .charting because I'm having trouble
with the worksheet function aspect of my problem.

I hope that this might clear up my issue.

-BGetson

 

[vezerid]

The formula to test if a cell contains #N/A is:

=IF(ISNA(B2),"",INDIRECT(B2))

Bear in mind that, for charting, #N/A might be very useful because it
is ignored and not plotted. Therefore you might want to keep it.

HTH
Kostis