+ Reply to Thread
Results 1 to 8 of 8

Named formula relative reference

  1. 09-10-2016, 03:31 PM #1
    radji
    radji is offline
    Registered User
    Join Date
    09-10-2016
    Location
    San Diego
    MS-Off Ver
    2007
    Posts
    3

    Named formula relative reference

    Howdy all. Having issue with relative reference in a named formula.

    I have attached the spreadsheet for easier reference. Basically I want to have one named "master" formula on a VALIDATION sheet, then apply that formula via name to to multiple cells over 12 other sheets. The problem I am running into is I need the named formula to pull it's values from the two columns next to the current cell and not the original named formula's cell. I have tried relative reference and using OFFSET, but still get a circular reference. Any thoughts on what I am doing wrong?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-10-2016, 06:15 PM #2
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Named formula relative reference

    Hello,

    first, remove the merged cells. Why merge across rows if you can achieve the save effect by changing the row height. Merged cells can cause a lot of headache and should be avoided.

    Now to the topic. The idea here is that the named range does not point to a cell with a formula, but contains the formula itself.

    I created a named range called myCost and assigned it this formula:

    =VLOOKUP(Admin!$A3,VALIDATION!$A$5:$B$7,2,0)

    With the relative reference for the row number in "$A3" the active cell must be in row 3 when the formula is created. Now you can use =myCost in the Admin sheet table and it will return the value relative to the row.

    2016-09-11_10-13-57.png

    Of course you can use =myCost*B3 to multiply with the number of pages in one fell swoop.

    cheers, teylyn
    Last edited by teylyn; 09-10-2016 at 06:18 PM.
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    Reply With QuoteRegister To Reply
  3. 09-10-2016, 06:43 PM #3
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Named formula relative reference

    Try this in named range:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 09-11-2016, 03:53 AM #4
    radji
    radji is offline
    Registered User
    Join Date
    09-10-2016
    Location
    San Diego
    MS-Off Ver
    2007
    Posts
    3

    Re: Named formula relative reference

    Quote Originally Posted by teylyn View Post
    I created a named range called myCost and assigned it this formula:

    =VLOOKUP(Admin!$A3,VALIDATION!$A$5:$B$7,2,0)

    Of course you can use =myCost*B3 to multiply with the number of pages in one fell swoop.

    cheers, teylyn
    Thank you for this. I definitely wanted to avoid using volatile functions. I think I can apply this to my entire workbook using mycost*relative_cell.

    PS: I can forgo merging the rows in my workbook, but I have columns merged to separate the months. Those I can't do without.
    Last edited by radji; 09-11-2016 at 11:09 PM.
    Reply With QuoteRegister To Reply
  5. 09-11-2016, 04:04 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    Re: Named formula relative reference

    @radji

    Please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 09-11-2016, 06:12 AM #6
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Named formula relative reference

    Quote Originally Posted by radji View Post
    I definitely wanted to avoid using volatile functions. I think I can apply this to my entire workbook using mycost*relative_cell.

    PS: I can forgo merging the rows in my workbook, but I have columns merged to separate the months. Those I can't do without.
    But that VLOOKUP will not work if the "Validation" sheet doesn't contain all the the values that you want. So you have to use the the Validation sheet as a database here.
    Reply With QuoteRegister To Reply
  7. 09-11-2016, 04:53 PM #7
    radji
    radji is offline
    Registered User
    Join Date
    09-10-2016
    Location
    San Diego
    MS-Off Ver
    2007
    Posts
    3

    Re: Named formula relative reference

    Understood. I have played around with it a bit now and can see how VLOOKUP returns it's value. My actual workbook has a straight list of paper type with their costs in a list format. So I will set that list as the database for the name range. Will it matter if there are a few blank entries at the end of the list for the name range? Reason being is I keep a few blank rows for the data validation drop down in case I need to add a paper type and cost in the future. Don't know if it will affect VLOOKUP if those rows are blank.
    Reply With QuoteRegister To Reply
  8. 09-11-2016, 04:56 PM #8
    sanram
    sanram is offline
    Valued Forum Contributor
    Join Date
    06-21-2013
    Location
    Bangladesh
    MS-Off Ver
    Excel 2000, Excel XP, Excel 2002, Excel 2007, Excel 2010, Excel 2013
    Posts
    975

    Re: Named formula relative reference

    No. There is no problem with that.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula with relative reference
    By MigLMunoz in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-14-2013, 12:50 PM
  2. dragging formula across a row with absolute column reference but relative row reference
    By merlyn45 in forum Excel General
    Replies: 5
    Last Post: 09-21-2012, 02:08 PM
  3. [SOLVED] Problem with a relative reference in a formula.
    By roontoon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2012, 10:34 AM
  4. Using variables for relative reference in formula
    By goincrazy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2011, 08:23 AM
  5. Copying sheet with relative formula named range causes errors.
    By leaning in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-19-2011, 08:44 AM
  6. Named formulas to reference relative rows?
    By atreyu55 in forum Excel General
    Replies: 12
    Last Post: 06-17-2009, 06:14 AM
  7. Using Relative Reference in a Formula
    By tedd13 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-10-2006, 11:10 AM
  8. Named SUM Formula with relative refernce(s)
    By Werner Rohrmoser in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2005, 12:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1