Excel 97 Relative Referencing

  emperor 13:51 27 Feb 2003
Locked

I want to be able to address an arbitrary cell C, the identity of which will depend on the result of a different cell, V.
In a formula a cell reference can be used and the value in that cell is then used in the calculation. My question is whether or not the cell reference for cell V can be used in cell C's reference, so that the value in cell V is used to define the target cell.
Example: for value in cell V = 3
target cell C is in column E
so target cell is E3
Can I use a cell reference for C that incorporates column E and cell reference for V?
If that is not explained very well, I apologise and will try to do better next time.
I want to avoid lookup tables if possible so that the method is as simple and widely applicable as possible

  Ste R 62 16:45 27 Feb 2003

Think i know what you mean try using the formula 'INDIRECT'.

If you have a set of values in cells A1:A3 and using the value in cell A4 as the reference point and the result to output in cell A5

in A5 you would input =INDIRECT("A"&A4)

if A4 = 2 this would return the value inputted in cell A2.


Hope this makes sense, probably not but try experimenting with INDIRECT and using the help in excel, its a very useful formula for refenceing.

  emperor 20:16 27 Feb 2003

Thanks for the interest.

I shall look at this tomorrow. I confess I am not familiar with INDIRECT.

I find that I have a problem with the help utility in that it is most useful if you know what they would call the thing you are looking for.

  VoG™ 21:28 27 Feb 2003

Have a look at this excellent download for explanations of INDIRECT and a lot more besides click here

  emperor 12:53 28 Feb 2003

VoG™, Thanks for the link. It looks very useful and not just for this question.

I am still looking at INDIRECT so I will not tick this as resolved yet. I am really just flagging up that I have seen the responses and am grateful for them.

  emperor 13:31 28 Feb 2003

Ste R 62

It does what I want. Thanks very much.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?