Importing cells frmom a calendar workbook

[AZSteve]

"Time-off Calendar" is Excel workbook with 12 worksheets, Jan, Feb, Mar, etc.
Each is set up like a calendar with 7 columns (days of week) across and
usually 5 weeks down, where first and last rows may be partially filled like
a typical calendar.

But days aren't single cells - top cell for each day has number (date), and
about nine cells below it to enter time-off requests (one request per cell,
as text). Top cell showing date is actual date number formated as "d" so
only the day number shows.

In workbook ZZZ (ideally Excel 2003) I want to be able to enter the date and
have all requests for that date in "Time-Off Calendar" workbook to be
imported, one line for each entry under that date.

I have used the match function for a less-complicated situation - only one
worksheet in the other workbook contains the data I need. But here I need to
choose both the correct worksheet and the correct day in the other workbook
to be able to get the ten cells below with that day's requests. Or, since
each date number is unique, can I get what I want by searching the whole
Time-Off Calendar workbook? I don't want to have to set up 365 ranges across
the 12 worksheets.

Help please.

 

[Bernie Deitrick]

Steve,

It would be better if you re-designed your workbook using database style
entries - one entry per row, with names in column A, starting date of the
time off in column B, and the end date of the time off in column C. Repeat
the name for every non-contiguous date requested off.

Then when you want to know who has time off for a specific date, filter
your list based on column B using custom "less than or equal to" and the
date of interest, and column C with custom "greater than or equal to" and
the date of interest. Then your names in column A will only show those with
that date off. You can then also look at blocks of time usiing filters.
The formula that you would need to use could be written, but it would be
complicated to both write and maintain.

HTH,
Bernie
MS Excel MVP

 

[AZSteve]

Yeah, I know it would be easier, but 1) it's not my calendar workbook, and 2)
it's what the "visual" people around here are used to using (meaning "it
looks like a calendar").

 

[Bernie Deitrick]

Steve,

To do this requires a full column of formulas to link to one of the sheets
in Time-Off Calendar. It also requires that Time-Off Calendar be open.

Let's say that your Time-Off Calendar sheets are named with full month
names: January, February, etc. and that the Sundays are in column A, Monday
in B, etc.

The day of interest is entered in a cell - let's say B7. In A1 of that same
sheet, enter

=INDEX(INDIRECT("'[Time-Off Calendar]" &
TEXT($B$7,"mmmm")&"'!A1:G1000"),ROW(),WEEKDAY($B$7))

and copy down at least as far as the longest column in any sheet in your
calendar. This will pull in the correct column of data.

Then in another cell, say C7, enter the formula

=IF(INDEX(A:A,MATCH($B$7,A:A,FALSE)+ROW(A1))<>0,INDEX(A:A,MATCH($B$7,A:A,FALSE)+ROW(A1)),"")

and copy down for as many cells as you can possibly have names for any
single date. This will extract data from the column of data for just the day
of interest.

If Sundays are not in column A, change the A1:G1000 to reflect the first
column of your calendar entries, and add a constant after the WEEKDAY term
to reflect the shift to the right: 1 if Sundays are in B, 2 if in C, etc.
If the sheet names are abbreviations, change ,"mmmm" to "mmm" or even "mmm."
if they have a period at the end


HTH,
Bernie
MS Excel MVP

 

[AZSteve]

Thanks, I'll give this a try. It might be several days, but I'll respond.

Steve,

To do this requires a full column of formulas to link to one of the sheets
in Time-Off Calendar. It also requires that Time-Off Calendar be open.

Let's say that your Time-Off Calendar sheets are named with full month
names: January, February, etc. and that the Sundays are in column A, Monday
in B, etc.

The day of interest is entered in a cell - let's say B7. In A1 of that same
sheet, enter

=INDEX(INDIRECT("'[Time-Off Calendar]" &
TEXT($B$7,"mmmm")&"'!A1:G1000"),ROW(),WEEKDAY($B$7))

and copy down at least as far as the longest column in any sheet in your
calendar. This will pull in the correct column of data.

Then in another cell, say C7, enter the formula

=IF(INDEX(A:A,MATCH($B$7,A:A,FALSE)+ROW(A1))<>0,INDEX(A:A,MATCH($B$7,A:A,FALSE)+ROW(A1)),"")

and copy down for as many cells as you can possibly have names for any
single date. This will extract data from the column of data for just the day
of interest.

If Sundays are not in column A, change the A1:G1000 to reflect the first
column of your calendar entries, and add a constant after the WEEKDAY term
to reflect the shift to the right: 1 if Sundays are in B, 2 if in C, etc.
If the sheet names are abbreviations, change ,"mmmm" to "mmm" or even "mmm."
if they have a period at the end


HTH,
Bernie
MS Excel MVP

Yeah, I know it would be easier, but 1) it's not my calendar workbook, and
2)
it's what the "visual" people around here are used to using (meaning "it
looks like a calendar").

.