|Your interchanging of the terms "sheet" and "file" is what makes your question confusing.|
t.htm is not a sheet, it's a workbook.
callout is not a sheet, it's a workbook.
You don't save or open a sheet, you save and open a workbook. That workbook will contain 1 or more sheets.
When you use this formula:
the t.htm is the file name, the t is the sheet name,
When you said "I really just need it to be something like =t.htm!$I$7 you are asking for a formula that references a sheet named t.htm, not the file t.htm.
OK, now that we've cleared that up, if I understand your issue, it's this:
If you hard code the folder name into the formula (e.g. 02-15-10) the formula will link to "old data". You'd like the formula to reference a different folder name - i.e. the "newest" one - at any given time.
I can think of 2 possible solutions.
Save the latest file twice: Once into a folder for that day and once into a folder called e.g. "Most Recent". Then you could just reference the file in the Most Recent folder for your latest data.
This would not have to be a manual operation. Back in the days of floppies, I used have a macro that I called "Dual_Save". When I clicked the button, the file would get saved into 2 locations: The client's folder on my hard drive and then onto a floppy so I could give it to my clients. The code would pop up the standard Save As dialog box so that I could create a new folder for each client on my hard drive, then the file would get saved to that folder and then (automatically) to the floppy. In your case, instead of saving it to a floppy, your second save would go to the folder called Most Recent.
Consider the use of the INDIRECT function.
The INDIRECT function uses a text string to build a reference to a cell. You can include both text (within quotes) and cell references, the results of a formula, etc. to "build" the reference.
For example, all of these will reference Sheet2!A4:
=INDIRECT("SHEET2!A" & A2) (assuming A2 contains 4 or a formula that evaluates to 4)
=INDIRECT("SHEET" & IF(10>5, 2, 3) & "!A4") (since the IF function evaluates to 2)
So, how does this help you?
Well, if you could use a cell someplace that holds a value that represents the folder name that keeps changing, your link formulas might look something like this:
=INDIRECT("'C:\Users\Acomputer\Desktop\test\" & $A$1 & "\1\t_files\[t.htm]t'!$I$7")
Whatever value is in A1 - and assuming it matched the folder name exactly - would be used as the folder name in the formula.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.