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("filena me",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