0

EDIT: Thanks to Erik's help It works flawlessly. I made a new approach to this and works as well. I'm going to link the sheet so others can learn and use it for their own use. https://docs.google.com/spreadsheets/d/1snPK6wBmR0Xbz4PFLO8sG_hBSHOpND-e_UGxL033VtE/edit?usp=sharing

What I'm having trouble is grabbing all values in certain column and row all the way down to end of a certain column and row. I set up a drop-down list that I'm working with to incorporate with my tables.

Below is how sheet 1 looks like. Drop-down list is setup from range using data validation from sheet2

Drop-Down List1 Drop-Down List2
name1 name1
name2 name2
name3 name3
name4 name4
Gold Silver
This will show the sum of drop-down list 1 and drop-down list 2 This will show the sum of drop-down list 1 and drop-down list 2

I have two drop-down list that has value assigned in it which is referred to an array on another sheet.

Below is sheet 2 that has my data stored for sheet 1

Name Silver Gold Iron
name1 13 11 90
name2 11 33 111
name3 4 21 43
name4 55 6 299

This is very similar to what I have set up. So to explain what I want. What I want is to grab ALL data in each cell in a column between and with drop-down list 1 and drop-down list 2 value. For example under drop-down list 1, I select name2 and for drop-down list 2 I select name4. On sheet 1, Under GOLD header I want the sum of gold that get all data from name2 through name4, Which comes out to 60. 33 + 21 + 6. Should look something similar to this

Example 1: The top of this is drop down list value

name2 name4
Gold Silver Iron
60 70 453

Example 2: The top of this is drop down list value

name1 name3
Gold Silver Iron
65 28 244

What would be the best way to achieve this?

2
  • Please share a link to the spreadsheet (or to a copy of it). For all of your careful explanations, it will still be nearly impossible to answer this without seeing the actual spreadsheet, layout, ranges, data types, etc.
    – Erik Tyler
    Commented Mar 10, 2022 at 4:48
  • I made a temporary sheet to use. Its editable so you can edit whatever you want or need. docs.google.com/spreadsheets/d/… Commented Mar 10, 2022 at 5:03

1 Answer 1

1

I've added a new sheet ("Erik Help") with the following formula:

=ArrayFormula(IF(VLOOKUP(B3,{Sheet2!A:A,ROW(Sheet2!A:A)},2,FALSE)>VLOOKUP(C3,{Sheet2!A:A,ROW(Sheet2!A:A)},2,FALSE),"Drop-Down Values Not Possible",IFERROR(HLOOKUP(B7:D7,{FILTER(Sheet2!B:D,ISTEXT(Sheet2!B:B));MMULT(SEQUENCE(1,COLUMNS(Sheet2!B:D),1,0),1*FILTER(Sheet2!B:D,ROW(Sheet2!A:A)>=VLOOKUP(B3,{Sheet2!A:A,ROW(Sheet2!A:A)},2,FALSE),ROW(Sheet2!A:A)<=VLOOKUP(C3,{Sheet2!A:A,ROW(Sheet2!A:A)},2,FALSE)))},2,FALSE))))

This one formula produces all results and includes at least a fair amount of error handling.

As you can see, it's complex — more complex than I can usually offer via this free, volunteer-run forum. I've shared a solution here, however, with the understanding that I'm sharing it as-is and cannot also explain it. I leave that to any interested parties to analyze it, take it apart, see what each piece does separately and together, and learn that way.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.