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

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

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

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