software

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 quot;junequot; to quot;augustquot;, I need to know if it's
possible for excel to pull the data from the quot;augustquot; 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=quot;quot;,quot;quot;,IF(OFFSET(INDIRECT(quot;'quot;amp;$A$1amp;quot;'!A1quot;) ,ROW(A1)-1,COLUMN(A1)-1)=0
,quot;quot;,OFFSET(INDIRECT(quot;'quot;amp;$A$1amp;quot;'!A1quot;),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 gt; 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
savefile.com/projects/236895
xdemechanik
---
quot;Genesisquot; gt; wrote in message oups.com...
gt; Hello all,
gt;
gt; I a workbook with 13 tabs, of which 12 of them are names of the month
gt; (jan -dec). The 13th tab is the summary page. On the summary page, if
gt; cell A1 changed from say quot;junequot; to quot;augustquot;, I need to know if it's
gt; possible for excel to pull the data from the quot;augustquot; tab. In other
gt; word, whatever month appears in cell A1 of the summary tab, I want cell
gt; A2 to reflect the value from the coresponding month's tab. Please help
gt; if you can as I need this urgently. Many thanks.
gt;
Hi

If A1 contains august, and you want the data from cell A2 on the sheet
august, then
=INDIRECT(A10amp;quot;!A2quot;)

--
Regards

Roger Govierquot;Genesisquot; gt; wrote in message oups.com...
gt; Hello all,
gt;
gt; I a workbook with 13 tabs, of which 12 of them are names of the month
gt; (jan -dec). The 13th tab is the summary page. On the summary page, if
gt; cell A1 changed from say quot;junequot; to quot;augustquot;, I need to know if it's
gt; possible for excel to pull the data from the quot;augustquot; tab. In other
gt; word, whatever month appears in cell A1 of the summary tab, I want
gt; cell
gt; A2 to reflect the value from the coresponding month's tab. Please help
gt; if you can as I need this urgently. Many thanks.
gt;

Roger Govier wrote:
gt; Hi
gt;
gt; If A1 contains august, and you want the data from cell A2 on the sheet
gt; august, then
gt; =INDIRECT(A10amp;quot;!A2quot;)
gt;
gt; --
gt; Regards
gt;
gt; 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.
gt;
gt;
gt; quot;Genesisquot; gt; wrote in message
gt; oups.com...
gt; gt; Hello all,
gt; gt;
gt; gt; I a workbook with 13 tabs, of which 12 of them are names of the month
gt; gt; (jan -dec). The 13th tab is the summary page. On the summary page, if
gt; gt; cell A1 changed from say quot;junequot; to quot;augustquot;, I need to know if it's
gt; gt; possible for excel to pull the data from the quot;augustquot; tab. In other
gt; gt; word, whatever month appears in cell A1 of the summary tab, I want
gt; gt; cell
gt; gt; A2 to reflect the value from the coresponding month's tab. Please help
gt; gt; if you can as I need this urgently. Many thanks.
gt; gt;One way

=INDIRECT(quot;'quot;amp;A10amp;quot;'!quot;amp;CELL(quot;addressquot;,A2))

this will also work

=INDIRECT(quot;'quot;amp;A10amp;quot;'!Aquot;amp;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

quot;Genesisquot; gt; wrote in message oups.com...
gt;
gt; Roger Govier wrote:
gt;gt; Hi
gt;gt;
gt;gt; If A1 contains august, and you want the data from cell A2 on the sheet
gt;gt; august, then
gt;gt; =INDIRECT(A10amp;quot;!A2quot;)
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;
gt;
gt;
gt; Thank you all..
gt;
gt; Roger or anyone , i used the the INDIRECT formula you gave and it
gt; worked..but I can't seem to drag down. It keeps anchoring to A2 only..
gt; i want it to be able to it down A3,A4,A5, ETC .. how can this be done
gt; using this formula??
gt;
gt; Thanks again.
gt;
gt;
gt;
gt;gt;
gt;gt;
gt;gt; quot;Genesisquot; gt; wrote in message
gt;gt; oups.com...
gt;gt; gt; Hello all,
gt;gt; gt;
gt;gt; gt; I a workbook with 13 tabs, of which 12 of them are names of the month
gt;gt; gt; (jan -dec). The 13th tab is the summary page. On the summary page, if
gt;gt; gt; cell A1 changed from say quot;junequot; to quot;augustquot;, I need to know if it's
gt;gt; gt; possible for excel to pull the data from the quot;augustquot; tab. In other
gt;gt; gt; word, whatever month appears in cell A1 of the summary tab, I want
gt;gt; gt; cell
gt;gt; gt; A2 to reflect the value from the coresponding month's tab. Please help
gt;gt; gt; if you can as I need this urgently. Many thanks.
gt;gt; gt;
gt;Peo,

You are a GENIUS!!!!!!!!
Millions thanks to all who took the time to help!The flexibility you sought in terms of copying across amp; down
was pre-anticipated and covered within my response lt;ggt;
--
Max
Singapore
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:
www.savefile.com/files/9747307
Dynamic link a cell to a worksheet tab_Genesis_misc

The core formula is just:
=OFFSET(INDIRECT(quot;'quot;amp;$A$1amp;quot;'!A1quot;),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=quot;quot;,quot;quot;,
simply ensures that if A1 is cleared,
then blanks: quot;quot; (i.e. quot;nothingquot;) should/would show
(otherwise we'd get the ugly: #REF! error msgs)

The 2nd error trap:
IF(OFFSET(INDIRECT(quot;'quot;amp;$A$1amp;quot;'!A1quot;),ROW(A1)-1,COLUMN(A1)-1)=0,quot;quot;,
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:
quot;quot; (i.e. quot;nothingquot;) 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 gt; Options gt; View tab gt; Uncheck quot;Zero valuesquot; gt; OK

Hope the above clarifies it a little better ..
(You still need to catch up on your sleep, though lt;ggt;)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Genesisquot; gt; wrote in message oups.com...
gt; Max,
gt;
gt; Thanks for the help. I played with your formula for hours..slept only
gt; 30 min today only and I couldn't figure out what it means. Any good
gt; tutorial on your approach??
gt;
Typo, in line:
gt; copied to B3 returns in B3: =August!C1, and so on

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

(I'm also badly in need of sleep/oxy lt;ggt;)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---