worksheet references

[dido22]

Hello,

I have asked this before and thought I undestood the answer, but I was
wrong. Can I ask it again please ?

I have a workbook with several sheets. The 1st sheet is called 'Summary' and
uses data from any one of the other sheets. The other sheets are montly data
and are named 'Jan' 'Feb' 'Mar' etc.

At the moment I copy the required data manually using copy & paste, eg from
sheet Mar into Summary. No problem.

I would like to automate this by entering the name of the required month
into cell A1 in Summary, ie A1 contains the string Mar.

The other cells in Summary then need to reference the corresponding cells in
Mar.

I know how to do this sort of cell reference, eg in Sumary, in cell C3 I
put =Mar!C3, but I cant see how to get the Mar from cell A1 ?

I have tried as few things , eg =A1!C3 , and variants, but it doesn't seem
to work.

help & advice please?

Thanks

KK

 

[James Ravenswood]

Hello,

I have asked this before and thought I undestood the answer, but I was
wrong. Can I ask it again please ?

I have a workbook with several sheets. The 1st sheet is called 'Summary' and
uses data from any one of the other sheets. The other sheets are montly data
and are named 'Jan'  'Feb'  'Mar' etc.

At the moment I copy the required data manually using copy & paste, eg from
sheet Mar into Summary. No problem.

I would like to automate this by entering the name of the required month
into cell A1 in Summary, ie A1 contains the string Mar.

The other cells in Summary then need to reference the corresponding cellsin
Mar.

I know how to do this sort of cell reference, eg in Sumary, in cell C3   I
put      =Mar!C3,      but I cant see how to get the Mar from cell A1  ?

I have tried as few things , eg  =A1!C3 , and variants,  but it doesn't seem
to work.

help & advice please?

Thanks

KK

Say we have a worksheet named "qwerty"
In another sheet enter:
=qwerty!B9 to get the value in B9 in sheet qwerty

If A1 contains:
qwerty
then:
=INDIRECT(A1 & "!B9") will get the same value!

 

[Jim Cone]

The "Indirect" function reads a piece of text and provides the value in the cell referred
to in the text.
So if A1 contains July and the formula below is in Row 3 the Indirect function returns the
value in cell C3 on the July sheet.
=INDIRECT($A$1&"!C" & ROW())
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
Review of Special Sort add-in (30 ways to sort)




"dido22" <[email protected]>
wrote in message
news:[email protected]...

 

[Dave Peterson]

Sometimes, the sheetname needs to be surrounded by apostrophes.

I'd use:

=indirect("'"&a1&"'!c3")

If the apostrophes aren't required, it'll still work.