Greetings,

I have a pair of schedule sheets that I cant seem to coordinate.

The first is a daily sheet which starts on the first of the month with
27 positions to fill. These are filled from an Input sheet - This
works fine.

The second sheet starts not at the first day of the month, but on
Monday of the week that the first day falls in.

I'm not sure what to write to get the values of the daily sheet to
show up in the weekly sheet?

I tried making a 14 year master set of sheets and manually pasting the
section of the month into the weekly sheet, but that is not working
very well.

I can send a copy of the sheets involved with this question, if anyone
is willing to look at it.

Any idea's will be most appreciated.

TIA

-Minitman

Can you give a specific example of what you want to match on your 2nd sheet?
I'm guessing it's a date, but I'm not sure.

"Minitman" wrote in message
...
Greetings,

I have a pair of schedule sheets that I cant seem to coordinate.

The first is a daily sheet which starts on the first of the month with
27 positions to fill. These are filled from an Input sheet - This
works fine.

The second sheet starts not at the first day of the month, but on
Monday of the week that the first day falls in.

I'm not sure what to write to get the values of the daily sheet to
show up in the weekly sheet?

I tried making a 14 year master set of sheets and manually pasting the
section of the month into the weekly sheet, but that is not working
very well.

I can send a copy of the sheets involved with this question, if anyone
is willing to look at it.

Any idea's will be most appreciated.

TIA

-Minitman

Opps I forgot to mention that the daily sheet is set up to show the
information in two row blocks. I am using VLOOKUP in the daily
looking to the "Input" and the "Customer" sheets. It looks like I
should be able to do the same, but it doesn't.

-Minitman



On Sun, 30 Oct 2005 18:16:53 -0600, Minitman
wrote:

Greetings,

I have a pair of schedule sheets that I cant seem to coordinate.

The first is a daily sheet which starts on the first of the month with
27 positions to fill. These are filled from an Input sheet - This
works fine.

The second sheet starts not at the first day of the month, but on
Monday of the week that the first day falls in.

I'm not sure what to write to get the values of the daily sheet to
show up in the weekly sheet?

I tried making a 14 year master set of sheets and manually pasting the
section of the month into the weekly sheet, but that is not working
very well.

I can send a copy of the sheets involved with this question, if anyone
is willing to look at it.

Any idea's will be most appreciated.

TIA

-Minitman

OK, let me ask this again. What do you have on the INPUT sheet and what do
you have on the Customer sheet? It's hard to answer without knowing what's
there. Specifics would be helpful.


"Minitman" wrote in message
...
Opps I forgot to mention that the daily sheet is set up to show the
information in two row blocks. I am using VLOOKUP in the daily
looking to the "Input" and the "Customer" sheets. It looks like I
should be able to do the same, but it doesn't.

-Minitman



On Sun, 30 Oct 2005 18:16:53 -0600, Minitman
wrote:

Greetings,

I have a pair of schedule sheets that I cant seem to coordinate.

The first is a daily sheet which starts on the first of the month with
27 positions to fill. These are filled from an Input sheet - This
works fine.

The second sheet starts not at the first day of the month, but on
Monday of the week that the first day falls in.

I'm not sure what to write to get the values of the daily sheet to
show up in the weekly sheet?

I tried making a 14 year master set of sheets and manually pasting the
section of the month into the weekly sheet, but that is not working
very well.

I can send a copy of the sheets involved with this question, if anyone
is willing to look at it.

Any idea's will be most appreciated.

TIA

-Minitman

is there a column of dates in the second sheet?

If so, assume column A in that sheet,

so in B2 of the second sheet

=Vlookup($A2,Daily!$A$2:$AA$32,2,False)



--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
Greetings,

I have a pair of schedule sheets that I cant seem to coordinate.

The first is a daily sheet which starts on the first of the month with
27 positions to fill. These are filled from an Input sheet - This
works fine.

The second sheet starts not at the first day of the month, but on
Monday of the week that the first day falls in.

I'm not sure what to write to get the values of the daily sheet to
show up in the weekly sheet?

I tried making a 14 year master set of sheets and manually pasting the
section of the month into the weekly sheet, but that is not working
very well.

I can send a copy of the sheets involved with this question, if anyone
is willing to look at it.

Any idea's will be most appreciated.

TIA

-Minitman

