+ Reply to Thread
Results 1 to 8 of 8

How do you use a Cell Reference once you do Address?

  1. #1
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    How do you use a Cell Reference once you do Address?

    I have a dynamic table in a column that counts down numbers (think 29, 28, 27, 26, 25... 4, 3, 2, 1, 0, -1, etc). These numbers will constantly change, and I'll constantly want different numbers based on a criteria.

    I've applied CELL and INDEX(MATCH) to get the Cell references I need. In this example I'm grabbing 4 and 29 (would be dynamic in the final formula, but I don't need help there) based on the MATCH.

    =CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0))), which will return: $C$112

    =CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0))), which will return: $C$137

    A final result would function like =SUM($C$112:$C$137). When I try to combine the formulas it won't work, what am I doing wrong? How I'm thinking of the combination currently is below:

    =SUM(CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0))):CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0))))

    Thank you for the help!

  2. #2
    Registered User
    Join Date
    08-28-2015
    Location
    Melbourne, AUS
    MS-Off Ver
    2011
    Posts
    59

    Re: How do you use a Cell Reference once you do Address?

    Is this the one?

    =SUM(INDIRECT(CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0)))&":"&CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0)))))

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: How do you use a Cell Reference once you do Address?

    I don't believe you should need CELL function. This should do what it appears you want.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  4. #4
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    Re: How do you use a Cell Reference once you do Address?

    Thank you all for the help! mrshl9898 your solution worked perfectly for the base request, but I made the example too simplistic for what I really needed. After returning the cell, I then need to OFFSET it by X columns. FlameRetired, that's why I was seeking to apply the CELL function.

    When I try to apply the OFFSET I can get the value, but then I can't get the SUM (I'm actually going to being use RSQ, but for the example SUM is easier to work with) to behave correctly.

    What am I doing wrong (in this example I'm trying to move 6 columns over from the returned cell)?

    =SUM(OFFSET(INDIRECT(CELL("address",INDEX($C$101:$C$166,MATCH(4,$C$101:$C$166,0)))),0,6)&":"&SUM(OFFSET(INDIRECT(CELL("address",INDEX($C$101:$C$166,MATCH(29,$C$101:$C$166,0)))),0,6)

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,862

    Re: How do you use a Cell Reference once you do Address?

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: How do you use a Cell Reference once you do Address?

    I agree with John. This requires a sample workbook.

  7. #7
    Registered User
    Join Date
    06-25-2011
    Location
    Chicago, IL
    MS-Off Ver
    MS 365 apps for business
    Posts
    63

    Re: How do you use a Cell Reference once you do Address?

    Thought we could get away with it, but I guess not this time. See the attached example.

    There are three tabs. The first two are examples of how the output will change based upon a drop-down. In the real file there are +1000 of these cases contained to a single sheet, which is why the array for the RSQ formula needs to be dynamic.

    The third tab is a reference table. The formula will know which periods/rows it needs based on the reference table and the drop-down.

    Finally, once the rows are known, the formula needs to move cells over to perform the RSQ calculation on the dynamic dataset.

    Thanks for the help!
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,074

    Re: How do you use a Cell Reference once you do Address?

    This proposed solution employs some helper cells, which may be hidden for aesthetic purposes.
    The formulas in cells G2:G3 look up the 'Start' and 'End' numbers using Vlookup.
    The formulas in cells H2:H3 find the row numbers using formulas similar to: =SUMPRODUCT((A$1:A$100=G2)*ROW($1:$100))
    The formula in G1 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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] how to set the value of the cell with the reference Target.Address to a specific value?
    By AVM in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-14-2014, 01:53 AM
  2. Convert text address to cell reference address
    By shaod in forum Excel General
    Replies: 1
    Last Post: 12-23-2011, 01:37 AM
  3. Reference checkbox using linked cell address
    By chewie in forum Excel General
    Replies: 3
    Last Post: 06-02-2011, 02:11 PM
  4. Replies: 2
    Last Post: 12-02-2009, 03:23 PM
  5. [SOLVED] How do I get the cell address of a VLOOKUP reference?
    By tfleischny in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-04-2006, 07:20 PM
  6. [SOLVED] Reference cell address of Hyperlink
    By Mike Fogleman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-23-2005, 01:05 AM
  7. [SOLVED] How do I Address A Worksheet From A Cell Reference?
    By GeorgeF in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2005, 08:11 PM

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