Dynamically Changing HLOOKUP table

[PatK]

I have a set of 5 tables with values, by month (12 columns, but single row),
one each for 5 fiscal years, or 60 columns (cells), total (on worksheet A).

I also have a dropdown validation box (seperate cell) from which the
worksheet user can select one of the 5 fiscal years (one worksheet B).

I have an area on the Worksheet B that I would like to populated with the
entire table content (all 12 columns) for the selected fiscal year, based
upon which fiscal year the user selected from the drop down.

Any "simple" way to do this? I am thinking I have to dynamically point to a
different cell reference, and if I can get just the first one (month 1), and
can increment each subsequent cell reference by "1", I can make this work
somehow.

Just to keep this a simpler to understand request, consider the 12 columns
for each year simply "Jan-07, Feb-07, Mar-07, Apr07, ....Dec-07". The second
set of 12 columns is "Jan-08, Feb-08, Mar-08......Dec-08". And so on, for 5
years.

If the user selects FY07 (fiscal year 07), I want to change all the column
headings on that spreadsheet to the appropriate headings. Now, I know there
are easier ways to do just this, but once I know how to do this, I "also"
need to take other data from the fiscal year table and populate dozens of
rows below the headings...so it is not a solution that a function that
calculates MMM-YY headings will solve, but I figure if I can get it to work
for the headings, I can get it to work for the data beneath it, as well.

Ideas?

Thanks! pat

 

[Pete_UK]

I think you will be able to use an INDEX function here. Assume the data
for FY07 happens to be in row 7 - that tells you where to look in your
table, and the 12 columns moving across just increment.

Look it up in Excel help - it's described quite well, but come back if
you have any difficulties using it.

Hope this helps.

Pete

 

[PatK]

Yes, thanks. I had found the index function and was attempting to comprehend
what it is doing. It seems to me that I could zero in on the first item, but
then, I am not sure how to increment the index to move across the rows,
relative to the first cell value. That is the part I am stuck on. In fact,
the Hlookup gives me the first instance. Each column in the table has FY07
in the first row cell....directly beneath it another cell with Jan-07, then
you shift over a column, and the first row again says FY07, but beneath it it
says Feb-07. I am trying to get at the MMM-YY value, after finding the
location if the fiscal year, in the first row of the table.

I will try and go back and interpret the MS Index function instructions
again....I am feeling a little mentally dyslexic trying to interpret it.

Thanks!

pat

 

[PatK]

Ok...think I figured it out:

=INDEX(Factors!C47:BJ47,MATCH($C$1,Factors!C45:BJ45,0))

Factors is the worksheet with all the tables. The Match function returns
the position in the array of the first occurence of the FYxx value I am
looking for. I just copy this function out for the next 11 cells to get the
incremental cells. I gotta say....still a little voodoo going on here I do
not comprehend, but I will stare at it for a bit and figure it out.
Anyway...appreciate the pointer!!!

Pat

 

[PatK]

I take it back...spoke too soon. It works fine for the first fiscal year, but
then, when I change the value of the fiscal year in $C$1, to the next
year...it fails. Oh well...back to the drawing board. I think I will keep
staring at it and figure it out.

Pat