Is there a way to error handle a problem with a formula reference error when attempting to create a reference to a worksheet that does not exist? I am trying to create a master spreadsheet that will access multiple different files that will contain different products at different times. This will ultimately be used to step through vendors and send out letters. I want the master letter file to contain all products and depending which sub file that is linked will populate the index / match for whatever tabs it finds.
For example. For store X that is in cell E3, it will index/match the correct tab in the linked sheets and bring back the product indicated. If it doesn't find that product tab in the linked worksheet, it will leave it blank.
So in this case, this attached worksheet I have a apples worksheet in my linked file, but no oranges tab. So this is what I'd like to see:
But when I type =INDEX('[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$P$5:$P$237,MATCH($E$3,'[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$A$5:$A$237,0))
(and the linked file doesn't have an Oranges Tab) I get this error.
Obviously - because the Oranges Summary tab does not exist.
(note: in my summary files column P is where the data is, and column A is where the stores are, the index/match is working fine - 48 is the correct number of apples in this circumstance)
I have tried "If Error", but that still gives me the "Excel found a problem with one or more formula references in this worksheet" error.
Tried this - does not work: =iferror(INDEX('[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$P$5:$P$237,MATCH($E$3,'[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$A$5:$A$237,0)),"")
I was wondering if there's a workaround. I don't want to fix the error, I want to error handle it so when there is an "Oranges Summary" tab, it will populate the oranges data and when there is not, it will be blank.
Any help would be greatly appreciated.
Thanks.
For example. For store X that is in cell E3, it will index/match the correct tab in the linked sheets and bring back the product indicated. If it doesn't find that product tab in the linked worksheet, it will leave it blank.
So in this case, this attached worksheet I have a apples worksheet in my linked file, but no oranges tab. So this is what I'd like to see:
But when I type =INDEX('[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$P$5:$P$237,MATCH($E$3,'[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$A$5:$A$237,0))
(and the linked file doesn't have an Oranges Tab) I get this error.
Obviously - because the Oranges Summary tab does not exist.
(note: in my summary files column P is where the data is, and column A is where the stores are, the index/match is working fine - 48 is the correct number of apples in this circumstance)
I have tried "If Error", but that still gives me the "Excel found a problem with one or more formula references in this worksheet" error.
Tried this - does not work: =iferror(INDEX('[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$P$5:$P$237,MATCH($E$3,'[Production by Dealer 10-27-22.xlsx]Oranges Summary'!$A$5:$A$237,0)),"")
I was wondering if there's a workaround. I don't want to fix the error, I want to error handle it so when there is an "Oranges Summary" tab, it will populate the oranges data and when there is not, it will be blank.
Any help would be greatly appreciated.
Thanks.
