+ Reply to Thread
Results 1 to 5 of 5

Question about...Cell Referencing formula

  1. #1
    Registered User
    Join Date
    11-25-2017
    Location
    US, Illinois
    MS-Off Ver
    2016
    Posts
    3

    Question Question about...Cell Referencing formula

    I'm trying to use a formula to grab the row of a value. I've figure out how to do this, but I'm unable to use this value to reference a cell. For example I know the value I want is in cell "=A4", but when I try to use my formula of =$A$FORMULA I get an error message. of an invalid formula. My formula works and returns 4, but I'm struggling to figure out how to make the 4 my formula returns function the same as the formula "=A4".

    My Formula is "=CELL("row",INDEX(H2:H11,MATCH(MAX(H2:H11),H2:H11,0)))" which returns 4. I want to be able to do something like "=$A$CELL("row",INDEX(H2:H11,MATCH(MAX(H2:H11),H2:H11,0)))" and have it function the same as "=A4". I think it's a matter of conversion with the result of my formula, but I'm not having much luck with getting it to perform the way I'm expecting it to. I could write a long complicated IFELSE, but I think there should be a shorter solution to this.

    Basically I'm wondering how to get something such as "=A(4)" to function the same as "=A4".

    I haven't had much luck with google so far, but I may just not be searching the write terms. Any help understanding how this works would be appreciated!

    P.S. if Needed/Preferred I can provide screenshots of the excel formula I am using.

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Question about...Cell Referencing formula

    try
    =ADDRESS(MATCH(MAX(H2:H11),H2:H11,0)+ROW(H2)-1,1,4)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    11-25-2017
    Location
    US, Illinois
    MS-Off Ver
    2016
    Posts
    3

    Re: Question about...Cell Referencing formula

    That seems to get me the address of "A4" to display, but not the value in cell A4...I wonder if may I can then convert this from a string/whatever variable type it is returning as and then use that to create a cell reference? Thanks for the help! I'm going to play around with the ADDRESS formula and see if I can't get a little closer!

  4. #4
    Registered User
    Join Date
    11-25-2017
    Location
    US, Illinois
    MS-Off Ver
    2016
    Posts
    3

    Re: Question about...Cell Referencing formula

    Ah ha! That was it! I searched for how to use a string to reference a cell and came upon the INDIRECT formula. This let me throw a string into the formula and use that to reference the cell! Using "=INDIRECT(ADDRESS(MATCH(MAX(H2:H11),H2:H11,0)+ROW(H2)-1,1,4))" got me the value of the cell then! Thanks for the help!

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Question about...Cell Referencing formula

    I think Index & Match function will solve your problem, please attach a sample excel file with desired result to get easy and simple formula.

+ 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. Cell referencing question?
    By kamel_007 in forum Excel General
    Replies: 3
    Last Post: 04-05-2017, 12:29 PM
  2. Cell referencing formula question
    By mickbowler in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-12-2014, 09:19 AM
  3. Cell Referencing question
    By Ishdent in forum Excel General
    Replies: 6
    Last Post: 11-03-2011, 05:50 PM
  4. Cell Referencing Question
    By Impartial Derivative in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-29-2011, 12:58 PM
  5. INDIRECT cell referencing question.
    By call_me_bob in forum Excel General
    Replies: 7
    Last Post: 04-03-2007, 09:21 PM
  6. Odd question re: cell referencing
    By bindak in forum Excel General
    Replies: 1
    Last Post: 10-17-2006, 03:59 PM
  7. [SOLVED] Another question re cell referencing.
    By rmellison in forum Excel General
    Replies: 0
    Last Post: 09-23-2005, 08:05 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