+ Reply to Thread
Results 1 to 8 of 8

Changing cell number with in formula with another cell

  1. #1
    Registered User
    Join Date
    09-12-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    67

    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. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Changing cell number with in formula with another cell

    Hi,

    Perhaps you mean:

    =INDEX(B:B,A1)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    09-12-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    67

    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. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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. #5
    Registered User
    Join Date
    09-12-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    67

    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. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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. #7
    Registered User
    Join Date
    09-12-2013
    Location
    Kent, England
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Changing cell number with in formula with another cell

    Sorry for the slow reply.
    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. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 01-15-2016, 06:16 PM
  2. Replies: 6
    Last Post: 09-12-2012, 05:16 PM
  3. Replies: 5
    Last Post: 08-10-2012, 04:32 PM
  4. Replies: 5
    Last Post: 06-20-2012, 08:39 AM
  5. changing a cell to number format using a formula
    By pmetzak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2008, 02:56 PM

Bookmarks

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