Using numbers in column to reference tabs?

[hvpi91]

I want to be able to drag an equation down a column that will reference each of the different tabs that have been numbered same as the numbers in the column referenced. This is an Hlookup equation and I don't want to have to manually reference each tab (there are a lot of them).

So essentially how do I change the hard coded '1' in the tab reference to a cell reference that can be dragged?

Thanks

 

[AlphaFrog]

Put this in a cell and drag it down and watch what happens.
=ROW()

You'll probably have to use that with another function like INDIRECT or OFFSET.

If you give an example of your formula, I could be more specific.

 

[T. Valko]

I want to be able to drag an equation down a column that will reference each of the different tabs that have been numbered same as the numbers in the column referenced. This is an Hlookup equation and I don't want to have to manually reference each tab (there are a lot of them).

So essentially how do I change the hard coded '1' in the tab reference to a cell reference that can be dragged?

Thanks

Are you trying to say the sheet names are numbers like 1, 2, 3, 4 etc?

Show us what your current formula looks like.

 

[hvpi91]

=IF(HLOOKUP(D$1,'1'!$A$1:$AL$3,3)=" ","-",HLOOKUP(D$1,'1'!$A$1:$AL$3,3))

so I want the tab reference '1' replace with a cell reference A1 so I can drag the equation down without having to hard code the number

Thanks

 

[JoeMo]

Try

=IF(HLOOKUP(D$1,INDIRECT("'"&ROW(A1)&"'!$A$1:$AL$3"),3)=" ","-",HLOOKUP(D$1,INDIRECT("'"&ROW(A1)&"'!$A$1:$AL$3"),3))

 

[T. Valko]

=IF(HLOOKUP(D$1,'1'!$A$1:$AL$3,3)=" ","-",HLOOKUP(D$1,'1'!$A$1:$AL$3,3))

so I want the tab reference '1' replace with a cell reference A1 so I can drag the equation down without having to hard code the number

Thanks

Try it like this...

=IF(HLOOKUP(D$1,INDIRECT("'"&A1&"'!A1:AL3"),3)=" ","-",HLOOKUP(D$1,INDIRECT("'"&A1&"'!A1:AL3"),3))