Jul 27 2020 04:42 AM
We have "Work Orders" files on our server Z:, they are all named by "Job Number" i.e. 106574.
Z:\REPORTS\Work Orders\106574.xlsx
The total profit or loss on a job is in cell H52 in all cases.
NOW, me on my computer, on the network, want to make simply monthly reports, in EXCEL by just typing in the job number and having the next cell populate with the value.
THEREFORE, if I type 106574 in cell A1, I would like cell A2 to populate with the value of: 106574.xlsx, H52
SOMETHING LIKE: ='Z:\REPORTS\Work Orders\[106574.xlsx]Sheet1'!H52
BUT: For some reason, this keeps taking me to my C: drive and not the Z: drive.
ALSO: I would like to have my link referenced to cell A1..........
'Z:\REPORTS\Work Orders\[A1.xlsx]Sheet1'!H52........I know this is incorrect, but you get my point.
INDIRECT??????? function????
Any help out there? Thank You,
Chris
Jul 27 2020 06:08 AM
Hi @chrisahlf,
You are correct, "=indirect" should do the trick for you...
=INDIRECT("'Z:\REPORTS\Work Orders\["&A1&".xlsx]Sheet1'!$H$52")
Ben