Hey Barb,

Thanks for the quick reply.

Yes, it is a date and position.

In the "Daily" sheet each day has 2 'pages' (for printing) with 27 2
row sets of invoice information (19 on the right page and 8 on the
left page). the "Weekly" is an abbreviated version of the "Daily"
sheet showing one week per page with 9 entries per day instead
of 27. A further complicating factor is the order of display of the
"Daily" sheet, Number 1 position is at the bottom of the right hand
page and ascends to Number 19 at the top of the right hand page. Then
goes to number 20 at the bottom of the left hand page and goes up to
27. Only the first 9 from the right hand page are transferred because
of space constraints in the weekly format.

Any ideas or suggestions?

-Minitman

On Sun, 30 Oct 2005 19:22:38 -0500, "Barb Reinhardt"
wrote:

Can you give a specific example of what you want to match on your 2nd sheet?
I'm guessing it's a date, but I'm not sure.

"Minitman" wrote in message
.. .
Greetings,

I have a pair of schedule sheets that I cant seem to coordinate.

The first is a daily sheet which starts on the first of the month with
27 positions to fill. These are filled from an Input sheet - This
works fine.

The second sheet starts not at the first day of the month, but on
Monday of the week that the first day falls in.

I'm not sure what to write to get the values of the daily sheet to
show up in the weekly sheet?

I tried making a 14 year master set of sheets and manually pasting the
section of the month into the weekly sheet, but that is not working
very well.

I can send a copy of the sheets involved with this question, if anyone
is willing to look at it.

Any idea's will be most appreciated.

TIA

-Minitman

Hey Tom,

Both of these sheets are set up for printing and as such there are on
date columns.

In the Daily sheet the 1st date is in W2. The next date is in W44.
The 3rd date is in W86 and all the way to the last date (in a 31 day
month) at W1262.

In the Weekly sheet we start with Mon at B4, Tues at B13, Wed at B22,
Thurs at B31, Fri at B40, Sat at B49, Sun at B54 and the next Mon at
B60. Sat and Sun are only 6 rows each and there is a weekly total row
after Sun. The pattern repeats for a total of 6 weeks. The problem
is recognizing when the month starts in the cell formulas that are
getting the contents of the cells I need.

Both sheets get their date from a Date sheet which also gives the
Weekly sheet it's Monday date (A5 on the "Date" sheet for "Weekly" and
A7 for the "Daily" and every other sheet that needs a date).

Anything else I forgot to mention, please let me know.

And thanks for your help.

-Minitman
..

On Sun, 30 Oct 2005 19:39:18 -0500, "Tom Ogilvy"
wrote:

is there a column of dates in the second sheet?

If so, assume column A in that sheet,

so in B2 of the second sheet

=Vlookup($A2,Daily!$A$2:$AA$32,2,False)

If you know exactly where you want to get your information as you appear to,
the just use hard coded linking formulas

=Daily!W2

in B4 of the weekly sheet.

If you need to dynamically determine the location of the first monday in the
daily sheet from the weekly sheet, then you can do

(day(date of monday)-1)*42+2

This would be your anchor. You can combine it with Offset, or Index or
Indirect perhaps.



--
Regards,
Tom Ogivly


"Minitman" wrote in message
...
Hey Tom,

Both of these sheets are set up for printing and as such there are on
date columns.

In the Daily sheet the 1st date is in W2. The next date is in W44.
The 3rd date is in W86 and all the way to the last date (in a 31 day
month) at W1262.

In the Weekly sheet we start with Mon at B4, Tues at B13, Wed at B22,
Thurs at B31, Fri at B40, Sat at B49, Sun at B54 and the next Mon at
B60. Sat and Sun are only 6 rows each and there is a weekly total row
after Sun. The pattern repeats for a total of 6 weeks. The problem
is recognizing when the month starts in the cell formulas that are
getting the contents of the cells I need.

Both sheets get their date from a Date sheet which also gives the
Weekly sheet it's Monday date (A5 on the "Date" sheet for "Weekly" and
A7 for the "Daily" and every other sheet that needs a date).

Anything else I forgot to mention, please let me know.

And thanks for your help.

-Minitman
.

On Sun, 30 Oct 2005 19:39:18 -0500, "Tom Ogilvy"
wrote:

is there a column of dates in the second sheet?

