Create a Function similar to multiple IFs

[Guest]

Hi guys,

I was hoping someone with some basic macro writing can urgently help me.

I have about 15 identical tables on individual worksheets each with
different data.
I also have a 16th worksheet with a similar empty table.

I would like a function that lets me select a worksheet name from a dropdown
list and that will make that sheets data appear in the 16th sheet.

In basic terms it would say something like:

If A1='Red Sheet' then the current cell this function is in will equal the
same cell in 'Red Worksheet'
If A1='Blue Sheet' then the current cell this function is in will equal the
same cell in 'Blue Worksheet'
If A1='Green Sheet' then the current cell this function is in will equal the
same cell in 'Green Worksheet' etc etc etc.

I cant use pivot tables for this, and the 16th sheet feeds various graphs
and stuff.

Can anyone help me?

Andy

 

[Guest]

Try something ilke this:

=INDIRECT("'"&$A$1&"'!"&CHAR(64+COLUMN(A1))&ROW(1:1))
copied down and across
where
A1 being the cell with the drop down list of sheet names and
A1 is the first cell of your tables (change the formula to reflect the
actual column and row of the first cell)
CHAR(64+COLUMN(A1))&ROW(1:1) is equal to "A1"
CHAR(64+COLUMN(B1))&ROW(1:1) is equal to "B1"
CHAR(64+COLUMN(B1))&ROW(2:2) is equal to "B2" ...etc

HTH
Jean-Guy

 

[Guest]

Can you explain to me how this works? As Ive tried look up the help file, but
am still unsure how it would link the dropdown menu to changing which
worksheets it reads from.

Andy

 

[Guest]

Have a look here for a better understanding of the INDIRECT formula.

http://www.cpearson.com/excel/indirect.htm

Regards
Jean-Guy

 

[steven1001]

If the columns in each of your worksheets have the same headings, can
you put all the data in one worksheet (or database table) and add an
extra column populated with values like 'red sheet', 'blue sheet' or
some logical equivalent so that you can select 'red sheet' in a
dropdown filter in the pivot table? Then your graphs should still
function.

regards..