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

"PatK" wrote:

Ok...think I figured it out:

=INDEX(Factors!C47:BJ47,MATCH($C$1,Factors!C45:BJ4 5,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

"Pete_UK" wrote:

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 wrote:
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