Integrate OFFSET, INDIRECT and VLOOKUP?

[Guest]

I have a workbook setup with tabs for each month named "Jan 07", "Feb 07" etc.
In each tab, vertically the days of that month form the top-left of a range
going some 20 columns right and about 8 rows down. Then an empty row and the
next day is up. Picture it?

Ok, now what I can't get done is to get the data of a specified date to
another worksheet (in the same workbook). Simply to represent what's been
entered on a date and to do some more calculations.

What I got so far is: =OFFSET(INDIRECT("'"&-SheetName-&"'!-reference-");;2)
-SheetName- is a cell with the name of the tab to look at.
-reference- is the issue.
I need Excel to lookup the cell where the requested daterange starts in
order to get OFFSET get the data. I can't find a way to do that with a
VLOOKUP...

Please help! It's highly appreciated!
Marc

 

[Tyro]

Workbooks have worksheets. Worksheets have tabs. A tab is not a worksheet.

Tyro

 

[Guest]

Perhaps this sample will illustrate one way to achieve it:
http://www.flypicture.com/download/NDE0NTI=
Extract date range from month-yr tabs.xls

Source data is assumed in identically structured sheets named as: Jan 07,
Feb 07, etc, where the "top left" dates for each "daterange" are assumed
running in A1 down. Each "daterange" covers an 8R x 20C area

In a "summary" sheet,
With a DV created in B1 to select the desired date,

Place in B3:
=IF($B$1="","",OFFSET(INDIRECT("'"&TEXT($B$1,"mmm
yy")&"'!A"&MATCH($B$1,INDIRECT("'"&TEXT($B$1,"mmm
yy")&"'!A:A"),0)),ROWS($1:1)-1,COLUMNS($A:A)-1))
Copy B3 across to U3, fill down to U9 to populate the range. B3:U9 will
return the required range from the correct sheet dependent on the DV
selection in B1. You could then stream on further calcs from the extracted
range.

 

[Max]

Thought the OP's description was okay, viz his lines:

named "Jan 07", "Feb 07" etc. In each tab, ...

where "tab/s" is understood to mean worksheet/s (or sheet/s)

---

 

[Guest]

Perhaps this sample will illustrate one way to achieve it:

http://www.flypicture.com/download/NDE0NTI=
Extract date range from month-yr tabs.xls

If you're reading the above in MS' webpage, don't click directly on the
link. It'll bring you to the wrong page. Just copy the entire link, inclusive
of the trailing "=", and paste into the browser's address bar, press ENTER.

(Note that you might need to change the "comma" separators to "semicolons"
to suit your regional settings)

---

 

[Guest]

Errata, I missed filling down the formulas by 1 row earlier:

.. Copy B3 across to U3, fill down to U9 to populate the range.
B3:U9 will return the required range from the correct sheet
dependent on the DV selection in B1.

U9 above should have read as U10
(so that all the 8 rows are extracted)

Here's the revised sample:
http://www.flypicture.com/download/NDE0NzY=
Extract date range from month-yr tabs.xls

---

 

[Guest]

Max,

Thanks a lot for your time and effort.

I found another file via your signature: Extracting_from_multiple_shts.xls
Basically explained me the same trick, and it works now!

I could not get the file downloaded, as the address changed in
http://www.flypicture.com/download/NDE0NzY=Extract date range from month-yr tabs.xls ...

Got the solution anyway, so thanks again and all the best!

Marc
The Netherlands

 

[Max]

welcome, glad it works for you