Mar 31 2019 10:16 PM
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 thoughts?
Mar 31 2019 10:36 PM
Apr 01 2019 10:46 AM
@Twifoo, What if I did not know where the table of lookup values would be? Rather, I know it'll be on the second row, but perhaps 20 columns to the right. Is there a way to modify the formula to find some starter cell value (assume it's unique), which would mark the upper left corner of the range? So as shown in the revised attached, if I had this setup, and needed one formula, that based on the entry to the right of it, would find the appropriate range, and do the same operation you've expertly instructed in the last post...how could the formula be modified?
Apr 02 2019 08:21 PM
@Twifoo, any further thoughts that could help me get to this? Basic logic I need is:
1) start at A1, and go right until you find what's in Range Selection
2) from that cell, go down until you find the Row Item
3) in that row, give me the column represented by "Which Answer" selection
I think it's a combination of your index/match functions, set to be one within another, but I don't know how to order them. Hope you have an idea. This would expand the potential of the model into which this would go, exponentially.
Thanks.
Apr 02 2019 09:03 PM
Apr 03 2019 12:13 PM
@Twifoo, it seems to be working, but a few questions (for my education, and for understanding what I can and can't do with this in terms of expansion):
1) if I were to add 20 similar ranges, of varying lengths, to the right of these two, would this work? If not, what can be done to allow it to?
2) is the listing in the last match (where you list the Range 1, Range 2, etc.) possibly to not have hard-coded, but rather just work in a way where it is simply looking across row 1 for a match to the words found in H16, and because it finds it, that's how it knows it's found the right column in which to start?
3) just for my understanding, if you could help me understand what the different lines of the formula do, would be useful. Right now when I click into it (and the coloring of cells shows where it is reading), I'm having a tough time understanding why it's highlighting the first row/column of the first area, but only the results of the second area. And again, this may tie somewhat into my concern about if/how it's possible to make this work with many more ranges, placed out to the right of these.
Thanks for the continued support. Glad that at least one version works. This was an extract of the many ranges I have, so hopefully it is dynamic enough to handle those. Will await your response.
Apr 03 2019 12:36 PM
Apr 04 2019 03:54 PM
@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.
Apr 04 2019 09:38 PM
Apr 05 2019 06:24 AM
@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.