software

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
Try something ilke this:

=INDIRECT(quot;'quot;amp;$A$1amp;quot;'!quot;amp;CHAR(64 COLUMN(A1))amp;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))amp;ROW(1:1) is equal to quot;A1quot;
CHAR(64 COLUMN(B1))amp;ROW(1:1) is equal to quot;B1quot;
CHAR(64 COLUMN(B1))amp;ROW(2:2) is equal to quot;B2quot; ...etc

HTH
Jean-Guyquot;Andyquot; wrote:

gt; Hi guys,
gt;
gt; I was hoping someone with some basic macro writing can urgently help me.
gt;
gt; I have about 15 identical tables on individual worksheets each with
gt; different data.
gt; I also have a 16th worksheet with a similar empty table.
gt;
gt; I would like a function that lets me select a worksheet name from a dropdown
gt; list and that will make that sheets data appear in the 16th sheet.
gt;
gt; In basic terms it would say something like:
gt;
gt; If A1='Red Sheet' then the current cell this function is in will equal the
gt; same cell in 'Red Worksheet'
gt; If A1='Blue Sheet' then the current cell this function is in will equal the
gt; same cell in 'Blue Worksheet'
gt; If A1='Green Sheet' then the current cell this function is in will equal the
gt; same cell in 'Green Worksheet' etc etc etc.
gt;
gt; I cant use pivot tables for this, and the 16th sheet feeds various graphs
gt; and stuff.
gt;
gt; Can anyone help me?
gt;
gt; Andy
gt;
gt;

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

quot;pinmasterquot; wrote:

gt; Try something ilke this:
gt;
gt; =INDIRECT(quot;'quot;amp;$A$1amp;quot;'!quot;amp;CHAR(64 COLUMN(A1))amp;ROW(1: 1))
gt; copied down and across
gt; where
gt; A1 being the cell with the drop down list of sheet names and
gt; A1 is the first cell of your tables (change the formula to reflect the
gt; actual column and row of the first cell)
gt; CHAR(64 COLUMN(A1))amp;ROW(1:1) is equal to quot;A1quot;
gt; CHAR(64 COLUMN(B1))amp;ROW(1:1) is equal to quot;B1quot;
gt; CHAR(64 COLUMN(B1))amp;ROW(2:2) is equal to quot;B2quot; ...etc
gt;
gt; HTH
gt; Jean-Guy
gt;
gt;
gt; quot;Andyquot; wrote:
gt;
gt; gt; Hi guys,
gt; gt;
gt; gt; I was hoping someone with some basic macro writing can urgently help me.
gt; gt;
gt; gt; I have about 15 identical tables on individual worksheets each with
gt; gt; different data.
gt; gt; I also have a 16th worksheet with a similar empty table.
gt; gt;
gt; gt; I would like a function that lets me select a worksheet name from a dropdown
gt; gt; list and that will make that sheets data appear in the 16th sheet.
gt; gt;
gt; gt; In basic terms it would say something like:
gt; gt;
gt; gt; If A1='Red Sheet' then the current cell this function is in will equal the
gt; gt; same cell in 'Red Worksheet'
gt; gt; If A1='Blue Sheet' then the current cell this function is in will equal the
gt; gt; same cell in 'Blue Worksheet'
gt; gt; If A1='Green Sheet' then the current cell this function is in will equal the
gt; gt; same cell in 'Green Worksheet' etc etc etc.
gt; gt;
gt; gt; I cant use pivot tables for this, and the 16th sheet feeds various graphs
gt; gt; and stuff.
gt; gt;
gt; gt; Can anyone help me?
gt; gt;
gt; gt; Andy
gt; gt;
gt; gt;

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

www.cpearson.com/excel/indirect.htm

Regards
Jean-Guy

quot;Andyquot; wrote:

gt; Hi guys,
gt;
gt; I was hoping someone with some basic macro writing can urgently help me.
gt;
gt; I have about 15 identical tables on individual worksheets each with
gt; different data.
gt; I also have a 16th worksheet with a similar empty table.
gt;
gt; I would like a function that lets me select a worksheet name from a dropdown
gt; list and that will make that sheets data appear in the 16th sheet.
gt;
gt; In basic terms it would say something like:
gt;
gt; If A1='Red Sheet' then the current cell this function is in will equal the
gt; same cell in 'Red Worksheet'
gt; If A1='Blue Sheet' then the current cell this function is in will equal the
gt; same cell in 'Blue Worksheet'
gt; If A1='Green Sheet' then the current cell this function is in will equal the
gt; same cell in 'Green Worksheet' etc etc etc.
gt;
gt; I cant use pivot tables for this, and the 16th sheet feeds various graphs
gt; and stuff.
gt;
gt; Can anyone help me?
gt;
gt; Andy
gt;
gt;