If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Importing cells frmom a calendar workbook
"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. |
#2
|
|||
|
|||
Importing cells frmom a calendar workbook
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" wrote in message ... "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. |
#3
|
|||
|
|||
Importing cells frmom a calendar workbook
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" wrote: 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" wrote in message ... "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. . |
#4
|
|||
|
|||
Importing cells frmom a calendar workbook
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,IN DEX(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" wrote in message ... 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" wrote: 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" wrote in message ... "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. . |
#5
|
|||
|
|||
Importing cells frmom a calendar workbook
Thanks, I'll give this a try. It might be several days, but I'll respond.
"Bernie Deitrick" wrote: 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,IN DEX(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" wrote in message ... 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" wrote: 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" wrote in message ... "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. . . |
Thread Tools | |
Display Modes | |
|
|