Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting part of a spredsheet based on the date
I currently have a spreadsheet with all our employees down the A column
and then going across the top a calender. One column per day of the year (it has to go over 2 spreadsheets). I then have a code that is input in the grid to say what dates each employee is on holiday. I am looking for a way to link another spreadsheet to this one, but only show the columns relating to the current month and next month. I can link in the first column with the employee's name as it is static, but how do I get it to link only to this month and next month when it changes each month? I hope this makes sense. Regards Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting part of a spredsheet based on the date
put in formulas that involve a structure like this or similar
=offset(MasterSheet!$A$1,row(),match(Indirect(Addr ess(1,column())),masterShe ets!$B$1:$IV$1,0),1,1) since you have multiple sheets, it would be more complex in that you would need to determine which sheet and which column in that sheet. -- Regards, Tom Ogilvy "Keith" wrote in message ... I currently have a spreadsheet with all our employees down the A column and then going across the top a calender. One column per day of the year (it has to go over 2 spreadsheets). I then have a code that is input in the grid to say what dates each employee is on holiday. I am looking for a way to link another spreadsheet to this one, but only show the columns relating to the current month and next month. I can link in the first column with the employee's name as it is static, but how do I get it to link only to this month and next month when it changes each month? I hope this makes sense. Regards Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting part of a spredsheet based on the date
The number of columns can be calculated by Date(Year,Month,1)-Date(Year,1,1):
this gives you a number you can use with the OFFSET function to specify the cell to link to in the master sheet: for example, this would be the formula in B2 =OFFSET('[MasterBookName]MasterSheetName'!B2,0,Date(Year,Month,1)-Date(Year,1,1)) Substitute in the proper book and sheet names. You can copy this formula throughout the output range and it should correctly link up the proper cells from the master sheet into the corresponding cells in the new sheet. There are just a couple things to take into account, though: 1) If any cells in the master sheet are blank, they will come across as zeros; this may or may not be a problem and could be addressed either through conditional formatting or adding an IF condition to the function above; 2) You would need to determine the proper # of columns to set up in the new sheet; it would be nice to just have the same formula in 62 columns for 2 months and have it suppress the results if either of the two months was less than 31 days; this would be possible with another IF condition (or maybe CHOOSE) but you would end up with a very long formula and I don't know if you need to do that. So I will leave that up to you to figure out! HTH "Keith" wrote: I currently have a spreadsheet with all our employees down the A column and then going across the top a calender. One column per day of the year (it has to go over 2 spreadsheets). I then have a code that is input in the grid to say what dates each employee is on holiday. I am looking for a way to link another spreadsheet to this one, but only show the columns relating to the current month and next month. I can link in the first column with the employee's name as it is static, but how do I get it to link only to this month and next month when it changes each month? I hope this makes sense. Regards Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting part of a spredsheet based on the date
That would certainly be more direct if weekends and actuall holidays are
included on the master sheet. -- Regards, Tom Ogilvy "K Dales" wrote in message ... The number of columns can be calculated by Date(Year,Month,1)-Date(Year,1,1): this gives you a number you can use with the OFFSET function to specify the cell to link to in the master sheet: for example, this would be the formula in B2: =OFFSET('[MasterBookName]MasterSheetName'!B2,0,Date(Year,Month,1)-Date(Year, 1,1)) Substitute in the proper book and sheet names. You can copy this formula throughout the output range and it should correctly link up the proper cells from the master sheet into the corresponding cells in the new sheet. There are just a couple things to take into account, though: 1) If any cells in the master sheet are blank, they will come across as zeros; this may or may not be a problem and could be addressed either through conditional formatting or adding an IF condition to the function above; 2) You would need to determine the proper # of columns to set up in the new sheet; it would be nice to just have the same formula in 62 columns for 2 months and have it suppress the results if either of the two months was less than 31 days; this would be possible with another IF condition (or maybe CHOOSE) but you would end up with a very long formula and I don't know if you need to do that. So I will leave that up to you to figure out! HTH "Keith" wrote: I currently have a spreadsheet with all our employees down the A column and then going across the top a calender. One column per day of the year (it has to go over 2 spreadsheets). I then have a code that is input in the grid to say what dates each employee is on holiday. I am looking for a way to link another spreadsheet to this one, but only show the columns relating to the current month and next month. I can link in the first column with the employee's name as it is static, but how do I get it to link only to this month and next month when it changes each month? I hope this makes sense. Regards Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting part of a cell | Excel Worksheet Functions | |||
Extracting part of data | Excel Discussion (Misc queries) | |||
Extracting part of a cell | Excel Worksheet Functions | |||
Extracting Part of a Date | Excel Discussion (Misc queries) | |||
extracting part of string value | Excel Programming |