Dynamic link a cell to a worksheet tab

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
---

Max,

Thanks for the help. I played with your formula for hours..slept only
30 min today only and I couldn't figure out what it means. Any good
tutorial on your approach??

Here's a sample construct to illustrate the earlier response:
http://www.savefile.com/files/9747307
Dynamic link a cell to a worksheet tab_Genesis_misc

The core formula is just:
=OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROW(A1)-1,COLUMN(A1)-1)

With A1 containing: August
the above OFFSET formula placed in A2 returns the same
as the relative link formula: =August!A1

And when the formula is filled across / down, it will simply replicate
the filling across/down of the link formula: =August!A1, viz.:

A2 copied across to B2 returns in B2: =August!B1,
copied to B3 returns in B3: =August!C1, and so on

A2 copied down to A3 returns in A3: =August!A2, and so on

I threw in 2 error traps to provide a cleaner display in the summary sheet

The 1st error trap: =IF($A$1="","",
simply ensures that if A1 is cleared,
then blanks: "" (i.e. "nothing") should/would show
(otherwise we'd get the ugly: #REF! error msgs)

The 2nd error trap:
IF(OFFSET(INDIRECT("'"&$A$1&"'!A1"),ROW(A1)-1,COLUMN(A1)-1)=0,"",
simply ensures that if the result returned by the OFFSET is a zero (eg, if
the source sheet's cells are empty), then again, for a cleaner look, blanks:
"" (i.e. "nothing") would show.

We could also do away with the 2nd error trap
by simply switching off the zero values display in the sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

Hope the above clarifies it a little better ..
(You still need to catch up on your sleep, though <g>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Genesis" <[email protected]> wrote in message
news:[email protected]...
> Max,
>
> Thanks for the help. I played with your formula for hours..slept only
> 30 min today only and I couldn't figure out what it means. Any good
> tutorial on your approach??
>

Typo, in line:
> copied to B3 returns in B3: =August!C1, and so on

should read as:
> copied to C2 returns in C2: =August!C1, and so on

(I'm also badly in need of sleep/oxy <g>)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---