Dragging down formulas to change sheet number

[martin335746]

I have a sheet with a column holding registration numbers. The value of each cell is taken from a mass of information copy/pasted into another sheet (e.g. Sheet1) via the INDEX function (as the desired cell is not always in the same place).

I wish to drag this formula down so that information can be obtained from Sheet2, Sheet3, and so on.

=INDEX(Sheet1!$A$1:$A$75,MATCH("Registration No. of vehicle or substitute ",Sheet1!$A$1:$A$75,0)+1,1)

I have tried, without success, to replace 'Sheet1' with an INDIRECT function that takes a value from another sheet holding a list from Sheet1 to Sheet50 in order to drag it down.

 

[etaf]

what row is the formula in
you could use something like
=INDIRECT("Sheet"&ROW()&"!$A$1:$A$75")
and as you copy down it will change the sheet number

so if the formula is in row 2 and you want to reference sheet1
=INDIRECT("Sheet"&ROW()-1&"!$A$1:$A$75")
then copy down

can you post what used
I have tried, without success, to replace 'Sheet1' with an INDIRECT function

and how another sheet with reference

 

[martin335746]

what row is the formula in
you could use something like
=INDIRECT("Sheet"&ROW()&"!$A$1:$A$75")
and as you copy down it will change the sheet number

so if the formula is in row 2 and you want to reference sheet1
=INDIRECT("Sheet"&ROW()-1&"!$A$1:$A$75")
then copy down

can you post what used
I have tried, without success, to replace 'Sheet1' with an INDIRECT function

and how another sheet with reference

I am starting in row 1463, although for simplicity we can assume row 2.

I'm not certain what I used but one of the formulas did look like what you provided. Indeed it does work on its own; however, I am clueless as to how the '=INDIRECT' can be put into my existing formula to replace my 'Sheet1'.

=INDEX(INDIRECT("Sheet"&ROW()-1&"!$A$1:$A$75")!$A$1:$A$75,MATCH("Registration No. of vehicle or substitute ",(INDIRECT("Sheet"&ROW()-1&"!$A$1:$A$75")!$A$1:$A$75,0)+1,1) is what I want to achieve (at least, I think it is).

I am pretty much a beginner in Excel and trying to glue bits of formula together and learn as I go, but I just can't figure this one out.

 

[Georgiboy]

The formula ROW() assumes the current row the formula resides in. If you are starting in row 1463 and you want to refer to sheet 1 then instead of ROW()-1 you would need ROW()-1462.
This is because 1463 - 1462 = 1
If you start in row 2 then 2 - 1 = 1 so ROW()-1 would be fine

If the formula is in row 1463 then:

=INDEX(INDIRECT("Sheet"&ROW()-1462&"!$A$1:$A$75"),MATCH("Registration No. of vehicle or substitute ",INDIRECT("Sheet"&ROW()-1462&"!$A$1:$A$75"),0))

If row 2 then change the 1462 to 1

 

[martin335746]

The formula ROW() assumes the current row the formula resides in. If you are starting in row 1463 and you want to refer to sheet 1 then instead of ROW()-1 you would need ROW()-1462.
This is because 1463 - 1462 = 1
If you start in row 2 then 2 - 1 = 1 so ROW()-1 would be fine

If the formula is in row 1463 then:

=INDEX(INDIRECT("Sheet"&ROW()-1462&"!$A$1:$A$75"),MATCH("Registration No. of vehicle or substitute ",INDIRECT("Sheet"&ROW()-1462&"!$A$1:$A$75"),0))

If row 2 then change the 1462 to 1

Thank you both very much, the formula you provided in full here works perfectly. I was leaving the section '!$A$1:$A$75' on after the INDIRECT function which kept it from functioning.

Now, time for me to have a whack at the next hurdle.

 

[etaf]

you are welcome