+ Reply to Thread
Results 1 to 8 of 8

Extracting cell data to identify a named range

  1. #1
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Extracting cell data to identify a named range

    I have a header row in a series of 25 tables. These are named Year 1, Year 2, etc. I also have formulas in each column below these headers that refer to a named range such as RentIncrease1, RentIncrease2, etc.

    Is it possible to have the same formula in every cell that can use the header data, such as =C16*(1+RentIncreaseX) where "X" is actually the rightmost 2 characters from the header row? I know I can use Left and Right functions in formulas but can I concatenate "RentIncrease" and the right 2 characters from the header row to come up with RentIncrease1 or RentIncrease2?

    I'm trying to prevent having a dozen rows with custom formulas for each row.

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Extracting cell data to identify a named range

    If I'm understanding your problem correctly, I think you could use INDIRECT, which converts what's in a cell to a reference. For example, if cell A1 contains 'C12' (without inverted commas) and you put =INDIRECT(A1) into B1, B1 will display the contents of cell C12.

    Otherwise, please consider uploading a sample file (with confidential information removed) which shows what you want to achieve - to upload, click 'Go Advanced' under the reply box and use the paperclip icon.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

  3. #3
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Re: Extracting cell data to identify a named range

    Thanks for asking...attached in one of the many tables that I'm trying to make easier to update.

    NamedRange Shortcut Example.xlsx

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,847

    Re: Extracting cell data to identify a named range

    Hi Jim,

    How about trying a VLookup formula? I'm not sure all my formulas are correct but they look like this..

    =D12*(1+VLOOKUP("Rentincrease"&TRIM(RIGHT(E$9,2)),IncreaseTable,2,FALSE))

    see the attached where I've done the top half triangle.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Re: Extracting cell data to identify a named range

    Wow, that's actually pretty good. Although I included the "table" where the values and row titles are shown, the actual values are contained in a single row on another page and the 10 cells are each a named range, there is no column or row header as this VLookup function uses. My fault for not stating that initially. I think the limitation is going to come in that there may be no way to cobble together bits of cell data to create the named range reference for use in a formula.

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Extracting cell data to identify a named range

    Quote Originally Posted by JimDandy View Post
    <snip>can I concatenate "RentIncrease" and the right 2 characters from the header row to come up with RentIncrease1 or RentIncrease2?
    If what you want to do is replace any formula which contains 'RentIncreaseX' with something which puts the X in automatically, try these:

    In column D, instead of =L7*(1+RentIncrease1) try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and in column E onwards, instead of =D21*(1+RentIncrease2) (and similar), try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is that something you can use with your file layout?

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

  7. #7
    Forum Contributor
    Join Date
    11-17-2004
    MS-Off Ver
    Office 2016
    Posts
    527

    Re: Extracting cell data to identify a named range

    Aardigspook, that was the exact solution I needed, thanks!

    Thanks too for the other options.

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Extracting cell data to identify a named range

    You're welcome, glad I could help - and thanks for the rep.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

+ 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. extracting data from a range if a cell value is identical to a range name
    By blue_clouds_mountain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2013, 05:28 PM
  2. Identify which named range(s) ActiveCell intersects
    By yay_excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 12:31 PM
  3. Identify duplicate roes in a named range
    By SSGMiami in forum Excel General
    Replies: 3
    Last Post: 03-24-2009, 08:47 AM
  4. Identify intersection in named range
    By Freddy K in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-06-2009, 10:29 AM
  5. How do you Identify text as a named range in excel
    By DMDave in forum Excel General
    Replies: 7
    Last Post: 05-07-2006, 06:55 PM
  6. [SOLVED] Extracting a column from a named range
    By Andy Chan in forum Excel General
    Replies: 10
    Last Post: 12-24-2005, 02:55 PM
  7. Replies: 0
    Last Post: 12-13-2005, 02:35 AM
  8. Extracting unique entries and assigning it to a named range
    By Hari in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-13-2005, 02:35 AM

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