+ Reply to Thread
Results 1 to 8 of 8

Named formula relative reference

  1. #1
    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

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    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.

  3. #3
    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.

  4. #4
    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.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,258

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    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.

  7. #7
    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.

  8. #8
    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 to 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. 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. [SOLVED] 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1