Forum Discussion

txrussianguy's avatar
txrussianguy
Brass Contributor
Mar 31, 2019

dynamic offset with lookup of location

I need H3 in the attached to take the value selected in H2 and search in column A to find the location of that line...then sum up the number of values in the first X columns (selected in K2).   Any...
excel
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
Apr 03, 2019
The 3 MATCH functions return, the row, column, and area, respectively. Instead of hard-coding the ranges, you can enter them in F2:F21 for the 20 ranges, then the 3rd MATCH will look like this:
MATCH(H16,F2:F21,0)
txrussianguy's avatar
txrussianguy
Brass Contributor
Apr 04, 2019

Twifoo, I think I've not done a good job at explaining the need.  See attached.  K14 has what I think you're saying the formula needs to be.  H16 I've tried to set to be limited to the CustomerList range (though unsuccessfully), which should be a named range that I can change to expand around the list of varying customer/target names.  The order of the sections of assumptions (up top) will not be in any particular order.  I will simply ensure that the upper left corner of any of them, is within the list of customer names (I'll do that with similar validation rules, as H16 (assuming you can help me with that one as well).

 

With that in mind, how is K14 identifying the item from Range selection, and then using that range as the one within which it goes to find the "Which Answer" and "Row Item" combo that I'm selecting?

 

Sorry if I'm just not seeing something obvious.  I feel this is just on the cusp of what I need, but not quite getting there.  Let me know your thoughts.

 

Thanks.

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Apr 05, 2019
    Welcome!
  • txrussianguy's avatar
    txrussianguy
    Brass Contributor
    Apr 05, 2019

    Twifoo, sorry.  I tried putting this idea out originally.  It was not understood.  I tried to break it down into the components.  Obviously didn't quite work, though I think what you gave me comes very close.  I'll try to reframe it in another post.

    Thanks again.

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Apr 04, 2019
    You have repeatedly modified the scenario to a point that I now find everything ambiguous. You must ensure that each conversation you start is limited to only 1 scenario with specific results you want to achieve. Any modification to your originally given scenario must instead be posted to a new conversation.