Indirect for autofill

[GedSalter]

Hi Guys

i have the following formula which I need to autofill down to 3800 rows

A1 ON PORTAL SHEET has a code which referes to a tab sheet name
A2 refer to the cell from that particular sheet I wish to retrieve the data.

the cell the first formula is in is X1, there the formula will autofill down.


=INDIRECT("'" & A$1&"'!"&"A2")

the following rows should read
=INDIRECT("'" & A$1&"'!"&"A3")
=INDIRECT("'" & A$1&"'!"&"A4")
=INDIRECT("'" & A$1&"'!"&"A5") etc..

I have looked at many solutions but none work. Something that I thought was so simple is not working.

Thank you in advance to any solution.


regards

Ged

 

[6StringJazzer]

What cells are these INDIRECT formulas in? That is, what cell has the formula that refers to A2?

As an example, if the formula is in row 2, then you can do this, and you can copy it down.

=INDIRECT("'" & A$1&"'!"&ADDRESS(ROW(),1))

 

[GedSalter]

What cells are these INDIRECT formulas in? That is, what cell has the formula that refers to A2?

As an example, if the formula is in row 2, then you can do this, and you can copy it down.

=INDIRECT("'" & A$1&"'!"&ADDRESS(ROW(),1))

the start cell is X2

 

[GedSalter]

What cells are these INDIRECT formulas in? That is, what cell has the formula that refers to A2?

As an example, if the formula is in row 2, then you can do this, and you can copy it down.

=INDIRECT("'" & A$1&"'!"&ADDRESS(ROW(),1))

cell X2

 

[GedSalter]

What cells are these INDIRECT formulas in? That is, what cell has the formula that refers to A2?

As an example, if the formula is in row 2, then you can do this, and you can copy it down.

=INDIRECT("'" & A$1&"'!"&ADDRESS(ROW(),1))

my excel version is Office 2017

 

[GedSalter]

What cells are these INDIRECT formulas in? That is, what cell has the formula that refers to A2?

As an example, if the formula is in row 2, then you can do this, and you can copy it down.

=INDIRECT("'" & A$1&"'!"&ADDRESS(ROW(),1))

wow. that worked a treat. Now I understand how that works.

 

[Peter_SSs]

That formula may well suit you fine. To me though it isn't as robust as it might be. For example, if you later decide to insert one or more new rows at the top of the sheet that the formula is in, the results will not longer point at rows 2, 3, 4 etc of the referenced sheet.

Here is my example of the 'other' sheet.

GedSalter.xlsm
	A
1
2	zzz
3	yyy
4	www
5
abc

Here is my 'PORTAL' sheet with the previously suggested formula in column Y and an alternative suggestion in column X.
Both formulas return the same results.

GedSalter.xlsm
	A																							X	Y
1	abc
2																								zzz	zzz
3																								yyy	yyy
4																								www	www
PORTAL

Cell Formulas
Range	Formula
X2:X4	X2	=INDIRECT("'" & A$1&"'!A"&ROWS(X$2:X2)+1)
Y2:Y4	Y2	=INDIRECT("'" & A$1&"'!"&ADDRESS(ROW(),1))

If a new row is inserted at the top of this sheet though, the results vary with one automatically adjusting and still points at the original cells and one not so.
Just something to consider with your own possible circumstances.

GedSalter.xlsm
	A																							X	Y
1
2	abc
3																								zzz	yyy
4																								yyy	www
5																								www	0
PORTAL

Cell Formulas
Range	Formula
X3:X5	X3	=INDIRECT("'" & A$2&"'!A"&ROWS(X$3:X3)+1)
Y3:Y5	Y3	=INDIRECT("'" & A$2&"'!"&ADDRESS(ROW(),1))

 

[GedSalter]

That formula may well suit you fine. To me though it isn't as robust as it might be. For example, if you later decide to insert one or more new rows at the top of the sheet that the formula is in, the results will not longer point at rows 2, 3, 4 etc of the referenced sheet.

Here is my example of the 'other' sheet.

GedSalter.xlsm
	A
1
2	zzz
3	yyy
4	www
5
abc

Here is my 'PORTAL' sheet with the previously suggested formula in column Y and an alternative suggestion in column X.
Both formulas return the same results.

GedSalter.xlsm
	A																							X	Y
1	abc
2																								zzz	zzz
3																								yyy	yyy
4																								www	www
PORTAL

Cell Formulas
Range	Formula
X2:X4	X2	=INDIRECT("'" & A$1&"'!A"&ROWS(X$2:X2)+1)
Y2:Y4	Y2	=INDIRECT("'" & A$1&"'!"&ADDRESS(ROW(),1))

If a new row is inserted at the top of this sheet though, the results vary with one automatically adjusting and still points at the original cells and one not so.
Just something to consider with your own possible circumstances.

GedSalter.xlsm
	A																							X	Y
1
2	abc
3																								zzz	yyy
4																								yyy	www
5																								www	0
PORTAL

Cell Formulas
Range	Formula
X3:X5	X3	=INDIRECT("'" & A$2&"'!A"&ROWS(X$3:X3)+1)
Y3:Y5	Y3	=INDIRECT("'" & A$2&"'!"&ADDRESS(ROW(),1))

[

Cell Formulas
Range	Formula

 

[GedSalter]

Hi Peter this looks good But I wish to start the column from say AQ ie AQ41 then fill down with the reference to every tenth column value ie BA41,BK41,BU41 etc. is that possible?

 

[Peter_SSs]

I wish to start the column from say AQ ie AQ41

Is that the formula cell or the cell on the other sheet?

.. reference to every tenth column value ie BA41,BK41,BU41

That does not seem to match the original description