If so, assume column A in that sheet,

so in B2 of the second sheet

=Vlookup($A2,Daily!$A$2:$AA$32,2,False)

Hey Tom,

I do know where I am getting my date from, I just don't know how Daily
will know where since the 1st of the month will occur in anyone of the
first seven days of the week. How do I tell Weekly where to look.
Hard coding the links works if the days stay the same, which they do
in all other sheets except Weekly (and they are hard coded). Since
Weekly can start on any day of the week, I am at a loss.

-Minitman


On Sun, 30 Oct 2005 21:09:26 -0500, "Tom Ogilvy"
wrote:

If you know exactly where you want to get your information as you appear to,
the just use hard coded linking formulas

=Daily!W2

in B4 of the weekly sheet.

If you need to dynamically determine the location of the first monday in the
daily sheet from the weekly sheet, then you can do

(day(date of monday)-1)*42+2

This would be your anchor. You can combine it with Offset, or Index or
Indirect perhaps.

Opps... It's Weekly, not Daily, that I am having trouble with!

-Minitman

On Sun, 30 Oct 2005 20:34:49 -0600, Minitman
wrote:

Hey Tom,

I do know where I am getting my date from, I just don't know how Daily
will know where since the 1st of the month will occur in anyone of the
first seven days of the week. How do I tell Weekly where to look.
Hard coding the links works if the days stay the same, which they do
in all other sheets except Weekly (and they are hard coded). Since
Weekly can start on any day of the week, I am at a loss.

-Minitman


On Sun, 30 Oct 2005 21:09:26 -0500, "Tom Ogilvy"
wrote:

If you know exactly where you want to get your information as you appear to,
the just use hard coded linking formulas

=Daily!W2

in B4 of the weekly sheet.

If you need to dynamically determine the location of the first monday in the
daily sheet from the weekly sheet, then you can do

(day(date of monday)-1)*42+2

This would be your anchor. You can combine it with Offset, or Index or
Indirect perhaps.

If you don't know the date for the first location in Weekly (B2), then I
don't know what to tell you. If you do, then I gave you the fromula to find
the day in daily.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
Hey Tom,

I do know where I am getting my date from, I just don't know how Daily
will know where since the 1st of the month will occur in anyone of the
first seven days of the week. How do I tell Weekly where to look.
Hard coding the links works if the days stay the same, which they do
in all other sheets except Weekly (and they are hard coded). Since
Weekly can start on any day of the week, I am at a loss.

-Minitman


On Sun, 30 Oct 2005 21:09:26 -0500, "Tom Ogilvy"
wrote:

If you know exactly where you want to get your information as you appear
to,
the just use hard coded linking formulas

=Daily!W2

in B4 of the weekly sheet.

If you need to dynamically determine the location of the first monday in
the
daily sheet from the weekly sheet, then you can do

(day(date of monday)-1)*42+2

This would be your anchor. You can combine it with Offset, or Index or
Indirect perhaps.

I think I understand. One question, with OFFSET how do you get it to
move up? Lets say my first location is in P41 and the next location
is in P39 and the next is in P37 and so on. How would that be
written?

-Minitman



On Sun, 30 Oct 2005 21:58:45 -0500, "Tom Ogilvy"
wrote:

If you don't know the date for the first location in Weekly (B2), then I
don't know what to tell you. If you do, then I gave you the fromula to find
the day in daily.

Put this in a cell

=Offset($P$41,2-2*row(A1),0)

then drag down the column

the next cell down P39, the row argument would be row(A2) = 2, 2-2*2 = -2
the next cell down P37, the row agument would be row(A3) = 3, 2-2*3 = -4

and so forth.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
I think I understand. One question, with OFFSET how do you get it to
move up? Lets say my first location is in P41 and the next location
is in P39 and the next is in P37 and so on. How would that be
written?

-Minitman



On Sun, 30 Oct 2005 21:58:45 -0500, "Tom Ogilvy"
wrote:

If you don't know the date for the first location in Weekly (B2), then I
don't know what to tell you. If you do, then I gave you the fromula to
find
the day in daily.

Hey Tom,

Thanks, I can make this work.

Is there any way to use the day of the month as a parameter in this
OFFSET command?



On Sun, 30 Oct 2005 22:58:18 -0500, "Tom Ogilvy"
wrote:

