Wanna Reference Sheet in my Formula - Automatically - Please Help

[cvici]

Hello Everyone,


I currently have a formula that references data/information in different sheets. The formula is fine and work but the issue is that the sheet that is being reference needs to be selected each time. The name of the Sheet is always the same and I wanted to see if there is a Formula that I am not aware of that will automatically reference that sheet as long as the name of the sheet is reference.

This is the formula: =INDEX('23465'!E:E,MATCH("Example Title",'23465'!B:B,FALSE),1)

The reference sheet '23465"! has been manual selected when using the formula.

The moment that I re-populate an updated Sheet "23465" it gives me this issue: = =INDEX(#REF!E:E,MATCH("Example Title",#REF!B:B,FALSE),1)

So I wanted to know if there is a way that I can reference a sheet that it will always recognize as long as the name matches the sheet name "23465"

This would help me out alot because I have to do this to about 85-90 accounts that populate their information each on different sheets


If you need further details, just let me know

[Gregb11]

The moment that I re-populate an updated Sheet "23465" it gives me this issue:

Are you pasting into the sheet, or are you deleting the sheet and then copying a sheet from another Workbook into this Workbook with the same Sheet Name?
If it's the later, once you delete the sheet, it loses the reference.

[cvici]

So Our system generates a new Sheet everyday.

I populate that information in the necessary field and once I complete I then delete that tab. Which then give me that Ref Error (=INDEX(#REF!E:E,MATCH("Example Title",#REF!B:B,FALSE),1)). Which is okay because I have deleted the Source. However I would like when a new sheet is generated that it able to recognize the source sheet due to the same name or even sheet order.

What I mean by sheet order - There are 60-85 files that populate daily with new numbers (information). I have a Macro that add all the files into separate sheet onto the master file. What become a hassle is when I need to re-source the sheet reference so the formula would be complete and pull information.

The sheet name will always be the same.

[Gregb11]

That's what I thought, when you delete the sheet, it no longer has a reference to point to so it changes to #REF. Once that's done, even if you add a sheet with the same name, it doesn't know what the name was that it was pointing to. It seems like you'll have to use the INDIRECT formula then. Try this:

=INDEX(INDIRECT("'23465'!E:E"),MATCH("Example Title",INDIRECT("'23465'!B:B"),FALSE),1)

The RESULT will turn to #REF when you delete the sheet, but the formula will still reference the sheet name so when it's added back in it will work.