Referencing another Workbook with Indirect

[Marcelo Branco]

Hi,

There is a workbook named Data with 31 worksheets named sequentially 1, 2, 3 .....31 (for each day of the month) where A1, B1 and C1 house the final results of each day.

In another workbook (same directory) , Report, the user wants to summarize the data using a formula and dragging down to get the data from every day.

To get A1 i tried this
=INDIRECT("[Data.xlsx]"&ROWS($1:1)&"!A1")
copy down

It worked but i'm facing two issues:
1. If the Data workbook has not been opened before, the formula generates a #REF.
2. If opened, after closing, any new calculation at Report provokes a #REF.

Is there another way, using formulas (i prefer to avoid VBA), to do this and get the data from A1, B1 and C1 even when Data.xlsx has not been opened?

Any help would be appreciated.

M.

 

[AlphaFrog]

Why don't you just type in the exact formulas; e.g.
='[Data.xlsx]1'!A1
='[Data.xlsx]2'!A1
.etc

It's just 31 formulas where you only have to change one number. You can then drag\copy the 31 formulas to the next two columns to get B1 and C1.

 

[AlphaFrog]

Addition to post #2
To save some typing, if you have Data.xlsx open, on the Report sheet you can type in an equal sign and then click on Data.xlsx and select a sheet and a cell. Excel will fill in the reference for you.

 

[Marcelo Branco]

Why don't you just type in the exact formulas; e.g.
='[Data.xlsx]1'!A1
='[Data.xlsx]2'!A1
.etc

It's just 31 formulas where you only have to change one number. You can then drag\copy the 31 formulas to the next two columns to get B1 and C1.

Tks for your quick answer.

Yes, i can type the formulas - not so much effort - but i was curious if it was possible find out an automate solution to use in the future on a more complicated (or bigger) scenario.

M.