Macro Help

[Lisa]

I have a Daily Report that my guys have to fill out when on the job. The
worksheets for their first three days are already set up and I have a blank
worksheet for them to copy and use for additional days. We have to keep
track of daily total hours and project total hours, so like on the day 2
sheet I have a formula set up where it will pick up that days hours plus the
previous days hours and give the project total hours. Day 2 and Day 3 are
already set up but when they copy the blank sheet to start day 4 the formula
is not there.

Ex: on the day 2 sheet for project total I have =AU4+'Day 1'!AU5 and on the
day 3 sheet I have =AU4+'Day 2'!AU5

When they add a copy of the blank worksheet I cant figure out how to make
the project total formula change to =AU4+'Day 3'!AU5 because I would need the
formula to change each time to Day 4, Day 5, etc....Any ideas?

Lisa

[SimonCC]

Try this:
=AU4+INDIRECT("'Day
"&VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))-4))-1&"'!AU5")

-Simon

"Lisa" wrote:

> I have a Daily Report that my guys have to fill out when on the job. The
> worksheets for their first three days are already set up and I have a blank
> worksheet for them to copy and use for additional days. We have to keep
> track of daily total hours and project total hours, so like on the day 2
> sheet I have a formula set up where it will pick up that days hours plus the
> previous days hours and give the project total hours. Day 2 and Day 3 are
> already set up but when they copy the blank sheet to start day 4 the formula
> is not there.
>
> Ex: on the day 2 sheet for project total I have =AU4+'Day 1'!AU5 and on the
> day 3 sheet I have =AU4+'Day 2'!AU5
>
> When they add a copy of the blank worksheet I cant figure out how to make
> the project total formula change to =AU4+'Day 3'!AU5 because I would need the
> formula to change each time to Day 4, Day 5, etc....Any ideas?
>
> Lisa

[Lisa]

Ok this is way beyond my scope of knowledge, do I replace "filename" with one
of my sheet names and if so which one. I would like to have this formula on
my blank sheet so that when the guys copy this sheet to start a new day it
will already be set up for them.

"SimonCC" wrote:

> Try this:
> =AU4+INDIRECT("'Day
> "&VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))-4))-1&"'!AU5")
>
> -Simon
>
> "Lisa" wrote:
>
> > I have a Daily Report that my guys have to fill out when on the job. The
> > worksheets for their first three days are already set up and I have a blank
> > worksheet for them to copy and use for additional days. We have to keep
> > track of daily total hours and project total hours, so like on the day 2
> > sheet I have a formula set up where it will pick up that days hours plus the
> > previous days hours and give the project total hours. Day 2 and Day 3 are
> > already set up but when they copy the blank sheet to start day 4 the formula
> > is not there.
> >
> > Ex: on the day 2 sheet for project total I have =AU4+'Day 1'!AU5 and on the
> > day 3 sheet I have =AU4+'Day 2'!AU5
> >
> > When they add a copy of the blank worksheet I cant figure out how to make
> > the project total formula change to =AU4+'Day 3'!AU5 because I would need the
> > formula to change each time to Day 4, Day 5, etc....Any ideas?
> >
> > Lisa

[SimonCC]

Sorry, maybe I should have explained this a little bit. The "filename" part
is literal, so should be typed or copied as is. If you put
=CELL("filename",A1) exactly the way it is in any cell, it should give you
the full path of the file along with filename and sheetname. Only thing is
that the file has to be saved at least once (another words, not a new file)
or else it would return a blank.

Also, if your sheetname is not in the format of Day 1, Day 2, Day 3, etc, it
will not work. So just make sure your sheetname stays consistent and it
should be ok. Let me know what error you're getting please, if you can't get
it to work.

-Simon

"Lisa" wrote:

> Ok this is way beyond my scope of knowledge, do I replace "filename" with one
> of my sheet names and if so which one. I would like to have this formula on
> my blank sheet so that when the guys copy this sheet to start a new day it
> will already be set up for them.
>
> "SimonCC" wrote:
>
> > Try this:
> > =AU4+INDIRECT("'Day
> > "&VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))-4))-1&"'!AU5")
> >
> > -Simon
> >
> > "Lisa" wrote:
> >
> > > I have a Daily Report that my guys have to fill out when on the job. The
> > > worksheets for their first three days are already set up and I have a blank
> > > worksheet for them to copy and use for additional days. We have to keep
> > > track of daily total hours and project total hours, so like on the day 2
> > > sheet I have a formula set up where it will pick up that days hours plus the
> > > previous days hours and give the project total hours. Day 2 and Day 3 are
> > > already set up but when they copy the blank sheet to start day 4 the formula
> > > is not there.
> > >
> > > Ex: on the day 2 sheet for project total I have =AU4+'Day 1'!AU5 and on the
> > > day 3 sheet I have =AU4+'Day 2'!AU5
> > >
> > > When they add a copy of the blank worksheet I cant figure out how to make
> > > the project total formula change to =AU4+'Day 3'!AU5 because I would need the
> > > formula to change each time to Day 4, Day 5, etc....Any ideas?
> > >
> > > Lisa

[Lisa]

Ok I copied the above formula and pasted it to the cell where I want the
result. On my sheet title blank sheet (which is the one they copy to start a
new days report) I got a #value error. I removed the formula, copied the
blank sheet and renamed it Day 4, pasted the formula into the cell on the Day
4 sheet and got a #ref error.

Lisa

"SimonCC" wrote:

> Sorry, maybe I should have explained this a little bit. The "filename" part
> is literal, so should be typed or copied as is. If you put
> =CELL("filename",A1) exactly the way it is in any cell, it should give you
> the full path of the file along with filename and sheetname. Only thing is
> that the file has to be saved at least once (another words, not a new file)
> or else it would return a blank.
>
> Also, if your sheetname is not in the format of Day 1, Day 2, Day 3, etc, it
> will not work. So just make sure your sheetname stays consistent and it
> should be ok. Let me know what error you're getting please, if you can't get
> it to work.
>
> -Simon
>
> "Lisa" wrote:
>
> > Ok this is way beyond my scope of knowledge, do I replace "filename" with one
> > of my sheet names and if so which one. I would like to have this formula on
> > my blank sheet so that when the guys copy this sheet to start a new day it
> > will already be set up for them.
> >
> > "SimonCC" wrote:
> >
> > > Try this:
> > > =AU4+INDIRECT("'Day
> > > "&VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))-4))-1&"'!AU5")
> > >
> > > -Simon
> > >
> > > "Lisa" wrote:
> > >
> > > > I have a Daily Report that my guys have to fill out when on the job. The
> > > > worksheets for their first three days are already set up and I have a blank
> > > > worksheet for them to copy and use for additional days. We have to keep
> > > > track of daily total hours and project total hours, so like on the day 2
> > > > sheet I have a formula set up where it will pick up that days hours plus the
> > > > previous days hours and give the project total hours. Day 2 and Day 3 are
> > > > already set up but when they copy the blank sheet to start day 4 the formula
> > > > is not there.
> > > >
> > > > Ex: on the day 2 sheet for project total I have =AU4+'Day 1'!AU5 and on the
> > > > day 3 sheet I have =AU4+'Day 2'!AU5
> > > >
> > > > When they add a copy of the blank worksheet I cant figure out how to make
> > > > the project total formula change to =AU4+'Day 3'!AU5 because I would need the
> > > > formula to change each time to Day 4, Day 5, etc....Any ideas?
> > > >
> > > > Lisa

[SimonCC]

Ok I just tried it myself copying from the post and pasting to Excel, and I
think I know what's wrong. When you paste the formula into Excel, is there a
linebreak/carriage return in the formula after the "'Day part? Because the
formula was too long to fit on one line, the post automatically inserted the
linebreak/carriage return on the page. So just delete the linebreak in the
formula so the "'Day " part is on one line (keep the space though). Let me
know please if that works. Hopefully that's the problem.

-Simon

"Lisa" wrote:

> Ok I copied the above formula and pasted it to the cell where I want the
> result. On my sheet title blank sheet (which is the one they copy to start a
> new days report) I got a #value error. I removed the formula, copied the
> blank sheet and renamed it Day 4, pasted the formula into the cell on the Day
> 4 sheet and got a #ref error.
>
> Lisa
>
> "SimonCC" wrote:
>
> > Sorry, maybe I should have explained this a little bit. The "filename" part
> > is literal, so should be typed or copied as is. If you put
> > =CELL("filename",A1) exactly the way it is in any cell, it should give you
> > the full path of the file along with filename and sheetname. Only thing is
> > that the file has to be saved at least once (another words, not a new file)
> > or else it would return a blank.
> >
> > Also, if your sheetname is not in the format of Day 1, Day 2, Day 3, etc, it
> > will not work. So just make sure your sheetname stays consistent and it
> > should be ok. Let me know what error you're getting please, if you can't get
> > it to work.
> >
> > -Simon
> >
> > "Lisa" wrote:
> >
> > > Ok this is way beyond my scope of knowledge, do I replace "filename" with one
> > > of my sheet names and if so which one. I would like to have this formula on
> > > my blank sheet so that when the guys copy this sheet to start a new day it
> > > will already be set up for them.
> > >
> > > "SimonCC" wrote:
> > >
> > > > Try this:
> > > > =AU4+INDIRECT("'Day
> > > > "&VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))-4))-1&"'!AU5")
> > > >
> > > > -Simon
> > > >
> > > > "Lisa" wrote:
> > > >
> > > > > I have a Daily Report that my guys have to fill out when on the job. The
> > > > > worksheets for their first three days are already set up and I have a blank
> > > > > worksheet for them to copy and use for additional days. We have to keep
> > > > > track of daily total hours and project total hours, so like on the day 2
> > > > > sheet I have a formula set up where it will pick up that days hours plus the
> > > > > previous days hours and give the project total hours. Day 2 and Day 3 are
> > > > > already set up but when they copy the blank sheet to start day 4 the formula
> > > > > is not there.
> > > > >
> > > > > Ex: on the day 2 sheet for project total I have =AU4+'Day 1'!AU5 and on the
> > > > > day 3 sheet I have =AU4+'Day 2'!AU5
> > > > >
> > > > > When they add a copy of the blank worksheet I cant figure out how to make
> > > > > the project total formula change to =AU4+'Day 3'!AU5 because I would need the
> > > > > formula to change each time to Day 4, Day 5, etc....Any ideas?
> > > > >
> > > > > Lisa

[Lisa]

It works, It works, Thank you a million times over. Next question.....In an
earlier post you said it will not work if the sheetname is not in the format
of Day 1, Day 2, etc.... If the field supervisor wants the sheetname to be
the date of the workday how would I change the formula to reflect that
instead of the Day 1, Day 2.

"SimonCC" wrote:

> Ok I just tried it myself copying from the post and pasting to Excel, and I
> think I know what's wrong. When you paste the formula into Excel, is there a
> linebreak/carriage return in the formula after the "'Day part? Because the
> formula was too long to fit on one line, the post automatically inserted the
> linebreak/carriage return on the page. So just delete the linebreak in the
> formula so the "'Day " part is on one line (keep the space though). Let me
> know please if that works. Hopefully that's the problem.
>
> -Simon
>
> "Lisa" wrote:
>
> > Ok I copied the above formula and pasted it to the cell where I want the
> > result. On my sheet title blank sheet (which is the one they copy to start a
> > new days report) I got a #value error. I removed the formula, copied the
> > blank sheet and renamed it Day 4, pasted the formula into the cell on the Day
> > 4 sheet and got a #ref error.
> >
> > Lisa
> >
> > "SimonCC" wrote:
> >
> > > Sorry, maybe I should have explained this a little bit. The "filename" part
> > > is literal, so should be typed or copied as is. If you put
> > > =CELL("filename",A1) exactly the way it is in any cell, it should give you
> > > the full path of the file along with filename and sheetname. Only thing is
> > > that the file has to be saved at least once (another words, not a new file)
> > > or else it would return a blank.
> > >
> > > Also, if your sheetname is not in the format of Day 1, Day 2, Day 3, etc, it
> > > will not work. So just make sure your sheetname stays consistent and it
> > > should be ok. Let me know what error you're getting please, if you can't get
> > > it to work.
> > >
> > > -Simon
> > >
> > > "Lisa" wrote:
> > >
> > > > Ok this is way beyond my scope of knowledge, do I replace "filename" with one
> > > > of my sheet names and if so which one. I would like to have this formula on
> > > > my blank sheet so that when the guys copy this sheet to start a new day it
> > > > will already be set up for them.
> > > >
> > > > "SimonCC" wrote:
> > > >
> > > > > Try this:
> > > > > =AU4+INDIRECT("'Day
> > > > > "&VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))-4))-1&"'!AU5")
> > > > >
> > > > > -Simon
> > > > >
> > > > > "Lisa" wrote:
> > > > >
> > > > > > I have a Daily Report that my guys have to fill out when on the job. The
> > > > > > worksheets for their first three days are already set up and I have a blank
> > > > > > worksheet for them to copy and use for additional days. We have to keep
> > > > > > track of daily total hours and project total hours, so like on the day 2
> > > > > > sheet I have a formula set up where it will pick up that days hours plus the
> > > > > > previous days hours and give the project total hours. Day 2 and Day 3 are
> > > > > > already set up but when they copy the blank sheet to start day 4 the formula
> > > > > > is not there.
> > > > > >
> > > > > > Ex: on the day 2 sheet for project total I have =AU4+'Day 1'!AU5 and on the
> > > > > > day 3 sheet I have =AU4+'Day 2'!AU5
> > > > > >
> > > > > > When they add a copy of the blank worksheet I cant figure out how to make
> > > > > > the project total formula change to =AU4+'Day 3'!AU5 because I would need the
> > > > > > formula to change each time to Day 4, Day 5, etc....Any ideas?
> > > > > >
> > > > > > Lisa

[SimonCC]

The formula I gave relies on the fact that sheet names will always be "Day "
plus a number. So basically just have to extract the number part from the
current, then "Day "&number-1 will simply refer to the previous sheet.

However, if the sheetname is like dates, then it becomes a bit more
complicated for beginning of the month/year. For instance if it's 8/1/2006
and your sheetname is 08-01-06, subtracting 1 from the middle will give you
08-00-06 which is wrong.

I think it's doable if I just convert the date format to a numbered value.
I can give it a try, but first you should let me know what format the
sheetname is going to be. For example, if it was today, would the sheetname
look like 07-27-06, 060727, 07-27, 2006-07-27, 0727, or something else?

-Simon

"Lisa" wrote:

> It works, It works, Thank you a million times over. Next question.....In an
> earlier post you said it will not work if the sheetname is not in the format
> of Day 1, Day 2, etc.... If the field supervisor wants the sheetname to be
> the date of the workday how would I change the formula to reflect that
> instead of the Day 1, Day 2.
>

[Lisa]

If possible they would like the sheet name as a date. So if today was the
first day of the job instead of Day 1 it would be 7-27-06, tomorrow instead
of Day 2 would be 7-28-06, etc...

"SimonCC" wrote:

> The formula I gave relies on the fact that sheet names will always be "Day "
> plus a number. So basically just have to extract the number part from the
> current, then "Day "&number-1 will simply refer to the previous sheet.
>
> However, if the sheetname is like dates, then it becomes a bit more
> complicated for beginning of the month/year. For instance if it's 8/1/2006
> and your sheetname is 08-01-06, subtracting 1 from the middle will give you
> 08-00-06 which is wrong.
>
> I think it's doable if I just convert the date format to a numbered value.
> I can give it a try, but first you should let me know what format the
> sheetname is going to be. For example, if it was today, would the sheetname
> look like 07-27-06, 060727, 07-27, 2006-07-27, 0727, or something else?
>
> -Simon
>
> "Lisa" wrote:
>
> > It works, It works, Thank you a million times over. Next question.....In an
> > earlier post you said it will not work if the sheetname is not in the format
> > of Day 1, Day 2, etc.... If the field supervisor wants the sheetname to be
> > the date of the workday how would I change the formula to reflect that
> > instead of the Day 1, Day 2.
> >

[SimonCC]

Turned out it wasn't so bad when I actually tried to do it. Here it is:
=AU4+INDIRECT("'"&TEXT(VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))-1,"m-d-yy")&"'!AU5")

When August comes around, I'm assuming the sheet name is going to be 8-1-06.
If for some reason people decide to put 8-01-06, just change the "m-d-yy"
part to "m-dd-yy".

-Simon

"Lisa" wrote:

> If possible they would like the sheet name as a date. So if today was the
> first day of the job instead of Day 1 it would be 7-27-06, tomorrow instead
> of Day 2 would be 7-28-06, etc...
>
> "SimonCC" wrote:
>
> > The formula I gave relies on the fact that sheet names will always be "Day "
> > plus a number. So basically just have to extract the number part from the
> > current, then "Day "&number-1 will simply refer to the previous sheet.
> >
> > However, if the sheetname is like dates, then it becomes a bit more
> > complicated for beginning of the month/year. For instance if it's 8/1/2006
> > and your sheetname is 08-01-06, subtracting 1 from the middle will give you
> > 08-00-06 which is wrong.
> >
> > I think it's doable if I just convert the date format to a numbered value.
> > I can give it a try, but first you should let me know what format the
> > sheetname is going to be. For example, if it was today, would the sheetname
> > look like 07-27-06, 060727, 07-27, 2006-07-27, 0727, or something else?
> >
> > -Simon
> >
> > "Lisa" wrote:
> >
> > > It works, It works, Thank you a million times over. Next question.....In an
> > > earlier post you said it will not work if the sheetname is not in the format
> > > of Day 1, Day 2, etc.... If the field supervisor wants the sheetname to be
> > > the date of the workday how would I change the formula to reflect that
> > > instead of the Day 1, Day 2.
> > >

[Lisa]

That is perfect, thank you so very much for all your help.

"SimonCC" wrote:

> Turned out it wasn't so bad when I actually tried to do it. Here it is:
> =AU4+INDIRECT("'"&TEXT(VALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))-1,"m-d-yy")&"'!AU5")
>
> When August comes around, I'm assuming the sheet name is going to be 8-1-06.
> If for some reason people decide to put 8-01-06, just change the "m-d-yy"
> part to "m-dd-yy".
>
> -Simon
>
> "Lisa" wrote:
>
> > If possible they would like the sheet name as a date. So if today was the
> > first day of the job instead of Day 1 it would be 7-27-06, tomorrow instead
> > of Day 2 would be 7-28-06, etc...
> >
> > "SimonCC" wrote:
> >
> > > The formula I gave relies on the fact that sheet names will always be "Day "
> > > plus a number. So basically just have to extract the number part from the
> > > current, then "Day "&number-1 will simply refer to the previous sheet.
> > >
> > > However, if the sheetname is like dates, then it becomes a bit more
> > > complicated for beginning of the month/year. For instance if it's 8/1/2006
> > > and your sheetname is 08-01-06, subtracting 1 from the middle will give you
> > > 08-00-06 which is wrong.
> > >
> > > I think it's doable if I just convert the date format to a numbered value.
> > > I can give it a try, but first you should let me know what format the
> > > sheetname is going to be. For example, if it was today, would the sheetname
> > > look like 07-27-06, 060727, 07-27, 2006-07-27, 0727, or something else?
> > >
> > > -Simon
> > >
> > > "Lisa" wrote:
> > >
> > > > It works, It works, Thank you a million times over. Next question.....In an
> > > > earlier post you said it will not work if the sheetname is not in the format
> > > > of Day 1, Day 2, etc.... If the field supervisor wants the sheetname to be
> > > > the date of the workday how would I change the formula to reflect that
> > > > instead of the Day 1, Day 2.
> > > >