+ Reply to Thread
Results 1 to 5 of 5

Variable Column Reference from number

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Holland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Variable Column Reference from number

    Hello Excelworld,
    I have the following problem:
    There is a calculation that will be executed x times.
    x will be determined in advance. The column in which the outcome will be posted depends on x.
    I need a function that enables me to display the outcome in a fixed cell. For example if x is 2 the outcomes can be found in B3 but when x is 4 it will be in D3.
    I know this is possible for rows but i did not succeed in adjusting the INDIRECT function in a way to do the same for columns based on a number.
    Anybody knows a solution for that?


    Thanks in advance

    Jannis

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Variable Column Reference from number

    Can you elaborate? What is the formula you are using? Are you mean when x is 2 you want to reference B3 in the formula, and when x is 4 you want to reference D3? If so you can use INDEX instead of INDIRECT.

    e.g.

    =5+INDEX(A3:Z3,X1)

    this will add 5 to whatever is found in row A3:Z3 at the cell number indicated by X1... so if X1 is 2, then the formula reduces to: =5+B3
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    09-13-2012
    Location
    Holland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Variable Column Reference from number

    I'll try to explain. I am working on a thermodynamic model. Depending on the volume of the water the calculation has to be executed x times. x will be a different number every loop. so every time the final result will be in a different column. For example
    AB3
    Z3
    BB3
    etc.
    This has developed from the structure the excel sheet is made with. A different structure would make the sheet overly complex. To pass on the final result I want a fixed cell to display the final value.
    For example A1
    So if x is 26 A1 has to take the value of Z3.
    If x is 29 A1 has to take the value of AC3






    Quote Originally Posted by NBVC View Post
    Can you elaborate? What is the formula you are using? Are you mean when x is 2 you want to reference B3 in the formula, and when x is 4 you want to reference D3? If so you can use INDEX instead of INDIRECT.

    e.g.

    =5+INDEX(A3:Z3,X1)

    this will add 5 to whatever is found in row A3:Z3 at the cell number indicated by X1... so if X1 is 2, then the formula reduces to: =5+B3

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Variable Column Reference from number

    Then try:

    =INDEX(A3:Z3,X1)

    where X1 contains the X value... and A3:Z3 is the whole range to look at... you can also use whole row like:

    INDEX(3:3,X1)

  5. #5
    Registered User
    Join Date
    09-13-2012
    Location
    Holland
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Variable Column Reference from number

    That did it.
    Now all I need is to get my Macros to work.
    But for that I'll open a thread over in the other area.
    Muchas Gracias

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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