Constructing cell references to another sheet without INDIRECT (or any volatile function)

Hi all.

I'm still looking to move away from volatile functions but am stumped on this one. Where these cells hold the following:

A1 = Sheet Name
A2 = Column Letter
A3 = Row Number

..I can easily reference a cell on another sheet by using:

=INDIRECT("'"&A1&"'!"&A2&A3)

I have need to construct something like this multiple times where the references will change based on using different sources other than A1, A2 and A3.

But INDIRECT is volatile, and I can't seem to make it work with INDEX.

Are there any non-volatile functions that I can use? Or is the very nature of doing this volatile in itself?

With thanks as always - will add reputation and mark complete as soon as someone is able to help!

Oh well, I thought that might be the case. INDIRECT it is!

Originally Posted by AliGW

Are you not interested in my possible alternative, then?

I think, if I understand your suggestion correctly and based on the use case and how I've structured my workbook, it sadly won't be possible. Plus it's a work document which I wouldn't be able to share regardless, unfortunately.

Your help is, as always Ali, appreciated - hence the Reputation I left upon your first reply!

Originally Posted by 6StringJazzer

The hard part here is the sheet name. I can't think of another formula solution to reference cells with a dynamic sheet name other than INDIRECT. (Someone else may.)

One solution is using VBA to build the formulas as needed. Any time A1, A2, or A3 changes the code would rebuild the formula as a static formula.

I'm also trying to avoid VBA though, which I appreciate isn't always everyone else's preferences but it's mine on this occasion. So if such an alternate formula does exist, that would be fantastic!

Et voila!

Constructed References without INDIRECT.xlsx

This is a dummy structure but, in reality, there's a ton more info on each tab and the tabs run to higher numbers. Obviously they'll also adjust dynamically if one's deleted, which sometimes will happen and a new one created in its place.

Looking forward to your thoughts as always Ali and thank you in advance.

Thank you as always Ali. I'm not familiar with this function.

When I added TAB 5 into the mix, the summary sheet didn't update automatically and I had to change the formula to capture it. Is this expected behaviour?

I can't add reputation again sadly as I've done it too recently, but you've gone over and above as usual Ali. I'm not familiar with this function at all, but it looks like it's working very well indeed.

In the real-life version my data is drawing from lots of difference places rather than neatly in the columns in the dummy version, but I'm assuming I can easily modify it for that. I'd still be curious to know if a non-volatile function exists that's similar to INDIRECT but this could absolutely be the replacement in the long-term for larger tasks.

Thank you very much again and have a wonderful weekend