Use ONLY a certain row number in cell reference

[Chris Needham]

This is a bit difficult to explain...
Is there any way to use ONLY one row number, but different columns, in cell references on a sheet?

For instance, one cell might contain a reference to A5, another to B5, another to C5 etc.

Then on another sheet I want to reference another row, but the same sequence, A6, B6, C6 etc.

What I'm hoping to accomplish is to be able to create a new sheet by copying an existing sheet, then by simply typing in say "5" or "6" or whatever in ONE cell on that new sheet, all referance will change to the row number.

Does that make any sense?

 

[PA HS Teacher]

Try:
=Index(A:A,$C$1)
where B1 contains the desired row number.
If you paste across columns, formula should automatically change to
=Index(B:B,$C$1) etc.

 

[Chris Needham]

Thanks for that.

I can get that to work in the same worksheet, but doesn't seem to work across different worksheets.

If I enter =INDEX('Data Entry'!J:'Data Entry'!J,$B$1) I get the #NAME? error.

Any idea with that?

 

[Aladin Akyurek]

Thanks for that.

I can get that to work in the same worksheet, but doesn't seem to work across different worksheets.

If I enter =INDEX('Data Entry'!J:'Data Entry'!J,$B$1) I get the #NAME? error.

Any idea with that?

On which sheet and where are you entering this formula?

 

[Blade Hunter]

Thanks for that.

I can get that to work in the same worksheet, but doesn't seem to work across different worksheets.

If I enter =INDEX('Data Entry'!J:'Data Entry'!J,$B$1) I get the #NAME? error.

Any idea with that?

Delete the second occurence of 'Data Entry'! in the formula ie:

=INDEX('Data Entry'!J:J,$B$1)

 

[Chris Needham]

Thanks for the reply.

I'm entering the formula on another worksheet named "Week 1" in a blank cell.

 

[Chris Needham]

Delete the second occurence of 'Data Entry'! in the formula ie:

=INDEX('Data Entry'!J:J,$B$1)

That got it! Thanks to you and the others that helped.