Help requested: Referencing cells in other sheets using INDIRECT

[vonRobbo]

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?

[mikeTRON]

Why not have a helper column for the cell number and letter as well, so you can just concatenate the sheetName and Cell ref into ONE cell then indirect to THAT ont cell?

[vogel997]

=(INDIRECT("'"&A1&"'!$A$"&ROW(A5)))

Try this:
row(a5) will return the number five and
by dragging down it will increase by 1 automatically

[vonRobbo]

=(INDIRECT("'"&A1&"'!$A$"&ROW(A5)))

Try this:
row(a5) will return the number five and
by dragging down it will increase by 1 automatically

This works as you've described, but I need it to increase by one every 2 rows (see example in original post) not every single row.

[mikeTRON]

This works as you've described, but I need it to increase by one every 2 rows (see example in original post) not every single row.

this is why it is easier to attach a sample workbook and you explain exactly what it is you are looking for so we can solve one problem, not many LIKE your problem because you left pieces of the puzzle out.

[FDibbins]

You will have to play with this to get it started where you need it, but build this into the last part of your formula, it will cause an increase of 2 every 2 rows..

=ROW()-MOD(ROW(),2)