Dynamic file.......how to adjust formula to accomodate dynamic file

**welchs101** · 05-26-2011, 11:11 AM

I have a file of dates. I import this data into my file once a month. So one month i may have 1000 rows of dates the next I may have 25 rows of dates.

Regardless of the # of rows i have this formula on the 2nd sheet which I would like for it to update automatically if possible.

(see enclosed file)

Things to keep in mind:
Note: Data on "Raw Data sheet " will change. The amount of rows will change. One month maybe more than 182 other months may be less.

Note: Column name shown in col-A will stay the same but may not ALWAYS BE IN COL A. It could be in col K.

**NBVC** · 05-26-2011, 11:17 AM

Try:

Please Login or Register  to view this content.

**welchs101** · 05-26-2011, 11:54 AM

wow.........trying to follow this equation is kinda hard.

Can you walk me through this part of the equation:

Please Login or Register  to view this content.

Index(array, row# , col#)

array = Raw_Data!$1:$1048576.........confused about this....what area/array is this really? Dont you need to specify or know the column information in the area/array?

row#=0

col#=basically the col # that matches the "Raw_data_Date_field". I understand this part.

can you help me understand this a little more.......it seems to work.

**NBVC** · 05-26-2011, 12:05 PM

So we're indexing the whole sheet, since you don't know what column the header might occur in (you can limit it to less columns and/or rows if a limit is known).

Not sure if you know about INDEX/MATCH syntax and what it is used for. This site explains it well: Index/Match but basically it is used as a lookup, much like VLOOKUP except you have more flexibility and can match in 2 directions (i.e. a combination of VLOOKUP and HLOOKUP to find an intersection).

INDEX itself is a very powerful and multi-faced function. See here for more on that: The Imposing INDEX. In that article you will find that using a 0 as the Row argument of the INDEX function, returns the entire column and same goes if you use 0 for the Column argument, then you get the whole row returned.

So we've indexed the whole sheet, then we entered a 0 for the Row argument, meaning we are looking to return a whole column. Then for the column argument we use function: MATCH("Raw_data_Date_field",Raw_Data!$1:$1,0) this looks for a Match to the header title in row 1 of Raw_Data and returns the position (horizontal)...

So when all put together, INDEX(Raw_Data!$1:$1048576,0,MATCH("Raw_data_Date_field",Raw_Data!$1:$1,0)) indexes the sheet, and returns the whole column where the header is found in row 1.

Hope that all helps. I am not the best at explaining it all.. it gets complicated as you will note in the articles.

**welchs101** · 05-26-2011, 01:05 PM

I think you explained it really well. thanks.

I did have a question:
I would have thought that to select entire sheet you would have to do something like this
A1:XFD1048576.
By not specifying the cols (ie....1:1048576) it looks like you just specifying rows and this is what confused me.

**NBVC** · 05-26-2011, 01:10 PM

If you select all the Columns, Excel automatically converts to all of the rows (which is really the same thing).... I just clicked the grey square intersecting the column and row headers, and that is what Excel returned as the "whole sheet"