Create a Function similar to multiple IFs

[Andy]

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

[pinmaster]

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

[Andy]

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

[pinmaster]

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

[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..