?
Solved

cell reference based on value in another cell

Posted on 2011-05-11
5
Medium Priority
?
309 Views
Last Modified: 2012-05-11
I think this should be easy, but I can't figure out quite how to do it.

Here is a simple representation

     A     B     C     D     E
1   2                   2
2   4
3   6
4
5
6    x
In the cell A6 I need a function that returns the value in A1, A2 or A3 depending on the value of the relevant row in D1.

eg If D1 = 1 A6 is the value in A1.  If D1 = 3 A6 is the value in A3. etc

What is the easiest way of indicating a row reference based on the value in a given cell?

Best regards

Richard
0
Comment
Question by:rltomalin
5 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 668 total points
ID: 35736056
=INDEX(A1:A5,D1)
should work.
0
 
LVL 6

Assisted Solution

by:theKashyap
theKashyap earned 664 total points
ID: 35736114
Try this formula:
=OFFSET(A1,D1-1,0)
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 668 total points
ID: 35736160
or

=indirect("A"&D1)
0
 

Author Closing Comment

by:rltomalin
ID: 35736591
Thanks for the input.  All three solutions are clear and do the job.  I just chose one of course - shared points equally.

Regards

Richard
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35736614
Which one? ;-)

Thanks for the points
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question