cell reference in a worksheet (office 2000)

[alissa1]

hi, i have a relatively easy question for someone, but difficult for me. i'm in a workbook and i'm trying to keep a daily report with a front worksheet and a back worksheet per day. they are named by date (i.e. 030604 f - for front, and 030604 b - for back). i'm trying to keep data flowing and i'm wondering how to refer to a specific sheet each day. i would like to get data from a certain range of cells from march 2 front and refer to it in a different place on march 3 front. i would use something like "RefOnPrevSheet('g4)" or whatever, but there is the back of each day's sheet in between. i need to pull up information on each day and put it in a column named "previous" referring to the former day's "total." any suggestions?

additionally, how could i do the same for a range of cells in a different file? i would like to bring the crew's in-times from one daily file and put it on the back of another worksheet in a different file each day. i would do this for approximately 30 days.

thank you for your assistance in advance! i hope someone can help me.

[sdckapr]

This megaformula will return from the cell G$ on the date before (if you have them named as you describe):

=INDIRECT("'"&TEXT((REPLACE(REPLACE(LEFT(MID(CELL( "filename",A1),FIND("]",CELL("filename",A1))+1,33),6),5,0,"/"),3,0,"/"))-1,"mmddyy")&RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,33),2)&"'!G4")

It gets the fullname, and extracts out the sheet name. From this it extracts subtracts 1 from the day and then replaces it as the name to use in the indirect formula. You might want to create user defined function: it would be "cleaner".

CELL("filename",A1) gets the full path filename and sheetname
MID(xx,FIND("]",xx)+1,33) gets the sheetname
LEFT(xx,6) gets the "date"
TEXT(REPLACE(REPLACE(xx,5,0,"/"),3,0,"/")-1,"mmddyy")&RIGHT(xx,2) gets the date before and adds the "suffix"
INDIRECT("'"&xx&"'!G4") gets you the reference to the sheet before in G4

You can do similarly (only adding the filename in the indirect). The form for the indirect will be:

=indirect("'[" & Filename.xls & "]" & SheetName &"'!G4")
where filename and sheetname are the file and sheet names you want to get "indirectly"

The only problem with indirect is that it requires the other file to be open. Laurent Longre's has an addin function that can handle closed workbooks. The <img src=/S/free.gif border=0 alt=free width=30 height=15> MoreFunc add-in can be downloaded.

Steve

[HansV]

If you put a cell on each worksheet with the date (i.e. '030604 f' and '030604 b' contain a cell with the date 03/06/2004, '022904 f' and '022904 b' contain a cell with the date 02/29/2004), you can use the INDIRECT function. Say that the date is in cell A1, and you want to refer to cell G4 on the 'front' sheet for the previous day. You could use this function:<pre>=INDIRECT("'"&TEXT(A1-1,"mmddyy")&" b'!G4")</pre>

Note: if you install the excellent FREE Morefunc add-in from Laurent Longre, you can use the SHEETNAME function instead of putting the date in a cell.

You can also use INDIRECT to refer to other workbooks, but only if they are open. The Morefunc add-in contains a function INDIRECT.EXT that also works for closed workbooks.

[LegareColeman]

I'm not positive I understand exactly what you are trying to do, however, it think that the INDIRECT function may be what you are looking for. I will give you an example of how this can be used and you can adapt that to what you are trying to do. Lets say that you are on sheet "030704 f" and you want to refer to cell A2 on sheet "030704 b". Lets say that cell A1 on sheet "030704 f" contains an Excel date that matches the date used in the name of the two sheets. Then you can use a formula like this:

<pre>=INDIRECT("'"&TEXT(A1,"mmddyy")&" b'!A2")
</pre>

[sdckapr]

Hans and Legare,
You guys took the easy way out, forcing an entry of the date or date before to be on the cell somewhere. Now, where is the fun in that?

The "messy challenge" is getting excel to try to figure it for itself. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Steve

[LegareColeman]

Well, if it were my worksheet, I would also have a worksheet change event that would rename the worksheet when the date cell changed.

[HansV]

Steve,

I'll gladly leave the "messy challenges" to you. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

[sdckapr]

And if it were mine, it would be a database and not have separate sheets for each day. 1 master sheet and possibly (if needed) an "output sheet" in which you could enter a date and it would just grab the appropriate data from the database and have it in a form for display or printing.

But the question wasn't about design, only about the formula. Even I were to use this approach, I would break the megaformula into subformulas (which is how it was created) that refer to the earlier "part".

Steve

[alissa1]

what would it take for me to attach the workbook and see if you could figure out a better way for this. i am absolutely positive that this is really quite simple, but being a blonde and short on time (and having a limited education in excel), i am a bit perplexed as to the best way to handle this.

[LegareColeman]

Below the area where you type you message is a box labeled "Attach a file - 100K Max." Just click on the browse button and find the file and click on it to put its path and name in the box. Be sure to do this after previewing or spell checking your message as those will remove the file from the the box.

[HansV]

In addition to Legare's advice:
- Remove or modify sensitive information
- If the workbook is too large, create a zip file from it, and attach the zip file.

[alissa1]

ok, here it is. as you can see from the front and back, this is material called a production report for the motion picture industry. i take the information from a form very similar to this called a "call sheet" and then at the end of day, translate the material to a production report which give us our status as to how much film we shot each day, how many hours people worked, how many pages of our script we got, the locations where we shot, etc. i would like to be able to pull the information from day 1 to day 2 in certain areas. for instance, instead of having to retype each day's previous film totals, i would like the following day to reference the totals from the day before under the column marked "previous." i have some simple calculations already for totaling up grand inventory and the like, but i'm stumped on the "indirect" command and how that might actually work.

additionally, on the back of the sheet, i have to fill in everyone's start time on a daily basis. what i was hoping to also do is pull that information from another file called call sheets and see about doing it the same way. i will have individual call sheets in a file, with one front and one back. they are sent to me each day on a disk. the back of the call sheet is similar to the back of the production report, but it shows only the times that people have to be on set.

thank you for your input and assistance. what do yall think?

[sdckapr]

Upon a quick look thru, to me, this looks like it would be better handled in a true database (like access).

I will look a little in more detail, when I get a chance to digest it and try and understand it better, and give some "excel"-based recommendations for a multitabled workbook, but access has builtin capabilities that excel can't do as well for these types of relational tables.

Steve

[alissa1]

in terms of an access database, i've been trying to work on things like that for a while with someone, but of course time is always pressing when it's a job on the side. do you write this kind of thing? what's your location and would you be interested/willing to help me in a more detailed fashion if it were financially worth your while? [img]/forums/images/smilies/smile.gif[/img]

[sdckapr]

I don't really work with Access: I haven't had a need. I did reference your post to the other WMVPs and Mods and maybe someone else might get back to you.

Steve