# Changing cell number with in formula with another cell

1. ## Changing cell number with in formula with another cell

Hi.

Got a problem and not sure if it is possible but very difficult to to and find answer.

What I want is to be able to change the cell number in a formula dependant on the result of another cell ie

a1 = 10
b10 = It works

in cell c1 i want to return what is in cell b(a1) in this case cell b10

Any ideas??

2. ## Re: Changing cell number with in formula with another cell

Hi,

Perhaps you mean:

=INDEX(B:B,A1)

Regards

3. ## Re: Changing cell number with in formula with another cell

Thank you very much yes that does work.
Couple more questions if you don't mind.
What is the relevance of the first B, its coming up as array or referance but don't really understand
Second question might be answered with the first but if I want to make the letter varriable would that be possible so in the same situation
a1=B
b1=It works
c = ??? =Index(b:a1,1)
This I have tried but doesn't work as I said I dont really understand the array/referance which probably will solve it

Neil

4. ## Re: Changing cell number with in formula with another cell

Hi,

If you really want to do it that way, then:

=INDEX(INDIRECT(A1&":"&A1),1)

This will return the value in the first row of the column specified in cell A1.

B:B is a range reference to the entire B column.

Regards

5. ## Re: Changing cell number with in formula with another cell

Thank you really are being very helpful.
Sorry to keep asking questions the reason for all this is trying to link data from two spread sheets. Its all a bit messy and for reasons that baffle me but my management want stuff done a certain way and I'm trying to create a solution.

So I currently can get a cell from another spreadsheet with :
='[CPD Record Team 4.xlsx]NP East 1'!\$V\$3

I now with the tip you gave want to try and make the V varriable and to be what is in cell a3.
Have tried just copying in your last answer but where there are \$ its making things a bit tricky. I could have some hiden cells onone spreadsheet with ='[CPD Record Team 4.xlsx]NP East 1'!\$V\$3 giving values and then use what you have given me to get the relevent cells but would if it is possible do it in one move.

Thank you

6. ## Re: Changing cell number with in formula with another cell

I'm not sure what you mean in your last paragraph, and without seeing a sheet I'm not entirely sure what you're after, but this is the adaptation of my previous formula based on your workbook/sheet name:

=INDEX(INDIRECT("'[CPD Record Team 4.xlsx]NP East 1'!\$"&A3&"\$3"),1)

This will give the value in [CPD Record Team 4.xlsx]NP East 1 located in row 3 and the column specified in cell A3.

Regards

7. ## Re: Changing cell number with in formula with another cell

That worked perfectly thank you.
Do need some more help if you can. What I need to do is incorporate that into a vlookup statement.

=INDEX(INDIRECT("'[CPD Record Team 4.xlsx]NP East 1'!\$"&A3&"\$3"),1)

=VLOOKUP(\$C\$1,'[CPD Record Team 4.xlsx]NP East 1'!\$V\$10:\$V\$99,1,TRUE)

So in the second one I need the V10 and v99 to be "a3"10:"a399"

Hope that makes sence

8. ## Re: Changing cell number with in formula with another cell

Hi,

=VLOOKUP(\$C\$1,INDIRECT("'[CPD Record Team 4.xlsx]NP East 1'!\$"&A3&"\$10:\$"&A3&"\$99"),1,TRUE)

Regards

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1