Lookup Question

Curtis · External Since: Feb 11, 2005 Posts: 114

I have a summary sheet that lists all divisions across the top (d7:q7)
It lists all GL accounts A3:A127
Also cell c3 is reserved for mth (numerically entered 1 thru 12)

My source workbook contains 12 sheets (each sheet representing a month)
-divisions are listed across the top (d7:q7)
-G/L accounts A3:A127
-costing d3:q127

Is than any formula that could look through all the sheets based on the
month number entered in cell c3 in the summary sheet. ( for example if 4 is
selected it would look for the worksheet representing the 4th month

Thanks

Max · External Since: Mar 17, 2004 Posts: 1751

Presume your 12 "month" source sheets are identically structured, with key
data in D7 across/down that you want extracted into your summary sheet. Take
a minute to rename the 12 "month" source sheets as the numbers: 1,2,3 ... 12

Then in your summary sheet,
the desired "month" will be input into C3, eg: 4 (for the 4th month)
In D7:
=IF($C$3="","",OFFSET(INDIRECT("'"&$C$3&"'!D7"),ROWS($1:1)-1,COLUMNS($A:A)-1))
Copy D7 across/down as far as required. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Curtis" wrote:
> I have a summary sheet that lists all divisions across the top (d7:q7)
> It lists all GL accounts A3:A127
> Also cell c3 is reserved for mth (numerically entered 1 thru 12)
>
> My source workbook contains 12 sheets (each sheet representing a month)
> -divisions are listed across the top (d7:q7)
> -G/L accounts A3:A127
> -costing d3:q127
>
> Is than any formula that could look through all the sheets based on the
> month number entered in cell c3 in the summary sheet. ( for example if 4 is
> selected it would look for the worksheet representing the 4th month
>
> Thanks

Curtis · External Since: Feb 11, 2005 Posts: 114

Where do I make reference in the formula to the source workbook say YTDTB (
note: the summary sheet resides in a different workbook than the source data)

Thanks

"Max" wrote:

> Presume your 12 "month" source sheets are identically structured, with key
> data in D7 across/down that you want extracted into your summary sheet. Take
> a minute to rename the 12 "month" source sheets as the numbers: 1,2,3 ... 12
>
> Then in your summary sheet,
> the desired "month" will be input into C3, eg: 4 (for the 4th month)
> In D7:
> =IF($C$3="","",OFFSET(INDIRECT("'"&$C$3&"'!D7"),ROWS($1:1)-1,COLUMNS($A:A)-1))
> Copy D7 across/down as far as required. Success? hit the YES below
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:27,000 Files:200 Subscribers:70
> xdemechanik
> ---
> "Curtis" wrote:
> > I have a summary sheet that lists all divisions across the top (d7:q7)
> > It lists all GL accounts A3:A127
> > Also cell c3 is reserved for mth (numerically entered 1 thru 12)
> >
> > My source workbook contains 12 sheets (each sheet representing a month)
> > -divisions are listed across the top (d7:q7)
> > -G/L accounts A3:A127
> > -costing d3:q127
> >
> > Is than any formula that could look through all the sheets based on the
> > month number entered in cell c3 in the summary sheet. ( for example if 4 is
> > selected it would look for the worksheet representing the 4th month
> >
> > Thanks

Max · External Since: Mar 17, 2004 Posts: 1751

Assume that the source book is Book2.xls, and this book is open at the same
time (this is a requirement for INDIRECT to work)

Then in D7 in the summary sheet in your other book,
you could use this instead:
=IF($C$3="","",OFFSET(INDIRECT("'[Book2]"&$C$3&"'!D7"),ROWS($1:1)-1,COLUMNS($A:A)-1))
Copy D7 across/down as far as required. Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Curtis" wrote:
> Where do I make reference in the formula to the source workbook say YTDTB (
> note: the summary sheet resides in a different workbook than the source data)

Curtis · External Since: Feb 11, 2005 Posts: 114

Can I use the following naming convention instead?

YTDTB01 reps mth 1
YTDTB02 reps mth 2

etc... since that is already what is in place?

thanks

"Curtis" wrote:

> Where do I make reference in the formula to the source workbook say YTDTB (
> note: the summary sheet resides in a different workbook than the source data)
>
> Thanks
>
> "Max" wrote:
>
> > Presume your 12 "month" source sheets are identically structured, with key
> > data in D7 across/down that you want extracted into your summary sheet. Take
> > a minute to rename the 12 "month" source sheets as the numbers: 1,2,3 ... 12
> >
> > Then in your summary sheet,
> > the desired "month" will be input into C3, eg: 4 (for the 4th month)
> > In D7:
> > =IF($C$3="","",OFFSET(INDIRECT("'"&$C$3&"'!D7"),ROWS($1:1)-1,COLUMNS($A:A)-1))
> > Copy D7 across/down as far as required. Success? hit the YES below
> > --
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > Downloads:27,000 Files:200 Subscribers:70
> > xdemechanik
> > ---
> > "Curtis" wrote:
> > > I have a summary sheet that lists all divisions across the top (d7:q7)
> > > It lists all GL accounts A3:A127
> > > Also cell c3 is reserved for mth (numerically entered 1 thru 12)
> > >
> > > My source workbook contains 12 sheets (each sheet representing a month)
> > > -divisions are listed across the top (d7:q7)
> > > -G/L accounts A3:A127
> > > -costing d3:q127
> > >
> > > Is than any formula that could look through all the sheets based on the
> > > month number entered in cell c3 in the summary sheet. ( for example if 4 is
> > > selected it would look for the worksheet representing the 4th month
> > >
> > > Thanks

Curtis · External Since: Feb 11, 2005 Posts: 114

Not working for me but it is likely me

Yes all sheets are identically structured

Row 7 (columns D:AI) contain the identifiers for the divisions
Col A (rows 3:277) contain the G/L #
Range (D3:AI277) contain the costing data

Also does the source sheet need to be open all the time or just to update
the file?

thanks Max

"Max" wrote:

> Assume that the source book is Book2.xls, and this book is open at the same
> time (this is a requirement for INDIRECT to work)
>
> Then in D7 in the summary sheet in your other book,
> you could use this instead:
> =IF($C$3="","",OFFSET(INDIRECT("'[Book2]"&$C$3&"'!D7"),ROWS($1:1)-1,COLUMNS($A:A)-1))
> Copy D7 across/down as far as required. Success? hit the YES below
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:27,000 Files:200 Subscribers:70
> xdemechanik
> ---
> "Curtis" wrote:
> > Where do I make reference in the formula to the source workbook say YTDTB (
> > note: the summary sheet resides in a different workbook than the source data)
>

Max · External Since: Mar 17, 2004 Posts: 1751

I'm out of further suggestions. It should have worked fine for you, provided
the source book is open at the same time (that's the requirement for
INDIRECT). Start a fresh new post with full details if you are expanding your
original scope as asked in this thread.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Don Guillett · External Since: Jan 04, 2006 Posts: 2988

If desired, send your file S to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1.RemoveThis@austin.rr.com
"Curtis" <Curtis.RemoveThis@discussions.microsoft.com> wrote in message
news:D3C94AE6-ACBA-4A9F-9460-14D3419F14B3@microsoft.com...
> Not working for me but it is likely me
>
> Yes all sheets are identically structured
>
> Row 7 (columns D:AI) contain the identifiers for the divisions
> Col A (rows 3:277) contain the G/L #
> Range (D3:AI277) contain the costing data
>
> Also does the source sheet need to be open all the time or just to update
> the file?
>
> thanks Max
>
> "Max" wrote:
>
>> Assume that the source book is Book2.xls, and this book is open at the
>> same
>> time (this is a requirement for INDIRECT to work)
>>
>> Then in D7 in the summary sheet in your other book,
>> you could use this instead:
>> =IF($C$3="","",OFFSET(INDIRECT("'[Book2]"&$C$3&"'!D7"),ROWS($1:1)-1,COLUMNS($A:A)-1))
>> Copy D7 across/down as far as required. Success? hit the YES below
>> --
>> Max
>> Singapore
>> http://savefile.com/projects/236895
>> Downloads:27,000 Files:200 Subscribers:70
>> xdemechanik
>> ---
>> "Curtis" wrote:
>> > Where do I make reference in the formula to the source workbook say
>> > YTDTB (
>> > note: the summary sheet resides in a different workbook than the source
>> > data)
>>