I have a workbook with precisely one hojillion budget tabs. We have a
summary sheet, but it's kind of clunky and hard to maintain, and I'm
trying to do a replacement.

Since we're constantly adding tabs, a great way to make sure we don't
miss any is to use a macro to list all the tabs in the workbook. So
far, so good. However, I'd like to use that list as the basis for the
summary, so it's all basically automatic.

For instance, lets say A1 through A3 has

Alison
Bob
Carl

These are the names of the tabs that the macro pulled.

I'd like B1 through B3 pull a specific cell from that sheet, let's say
cell Z1.

So the simple version of the formula for B1 would be =Alison!Z1

But I'd like a formula I can just copy down that reads more like
=[A1CellValue]!Z1

Is there any simple way to do this?