MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Writing a formula -- Please help.


Posted by Kevin on December 14, 2000 10:16 AM

I'm trying to write a formula and some help. Here is what I'm trying to do. I have a formula write in a cell that returns a number between 1 & 48. In the cell below it I need to write a formula that if the number is 1 then I want to display the information from cell b1, or if the number is 6 I want to display the information from cell b6, and so on. Any suggestions?


Posted by Tim Francis-Wright on December 14, 2000 10:24 AM

If the first cell is in A1,
then have A2 be :-
=INDIRECT("B" & A1)

Good luck!

Posted by xxx on December 14, 2000 10:30 AM

=OFFSET(B1,A1-1,0)

B1 = reference, you data is is B1:B48
A1-1 = A1 is the number you put in between 1-48
Offset gives you the value of the cell x rows and y columns away from your refence
=offset(reference,# rows, # columns)