Trying to use a cell value to reference another cell
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Trying to use a cell value to reference another cell

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    For example, if i have the value 5 in B1 and i want to use that value to reference a cell in column C, ie the formula for A1 is something like:

    =C(B1)

    obviously that doesn't work, but i hope you get the just of it.
    Thanks!

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    You need the INDIRECT function

    =INDIRECT("C"&B1)




  3. #3
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Go to bed, Dave.


    ~Anne Troy

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Dave!

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My pleasure kwaring


    Tuggy, I can't sleep with both arms in a cast )

  6. #6
    New Member
    Join Date
    Aug 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use a cell value to reference another cell

    Quote Originally Posted by Dave Hawley View Post
    Hi

    You need the INDIRECT function

    =INDIRECT("C"&B1)
    I am trying to put together a sheet that uses a lot of the INDIRECT function. Using a similar example to your above one =INDIRECT("C"&$B$1) is there a way to make the "C" changeable when dragging the function to apply to other cells? I want to apply the same formula to other columns, but it needs to reference the appropriate column rather than always "C". Same goes for rows.

    I apologize for any poor terminology. I am fairly new to what to call things in Excel.

  7. #7
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,551
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use a cell value to reference another cell

    Best to avoid INDIRECT() if possible, an alternative for the example given (that alters as the formula is coped across) would be:

    =INDEX(C:C,$B$1)

    You may need to give the details if this does not suffice.
    [code]your code[/code]

  8. #8
    New Member
    Join Date
    Aug 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use a cell value to reference another cell

    The portion of the formula I am having trouble with is:

    =SUMSQ(COLUMNS(INDIRECT("A"&$AW$5):INDIRECT($AV$5&"3"))-1,ROWS(INDIRECT("A"&$AW$5):INDIRECT($AV$5&"3"))-1)

    It works perfectly in one cell, but I would like to copy it to other cells easily which I can't do because of the portions that are in quotations. Is there a way to make that happen?

  9. #9
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,551
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use a cell value to reference another cell

    A brief description of what you have in the cells in question, and the expected result as you drag the formula to other cells would greatly increase your chances of getting a solid solution.
    [code]your code[/code]

  10. #10
    New Member
    Join Date
    Aug 2015
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to use a cell value to reference another cell

      
    Quote Originally Posted by FormR View Post
    A brief description of what you have in the cells in question, and the expected result as you drag the formula to other cells would greatly increase your chances of getting a solid solution.

    =SQRT(SUMSQ(COLUMNS(INDIRECT("A"&$AW$5):INDIRECT($AV$5&"3"))-1,ROWS(INDIRECT("A"&$AW$5):INDIRECT($AV$5&"3"))-1))

    AV5, AW5, AV6, and AW6 contain U, 3, U, and 27 respectively. The idea is that these cells can place a location from which the rest of the cells calculate their information. The location needs to be mobile.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com