Lookup Question

  • Thread starter Thread starter Curtis
  • Start date Start date
C

Curtis

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
 
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
 
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
 
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
 
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
 
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 said:
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 said:
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)
Click to expand...
Click to expand...
 
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
---
 
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
(e-mail address removed)
Curtis said:
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 said:
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 said:
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)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top