Hello all,

I a workbook with 13 tabs, of which 12 of them are names of the month
(jan -dec). The 13th tab is the summary page. On the summary page, if
cell A1 changed from say "june" to "august", I need to know if it's
possible for excel to pull the data from the "august" tab. In other
word, whatever month appears in cell A1 of the summary tab, I want cell
A2 to reflect the value from the coresponding month's tab. Please help
if you can as I need this urgently. Many thanks.

One play to try ..

In the summary sheet, assuming we want to extract over the range A1:E10 from
any monthly sheet, where the month is specified in A1 (A1 may contain a data
validation list to select the desired month)

A1 contains, say: August

Put in A2:
=IF($A$1="","",IF(OFFSET(INDIRECT("'"&$A$1&"'!A1") ,ROW(A1)-1,COLUMN(A1)-1)=0
,"",OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROW(A1)-1,COLUMN(A1)-1)))

Copy A2 across to E2, fill down to E11
to cover the target range (i.e. A1:E10) in the monthly tab

A2:E11 will return what's in A1:E10 from the sheet named: August
(Copy the source monthly sheet's format and do a paste special formats if
necess.)

When we input/select another month in A1, say: July
A2:E11 will auto-return the contents from the sheet named July
(Note that the sheet names must match exactly what's entered/selected in A1)

Adapt/extend to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Genesis" wrote in message
oups.com...
Hello all,

I a workbook with 13 tabs, of which 12 of them are names of the month
(jan -dec). The 13th tab is the summary page. On the summary page, if
cell A1 changed from say "june" to "august", I need to know if it's
possible for excel to pull the data from the "august" tab. In other
word, whatever month appears in cell A1 of the summary tab, I want cell
A2 to reflect the value from the coresponding month's tab. Please help
if you can as I need this urgently. Many thanks.

Hi

If A1 contains august, and you want the data from cell A2 on the sheet
august, then
=INDIRECT(A10&"!A2")

--
Regards

Roger Govier


"Genesis" wrote in message
oups.com...
Hello all,

I a workbook with 13 tabs, of which 12 of them are names of the month
(jan -dec). The 13th tab is the summary page. On the summary page, if
cell A1 changed from say "june" to "august", I need to know if it's
possible for excel to pull the data from the "august" tab. In other
word, whatever month appears in cell A1 of the summary tab, I want
cell
A2 to reflect the value from the coresponding month's tab. Please help
if you can as I need this urgently. Many thanks.

Roger Govier wrote:
Hi

If A1 contains august, and you want the data from cell A2 on the sheet
august, then
=INDIRECT(A10&"!A2")

--
Regards

Roger Govier



Thank you all..

Roger or anyone , i used the the INDIRECT formula you gave and it
worked..but I can't seem to drag down. It keeps anchoring to A2 only..
i want it to be able to it down A3,A4,A5, ETC .. how can this be done
using this formula??

Thanks again.





"Genesis" wrote in message
oups.com...
Hello all,

I a workbook with 13 tabs, of which 12 of them are names of the month
(jan -dec). The 13th tab is the summary page. On the summary page, if
cell A1 changed from say "june" to "august", I need to know if it's
possible for excel to pull the data from the "august" tab. In other
word, whatever month appears in cell A1 of the summary tab, I want
cell
A2 to reflect the value from the coresponding month's tab. Please help
if you can as I need this urgently. Many thanks.

One way

=INDIRECT("'"&A10&"'!"&CELL("address",A2))

this will also work

=INDIRECT("'"&A10&"'!A"&ROW(2:2))

however it's less flexible than the former

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"Genesis" wrote in message
oups.com...

Roger Govier wrote:
Hi

If A1 contains august, and you want the data from cell A2 on the sheet
august, then
=INDIRECT(A10&"!A2")

--
Regards

Roger Govier



Thank you all..

Roger or anyone , i used the the INDIRECT formula you gave and it
worked..but I can't seem to drag down. It keeps anchoring to A2 only..
i want it to be able to it down A3,A4,A5, ETC .. how can this be done
using this formula??

Thanks again.





"Genesis" wrote in message
oups.com...
Hello all,

I a workbook with 13 tabs, of which 12 of them are names of the month
(jan -dec). The 13th tab is the summary page. On the summary page, if
cell A1 changed from say "june" to "august", I need to know if it's
possible for excel to pull the data from the "august" tab. In other
word, whatever month appears in cell A1 of the summary tab, I want
cell
A2 to reflect the value from the coresponding month's tab. Please help
if you can as I need this urgently. Many thanks.

Peo,

You are a GENIUS!!!!!!!!
Millions thanks to all who took the time to help!

The flexibility you sought in terms of copying across & down
was pre-anticipated and covered within my response <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---