What is an alternative to Index function?

[colinheslop1984]

Currently using this formula to sum information from a large workbook. I want to link 5 workbooks so that certain info from workbook 1 will be visible all other workbooks and vice versa.

=SUMIF(INDIRECT("'"&$K$9&"'!c$12:c$25"),F$10:F$23,INDIRECT("'"&$K$9&"'!h$12:h$25"))

only problem with the above formula is that it returns the #REF error unless all workbooks are open.

What would be the alternative work around?

 

[etaf]

you cannot use a indirect() on a closed workbook
indirect_ext() was the alternative - but i don't know if it works passed version 2010
http://www.ashishmathur.com/tag/indirect-ext/

 

[colinheslop1984]

No other alternatives? Even if its not Index function

 

[RoryA]

Why are you using INDIRECT?

FYI SUMIF also won’t work with a closed workbook, but you can use SUMPRODUCT instead.

 

[colinheslop1984]

I have a 5 workbooks each laid out the same, gathering info for 4 locations. Each workbook has 104 sheets within it containing data, 1 week = 1 sheet, 2 years of data.

I have an overview page on each workbook that lets me filter which weeks I want to view the data for (K9 in the below formula refers to this filter, hence the indirect). But then the data also has to meet a criteria (c12:c25, which are just names). This works fine for each individual workbook.

=SUMIF(INDIRECT("'"&$K$9&"'!c$12:c$25"),F$10:F$23,INDIRECT("'"&$K$9&"'!h$12:h$25"))

I just want to take the same concept of this formula to gather info for the same ranges from each of the 5 workbooks in just the same way, satisfying the criteria but for the info to be visible on each individual workbook, without them all being open. Below is what I have for the minute, which works if the source sheets are open.

=SUMIF(INDIRECT("'[JAMIE SHEET LEEDS.xlsx]"&$C$5&"'!C$12:C$25"),$C9,INDIRECT("'[JAMIE SHEET LEEDS.xlsx]"&$C$5&"'!J$12:J$25"))

I just want an alternative solution/function/formula to get the results I need

Hope that makes sense

 

[RoryA]

Honestly, that’s a terrible setup for reporting. You should really be using a database, or any layout that doesn’t involve so many separate sheets.

Do you have a database program you could use? Or Power Query?

 

[colinheslop1984]

Nope haven't got anything like that.

Not sure how else to lay the info out. Im just sticking to what I know but have hit a wall