Create a Function similar to multiple IFs
 

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

Create a Function similar to multiple IFs
 

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


"Andy" wrote:

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

Create a Function similar to multiple IFs
 

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

"pinmaster" wrote:

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


"Andy" wrote:

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

Create a Function similar to multiple IFs
 

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

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

Regards
Jean-Guy

"Andy" wrote:

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