SOLVED

Using the number in a cell as the row reference in other.

%3CLINGO-SUB%20id%3D%22lingo-sub-1889542%22%20slang%3D%22en-US%22%3EUsing%20the%20number%20in%20a%20cell%20as%20the%20row%20reference%20in%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889542%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20to%20which%20I%20need%20to%20keep%20adding%20rows.%3C%2FP%3E%3CP%3EAt%20the%20end%20of%20the%20table%2C%20I%20have%20several%20cells%26nbsp%3Btaking%20the%20values%20of%20the%20last%20row%2C%20and%20it%20makes%20me%20change%20the%20row%20number%20in%20then%20all%20every%20time%20I%20add%20a%20row.%3C%2FP%3E%3CP%3EIt%20would%20help%20me%20a%20lot%20if%20I%20could%26nbsp%3B%20could%20do%20this%3A%3C%2FP%3E%3CP%3ELet's%20say%20I%20want%20the%20value%20of%20F3%20in%20E8.%3C%2FP%3E%3CP%3EInstead%20of%20writing%20%3DF3%20in%20E8%2C%20I%20want%20to%20put%20the%20the%20row%20number%203%20in%20D4%20and%20write%20a%20formula%20in%20E8%20that%20takes%20the%20column%20F%20and%20the%20row%20number%203%20I%20wrote%20in%20D4.%3C%2FP%3E%3CP%3EPlease%2C%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1889542%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1889639%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20number%20in%20a%20cell%20as%20the%20row%20reference%20in%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1889639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F869917%22%20target%3D%22_blank%22%3E%40eviera49%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20function%20that%20performs%20the%20task%20you%20describe%20is%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDIRECT(%22F%22%26amp%3BD4)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EAnother%20option%20is%20to%20use%20INDEX%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20INDEX(F%3AF%2C%20D4)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20tend%20to%20use%20defined%20names%20rather%20than%20cell%20references%20e.g.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20INDEX(%20data%2C%20instance%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ebut%20that%20is%20a%20matter%20of%20personal%20preference.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1890511%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20the%20number%20in%20a%20cell%20as%20the%20row%20reference%20in%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1890511%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F869917%22%20target%3D%22_blank%22%3E%40eviera49%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20probably%20it's%20not%20necessary%20to%20enter%20row%20number%2C%20it%20could%20be%20calculatable.%20Especially%20if%20use%20Excel%20Tables%2C%20not%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a table to which I need to keep adding rows.

At the end of the table, I have several cells taking the values of the last row, and it makes me change the row number in then all every time I add a row.

It would help me a lot if I could  could do this:

Let's say I want the value of F3 in E8.

Instead of writing =F3 in E8, I want to put the the row number 3 in D4 and write a formula in E8 that takes the column F and the row number 3 I wrote in D4.

Please, help

 

 

4 Replies
Highlighted
Best Response confirmed by eviera49 (New Contributor)
Solution

@eviera49 

The function that performs the task you describe is

=INDIRECT("F"&D4)

Another option is to use INDEX

= INDEX(F:F, D4)

I tend to use defined names rather than cell references e.g.

= INDEX( data, instance )

but that is a matter of personal preference.

Highlighted

@eviera49 

Most probably it's not necessary to enter row number, it could be calculatable. Especially if use Excel Tables, not ranges.

Highlighted

@Peter Bartholomew 

Thanks! Peter!!

It works perfectly.

You've been so helpful.

 

Highlighted

@Sergei Baklan 

Thanks a lot Sergei.

Perhaps this solution is better for people who knows more than I do.