Put this in a cell

=Offset($P$41,2-2*row(A1),0)

then drag down the column

the next cell down P39, the row argument would be row(A2) = 2, 2-2*2 = -2
the next cell down P37, the row agument would be row(A3) = 3, 2-2*3 = -4

and so forth.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
.. .
I think I understand. One question, with OFFSET how do you get it to
move up? Lets say my first location is in P41 and the next location
is in P39 and the next is in P37 and so on. How would that be
written?

-Minitman



On Sun, 30 Oct 2005 21:58:45 -0500, "Tom Ogilvy"
wrote:

If you don't know the date for the first location in Weekly (B2), then I
don't know what to tell you. If you do, then I gave you the fromula to
find
the day in daily.

Hey Tom,

I found the DAY command. But I the formula I came up with does not
seem to work. Do you see where I messed up?

=IF(N4<0,"",OFFSET((Daily!$P$41)*N4,2-2*ROW(A1),0)

N4 is the number of days(-) before (+) or after the 1st of the month.

What I am trying to do is go down to the correct cell for each day (42
times the day(N4) from P41



On Sun, 30 Oct 2005 22:50:37 -0600, Minitman
wrote:

Hey Tom,

Thanks, I can make this work.

Is there any way to use the day of the month as a parameter in this
OFFSET command?



On Sun, 30 Oct 2005 22:58:18 -0500, "Tom Ogilvy"
wrote:

Put this in a cell

=Offset($P$41,2-2*row(A1),0)

then drag down the column

the next cell down P39, the row argument would be row(A2) = 2, 2-2*2 = -2
the next cell down P37, the row agument would be row(A3) = 3, 2-2*3 = -4

and so forth.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
. ..
I think I understand. One question, with OFFSET how do you get it to
move up? Lets say my first location is in P41 and the next location
is in P39 and the next is in P37 and so on. How would that be
written?

-Minitman



On Sun, 30 Oct 2005 21:58:45 -0500, "Tom Ogilvy"
wrote:

If you don't know the date for the first location in Weekly (B2), then I
don't know what to tell you. If you do, then I gave you the fromula to
find
the day in daily.

Found it.

=IF(N4<0,"",OFFSET((Daily!$P$41)*N4,2-2*ROW(A1),0)
Should be:
=IF(N4<0,"",OFFSET(Daily!$P$41,(42*N4)+2-2*ROW(A1),0)

This works for all of the entries that are in the active workbook.
The problem, however, is in the entries that are in the previous and
the next workbooks (the beginning of the first week and the back of
the last week). The If statement (N4<0,"",....) is to leave the
previous months' cells blank, I really need to populate these cells
with the data from the previous month.

Anyone have any idea as to how to find these?

Any help would be appreciated.

-Minitman


On Sun, 30 Oct 2005 23:43:55 -0600, Minitman
wrote:

Hey Tom,

I found the DAY command. But I the formula I came up with does not
seem to work. Do you see where I messed up?

=IF(N4<0,"",OFFSET((Daily!$P$41)*N4,2-2*ROW(A1),0)

N4 is the number of days(-) before (+) or after the 1st of the month.

What I am trying to do is go down to the correct cell for each day (42
times the day(N4) from P41



On Sun, 30 Oct 2005 22:50:37 -0600, Minitman
wrote:

Hey Tom,

Thanks, I can make this work.

Is there any way to use the day of the month as a parameter in this
OFFSET command?



On Sun, 30 Oct 2005 22:58:18 -0500, "Tom Ogilvy"
wrote:

Put this in a cell

=Offset($P$41,2-2*row(A1),0)

then drag down the column

the next cell down P39, the row argument would be row(A2) = 2, 2-2*2 = -2
the next cell down P37, the row agument would be row(A3) = 3, 2-2*3 = -4

and so forth.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
I think I understand. One question, with OFFSET how do you get it to
move up? Lets say my first location is in P41 and the next location
is in P39 and the next is in P37 and so on. How would that be
written?

-Minitman



On Sun, 30 Oct 2005 21:58:45 -0500, "Tom Ogilvy"
wrote:

If you don't know the date for the first location in Weekly (B2), then I
don't know what to tell you. If you do, then I gave you the fromula to
find
the day in daily.

I would suggest a similar approach. You would have to figure out how to
calculate the location.

Be aware that Excel dates are pretty flexible

=DATE(YEAR(TODAY()),MONTH(TODAY()),-5)

done on Oct 31, 2005, returns Sept 25, 2005.

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)
gives you the date of the last day of the previous month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
gives you the date of the last day of this month
=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)
gives you the date of the last day of the next month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)
gives the date of the first day of the next month

If you enclose any of these in the Day function

=Day( - date formula -)

then you get the numerical day number. You should be able to use
information like this to figure out where to get your data.

I found the DAY command.
I am glad you found the day function. Remember this several posts back:
(day(date of monday)-1)*42+2



--
Regards,
Tom Ogilvy



"Minitman" wrote in message
...
Found it.

=IF(N4<0,"",OFFSET((Daily!$P$41)*N4,2-2*ROW(A1),0)
Should be:
=IF(N4<0,"",OFFSET(Daily!$P$41,(42*N4)+2-2*ROW(A1),0)

This works for all of the entries that are in the active workbook.
The problem, however, is in the entries that are in the previous and
the next workbooks (the beginning of the first week and the back of
the last week). The If statement (N4<0,"",....) is to leave the
previous months' cells blank, I really need to populate these cells
with the data from the previous month.

Anyone have any idea as to how to find these?

Any help would be appreciated.

-Minitman


On Sun, 30 Oct 2005 23:43:55 -0600, Minitman
wrote:

Hey Tom,

I found the DAY command. But I the formula I came up with does not
seem to work. Do you see where I messed up?

=IF(N4<0,"",OFFSET((Daily!$P$41)*N4,2-2*ROW(A1),0)

N4 is the number of days(-) before (+) or after the 1st of the month.

What I am trying to do is go down to the correct cell for each day (42
times the day(N4) from P41



On Sun, 30 Oct 2005 22:50:37 -0600, Minitman
wrote:

Hey Tom,

Thanks, I can make this work.

Is there any way to use the day of the month as a parameter in this
OFFSET command?



On Sun, 30 Oct 2005 22:58:18 -0500, "Tom Ogilvy"
wrote:

Put this in a cell

=Offset($P$41,2-2*row(A1),0)

then drag down the column

the next cell down P39, the row argument would be row(A2) = 2, 2-2*2
= -2
the next cell down P37, the row agument would be row(A3) = 3, 2-2*3
= -4

and so forth.

--
Regards,
Tom Ogilvy


"Minitman" wrote in message
...
I think I understand. One question, with OFFSET how do you get it to
move up? Lets say my first location is in P41 and the next location
is in P39 and the next is in P37 and so on. How would that be
written?

-Minitman



On Sun, 30 Oct 2005 21:58:45 -0500, "Tom Ogilvy"
wrote:

If you don't know the date for the first location in Weekly (B2),
then I
don't know what to tell you. If you do, then I gave you the fromula
to
find
the day in daily.

Hey Tom,

Thanks for the tutorial.

This is what I was looking for.

The first time you mentioned DAY was referenced to Monday, I
needed it to also reference the first of the month since there was no
data before the first of the month. I had to look into how it worked
before I could make it work for me. It is not knowing how commands
work that make them unusable and your examples show me just how
usable they are and after digging enough to understand them. I can
then modify them and make them work for me. I thank you for taking
the time and putting up with my ignorance until the light came on.

-Minitman


On Mon, 31 Oct 2005 08:53:05 -0500, "Tom Ogilvy"
wrote:

I would suggest a similar approach. You would have to figure out how to
calculate the location.

Be aware that Excel dates are pretty flexible

=DATE(YEAR(TODAY()),MONTH(TODAY()),-5)

done on Oct 31, 2005, returns Sept 25, 2005.

=DATE(YEAR(TODAY()),MONTH(TODAY()),0)
gives you the date of the last day of the previous month.

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)
gives you the date of the last day of this month
=DATE(YEAR(TODAY()),MONTH(TODAY())+2,0)
gives you the date of the last day of the next month
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)
gives the date of the first day of the next month

If you enclose any of these in the Day function

=Day( - date formula -)

then you get the numerical day number. You should be able to use
information like this to figure out where to get your data.

I found the DAY command.
I am glad you found the day function. Remember this several posts back:
(day(date of monday)-1)*42+2