+ Reply to Thread
Results 1 to 6 of 6

INDIRECT function using Absolute cell reference

  1. #1
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    INDIRECT function using Absolute cell reference

    I am using the formula shown below to refer to the name of a column in the table named "Tbl_Prices". Cell E9 contains the name of the column which is "Volume 1".
    The formula shown below exists in cell I13. My problem is that when I copy the formula to the cell below I13 which would be I14, the formula changes the reference from E9 to E10. I want E9 to be absolute and stay at E9. I tried putting dollar signs around the E and 9 $E$9 but an error occurs. What is the proper syntax to make E9 absolute and not change when I copy the formula? I need to copy this formula down 2000 rows!


    {=INDEX(INDIRECT("Tbl_Prices["&E9&"]"),MATCH($C15&$D15,Tbl_Prices[Category]&Tbl_Prices[Finish size],0))}

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,905

    Re: INDIRECT function using Absolute cell reference

    Hi absolute values be it row or columns are indicated with a $ sign
    The $ and a letter will hold the column, the $ and a number the roe and if both have a dollar sign the cell will stay

    You can select E9 in the formula and press F4 it toggles all the combinations.

    Hope I've been able to explain it, happy coding
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: INDIRECT function using Absolute cell reference

    It would help to see an example of your workbook. I'm not sure why you are using INDIRECT if you also want to always point to E9. What is in E9?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,903

    Re: INDIRECT function using Absolute cell reference

    It would be:

    =INDEX(INDIRECT("Tbl_Prices["&$E$9&"]"),MATCH($C15&$D15,Tbl_Prices[Category]&Tbl_Prices[Finish size],0))

    and remember to Ctrl+Shift+Enter when re-entering the formula.
    Rory

  5. #5
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: INDIRECT function using Absolute cell reference

    What a do-do. I forgot to Ctrl+Shift+Enter after I put the $ signs around $E$9

    How do I mark this as solved?

  6. #6
    Registered User
    Join Date
    07-09-2019
    Location
    United State
    MS-Off Ver
    2019
    Posts
    53

    Re: INDIRECT function using Absolute cell reference

    THANK YOU everyone!

+ 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] Replace an absolute cell reference in an If function when met.
    By markDuffy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-26-2018, 09:19 AM
  2. Replies: 12
    Last Post: 03-23-2017, 01:24 PM
  3. Indirect Sheet reference with cell reference left function and text
    By teststrip in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2015, 10:31 AM
  4. [SOLVED] Replace absolute cell reference with Indirect cell reference in formula
    By Roothy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2013, 04:46 AM
  5. [SOLVED] Indirect Function (Cell Reference) Help
    By Bioc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-09-2012, 05:30 PM
  6. Replies: 8
    Last Post: 07-13-2012, 06:48 AM
  7. Replies: 11
    Last Post: 04-22-2012, 09:14 AM

Tags for this Thread

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