Ask Your Question

How to reference an old document in a formula

asked 2018-03-01 20:10:42 +0200

I have a running tabulation of the gallons of 9 different products i have on my truck. I save the files as "03-01-2018", "03-02-2018" etc etc etc.

What I would like to know is how (either by formula or macro) to reference a cell in yesterdays file. I already have a cell in the document that has the date in it. When I open "#Daily Tabulations.ods", I want it to look at yesterdays file (Using A2 which has the date on the miscellaneous sheet -1 preferably), as well as on the miscellaneous sheet, at cells B7:B14, and use those to fill in the cells A2, C2, and E2 on gasoline and diesel sheets, and just A2 and C2 on the Kerosene sheet. But only if they're empty. I don't want it to replace data. I will be using this all year so I need it to only look at the previous day. I also need it to have if statements (maybe?) So if it's 3/5/18 and the last one I have is 3/1/18 because I had a 3 day weekend, it will use that file. Sorry for asking for so much and sorry if I've confused you.

I have attached 2 files. The first one is today's running totals. The second is my template that I open each day.


#Daily Tabulations.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-03-03 14:44:12 +0200

m.a.riosv gravatar image

It's possible construct the link with INDIRECT.

If A1 is the cell with the file name:

=INDIRECT("'file:///FILEPATH/"&A1&".ods'#$'Miscellaneous Information'.A7")
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-01 20:10:42 +0200

Seen: 24 times

Last updated: Mar 03 '18