×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Adding data arrays to existing workbook
2

Adding data arrays to existing workbook

Adding data arrays to existing workbook

(OP)
Is the following possible without using VB or macros?

Suppose we have a data array, for example data in the range of cells A1:D200. 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.
 

RE: Adding data arrays to existing workbook

Consider the function OFFSET.  For example,
=OFFSET(A1:D200,199,0) refers to range A201:D400

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

=VLOOKUP(E1,OFFSET(A1:D200,199,0),3) etc.

RE: Adding data arrays to existing workbook

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

RE: Adding data arrays to existing workbook

(OP)
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....

RE: Adding data arrays to existing workbook

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

RE: Adding data arrays to existing workbook

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(A1:D200,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.

RE: Adding data arrays to existing workbook

(OP)
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.

RE: Adding data arrays to existing workbook

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.

RE: Adding data arrays to existing workbook

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources