Reference closed workbook/worksheet cells

0
votes

I'm successfully using the following formula to reference cells in closed Excel workbooks but would like a cleaner look that removes the zeros in columns.

='C:\Users\HP 8200 Elite\Desktop\Performance\[MSA.xlsx]QQQ'!A2

Can I do something like this below?

=IF('C:\Users\HP 8200 Elite\Desktop\Performance\[MSA.xlsx]QQQ'!A2="","",QQQ!A2)

Doesn't seem to work right. I got something similar to work in a workbook that references its own worksheets:

=IF(QQQ!A2="","",QQQ!A2)

Just having problems with a closed file.

1
excel
Can't replicate this. What do you get if you open the target file, type the formula you need, and then close it? – BigBen
Doesn’t get that far. Just errors in the open file when referencing the closed one – hidefguy
I mean open MSA.xlsx, then in the workbook that has this formula in it, re-type it, =IF(... then select QQQ!A2 in the open MSA workbook...="",""... and so on. That shouldn't give you any errors. – BigBen
I’ll try it tomorrow when back in office. To be continued :) – hidefguy

1 Answers

0
votes

If you are retrieving text values but receiving zeroes when referencing a blank cell then concatenate the formulas with a zero-length string and you can avoid the loop.

='C:\Users\HP 8200 Elite\Desktop\Performance\[MSA.xlsx]QQQ'!A2&""

If you are trying to retrieve numbers then this would effectively turn true numbers into text that looks like numbers and that should be avoided. However in retrospect, you shouldn't be that concerned about retrieving a zero in lieu of a true number as there are many methods of avoiding the display of a zero value in A cell.