Hi everyone, just after a bit of help with applying my inderict formula to the rest of the cells in my sheet properly.

I am getting the values of cells in other sheets, using INDIRECT to use cell values to name the sheets.

Here is an example of how the cells are now...

A B
1 Sheet 1 =(INDIRECT("'"&A1&"'!$A$5"))
2 Sheet 2 =(INDIRECT("'"&A2&"'!$A$5"))
3 Sheet 1
4 Sheet 2
5 Sheet 1


The formulas in B1 are going to return the value of A5 in "Sheet 1" (or whatever value is in A1) and B2 will return the vale cell A5 in 'Sheet 2. The next line will be the value of A6 in Sheet 1, and the next will be A6 in Sheet 2 and so on.

So I have quite a few rows to get through I want to be able to autofill so it looks like this...

A B
1 Sheet 1 =(INDIRECT("'"&A1&"'!$A$5"))
2 Sheet 2 =(INDIRECT("'"&A2&"'!$A$5"))
3 Sheet 1 =(INDIRECT("'"&A3&"'!$A$6"))
4 Sheet 2 =(INDIRECT("'"&A4&"'!$A$6"))
5 Sheet 1 =(INDIRECT("'"&A5&"'!$A$7"))

As it is now, when I autofill, it autofills the Sheet name part of it. It is looking like this...
A B
1 Sheet 1 =(INDIRECT("'"&A1&"'!$A$5"))
2 Sheet 2 =(INDIRECT("'"&A2&"'!$A$5"))
3 Sheet 1 =(INDIRECT("'"&A3&"'!$A$5"))
4 Sheet 2 =(INDIRECT("'"&A4&"'!$A$5"))
5 Sheet 1 =(INDIRECT("'"&A5&"'!$A$5"))




Excel isn't intuitive enough to know I want to move cells in the referenced sheet.



I've tried to play around with the formula but am having problems.

Can anyone help?