+ Reply to Thread
Results 1 to 6 of 6

User Input to change cell reference in formulas

  1. #1
    Registered User
    Join Date
    03-31-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    38

    User Input to change cell reference in formulas

    I have a column on worksheet 'COR TABLE' that is pulling the values from worksheet 'EQUITIES' column CD4:CD1004.

    30 { =('EQUITIES'!CD4) }
    30 { =('EQUITIES'!CD5) }
    -30 { =('EQUITIES'!CD6) }
    40 { =('EQUITIES'!CD7) }
    45 { =('EQUITIES'!CD8) }
    20 { =('EQUITIES'!CD9) }
    so on and so on into the thousands of rows...

    I would like to add a user input onto the COR TABLE worksheet that would tell the formula in what column to get the data in the EQUITIES worksheet, so that I didn't have to copy and paste throughout the entire worksheet to change the reference to one column.

    So if somewhere I simply entered "AR", it would change all formula references to:
    -30 { =('EQUITIES'!AR4) }
    20 { =('EQUITIES'!AR5) }
    -30 { =('EQUITIES'!AR6) }
    45 { =('EQUITIES'!AR7) }
    25 { =('EQUITIES'!AR8) }
    15 { =('EQUITIES'!AR9) }

    Can anyone direct me to an answer on this one???

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: User Input to change cell reference in formulas

    Hi Williampdx, welcome to the forum.

    You can probably use INDIRECT, e.g.

    =INDIRECT("EQUITIES!"&A1&ROW(A4))

    where A1 holds the column letter you want to insert. ROW(A4) is used to return 4. If you hardcoded the "4" it would not update as you filled the formula down.

    Hope that helps!

  3. #3
    Registered User
    Join Date
    03-31-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: User Input to change cell reference in formulas

    It works perfectly if I change it to =INDIRECT("EQUITIES!"&$A$1&ROW(A4)). Now to complicate the matter, how would you suggest I do the same thing - using INDIRECT to get a user input to change the column reference - in a more complicated formula such as this one:

    =IF('USDJPY NY Close'!D5="NT",0,IF('USDJPY NY Close'!DP4>0,'USDJPY NY Close'!DP4-'USDJPY NY Close'!$I$1002,IF('USDJPY NY Close'!DP4<0,'USDJPY NY Close'!DP4-'USDJPY NY Close'!$I$1002)))

    In this formula, I want where it says D5, to change the 'D' to an 'E' or any other user input for the column reference.

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: User Input to change cell reference in formulas

    I don't have data to test on, but maybe:

    =IF(INDIRECT("'USDJPY NY Close'!"&$A$1&ROW(A5))="NT",0,IF('USDJPY NY Close'!DP4>0,'USDJPY NY Close'!DP4-'USDJPY NY Close'!$I$1002,IF('USDJPY NY Close'!DP4<0,'USDJPY NY Close'!DP4-'USDJPY NY Close'!$I$1002)))

  5. #5
    Registered User
    Join Date
    03-31-2010
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: User Input to change cell reference in formulas

    Since I am referencing it multiple times in the formula, I would think this would be the way to do it:

    =IF(INDIRECT('USDJPY NY Close'!&$A$3&ROW(A4))="NT",0,IF(INDIRECT('USDJPY NY Close'!&$A$3&ROW(A4)>0,'USDJPY NY Close'!&$A$3&ROW(A4))-'USDJPY NY Close'!$I$1002,IF(INDIRECT('USDJPY NY Close'!&$A$3&ROW(A4)<0,'USDJPY NY Close'!&$A$3&ROW(A4))-'USDJPY NY Close'!$I$1002)))

    But, I am getting an error on this. Anyone see what is wrong with this?

  6. #6
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: User Input to change cell reference in formulas

    Your previous formula only had D5 occurring once. The rest were referencing DP4.

    Also, you left out all of the quotation marks for the INDIRECT function like I had in my sample formula. As I said, I don't have your workbook to test, but you might try:

    =IF(INDIRECT("'USDJPY NY Close'!"&$A$3&ROW(A4))="NT",0,IF(INDIRECT("'USDJPY NY Close'!"&$A$3&ROW(A4)>0,INDIRECT("'USDJPY NY Close'!"&$A$3&ROW(A4))-('USDJPY NY Close'!$I$1002,IF(INDIRECT("'USDJPY NY Close'!"&$A$3&ROW(A4))<0,INDIRECT("'USDJPY NY Close'!"&$A$3&ROW(A4))-'USDJPY NY Close'!$I$1002)))

+ 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