How to use formula from another cell to direct formula to another sheet?

Hi Guys,

I have a table of contents (TOC) sheet at the start of my workbook, and I want the row to auto-populate with data from the sheets referred to in the TOC.

I was previously doing this manually with ='sheetname'!D2 and the various other cell locations it needs to draw data from.

Now that the first column is auto populated by a TOC macro (macro grabs all active sheet names, and creates a hyperlinked list in the first column) , I want to write a macro/formula that will run after the TOC macro, and auto-populate each row with the relevant formula (='sheetname'!A3 for example)

I'm having trouble getting excel to use the data in column A. It instead insists on using "A5" in the formula, and not the DATA that is present in A5.

to better summarise, cell A1 is a macro that grabs the sheet name and hyperlinks. I want cell A2 to be a formula that grabs some info out of a cell on the sheet referred to in cell A1, without having to manually enter the sheet name into cell A2.

any help greatly appreciated!

thanks guys!

Hi and welcome to the forum

I think you need to use INDIRECT() for this, maybe something like...
=INDIRECT("'"&A1&"'!A3")

do do doooo lol naaa its all done with mirrors mate

Happy to help and thanks for the feedback Looking forward to helping on you next question