Forum Discussion
Sumifs with indirect method
The other months work with this function : =SUMIFS('[12 MO ACTUAL_FY16.XLS]011000-030 - Acct Detail'!D$11:D$108,'[12 MO ACTUAL_FY16.XLS]011000-030 - Acct Detail'!$A$11:$A$108,dayschool!$B12)I am trying to put together financial statements into another file. And yes it is used for other tabs that flow into each other, so I am not trying to hand enter the data each month. So I am referencing to another workbook in the folder that we pull every month from the same source and is saved directly over the last file it as if it were the same. I also understand that when using the sumifs function, that the workbook must be opened but when entering the indirect function in returns a reference error. Maybe I am missing some information about the use of the indirect method. Also the only reason I need to do so is because the data I receive must be delimited (adjusted) to use in the file I was given. It works for the sumifs function alone, but since the data is in the same format every month when I adjust the column it then changes my function in the sheet that I am updating, which is why I am trying to use the indirect function because once it is adjusted the reference will be in the C column to begin. Please help or know of any sources, I have done a decent amount of research into it already. Thanks.
- I expect your formula should be:
=SUMIFS(INDIRECT("'[12 MO ACTUAL_FY16.XLS]011000-030 - Acct Detail'!D$11:D$108"),INDIRECT("'[12 MO ACTUAL_FY16.XLS]011000-030 - Acct Detail'!$A$11:$A$108"),dayschool!$B12)Thanks for the response, and finding the errors, it worked for the one column it was in. But when copied to other cells it still referenced the original column. However, I was able to find a solution using a sum(if( and index function that allowed it stretch over to the other columns as well as still referencing the other workbook while being closed.
Thanks,
Ant
- Excellent. I always prefer solutions which don't use INDIRECT over any other alternative.
