Adding data arrays to existing workbook 2

[SacreBleu]

Is the following possible without using VB or macros?

Suppose we have a data array, for example data in the range of cells A1200. This range is named as RAN1. In the workbook, there are a large number of formulae which refer to RAN1, for the purposes of data lookup.

Now suppose we find the need to add 10 more data arrays. Each is of the same size of RAN1; they merely have different data. We can easily set up the 10 additional arrays each below the original RAN1.

Now, in various cells of the workbook, we have the necessity to add the capability of selecting the correct data array (to make this simple, we have a formula which can calculate a final value of 1 thru 11).

How is it possible to accomplish this easily/efficiently? I am trying to avoid the necessity of selectivley updating each cell (there are very many) with this new requirement of picking data from one of 11 arrays instead of just one.

 

[cummings54]

Consider the function OFFSET. For example,
=OFFSET(A1200,199,0) refers to range A201400

It can be used in any function that takes a range as an argument. For example,

=VLOOKUP(E1,OFFSET(A1200,199,0),3) etc.

 

[pmover]

seems like a suitable application for what are called dynamic arrays. please goto the website:

http://www.ozgrid.com/Excel/default.htm

and investigate the dynamic array writeup and example.

hope this helps!
good luck!
-pmover

 

[SacreBleu]

cummings54,
Is that really OFFSET function? The one I am familiar with is in the format (for example): OFFSET(G20,A1,B1)
Where A1 is the "starting point"
A1 contains a numerical value equal to how many rows up or down from cell G20
B1 contains a numerical value equal to how many colums to the left or right from cell G20.

just wondering....

 

[SacreBleu]

OOPS, <retracting foot from mouth>....now I get it. Never mind.

 

[cummings54]

Here's a little clarifaction about the OFFSET function.

When it takes a single cell reference as its starting point argument it returns a single cell reference. When the starting point argument is a multicell range, it returns a multicell range of the same shape as the starting point argument.

The trick is to remember that in the second form, offset returns an array. If you simple enter

=OFFSET(A1200,199,0)

in a cell. It will return a #VALUE error. If you enter it as an array, first selecting cells with the same dimension as the source array (ie 4 x 100), and using the Ctrl-Shift-Enter key combination, you will see the target array.

 

[SacreBleu]

cummings,
Thanks for the info. I was in a design crunch mode, and haven't had time to digest this yet. I will keep you and pmover posted when I get back to the spreadsheet mode.

 

[bltseattle]

I would try the INDIRECT function. It could work for you like I show below. INDIRECT can provide a "pointer" to the correct array, as illustrated below. It is better than offset in a way because if you move your data arrays you do not have to recount how many rows to OFFSET, since you are referring to each array by its name not its physical location on the worksheet.

say that cell C1 has the name of the array (RAN1, or RAN2, or RAN3, etc). You could name cell C1 "arrayname".

Now change your lookup function something like this:

old version:
value = VLOOKUP(value, RAN1, 3)

new version:
value = VLOOKUP (value, INDIRECT(arrayname), 3)


I have not tested this exactly but use the same principle to change rainfall distributions in my hydrologic model. You could use the Data>validation function on cell C1 to only list valid array names, as an extra touch.

 

[SacreBleu]

bltseattle,
Thanks - I finally had the time to dial this in.