Dynamic link a cell to a worksheet tab

[Genesis]

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.

 

[Max]

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

 

[Roger Govier]

Hi

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

 

[Genesis]

Hi

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

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.

 

[Peo Sjoblom]

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]

Peo,

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

 

[Max]

The flexibility you sought in terms of copying across & down
was pre-anticipated and covered within my response <g>

---

 

[Genesis]

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

 

[Max]

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]

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

---