0

I have a worksheet that contains two separate charts:

Name Year 1 Count Year 1 Dollars Year 2 Count Year 2 Dollars ...
Bob ... ... ... ... ...
Anna ... ... ... ... ...
etc. ... ... ... ... ...
Name Year 1 Subcount 1 Year 1 Subcount 2 Year 1 Subcount 3 Year 1 Dollars Year 2 Subcount 1 Year 2 Subcount 2 Year 2 Subcount 3 Year 2 Dollars ...
Bob ... ... ... ... ... ... ... ... ...
Anna ... ... ... ... ... ... ... ... ...
etc. ... ... ... ... ... ... ... ... ...

For the second chart, I'd like to grab the dollar amounts from the first chart instead of recalculating. It's easy enough to do with something along the lines of =INDEX(C$2:C$10,MATCH($A7,$A$2:$A$10,0)) (this would be in Year 1 Dollars for Bob in the second chart, i.e. cell E7). The problem is that more names are likely to be added to the charts over time, which means that hardcoding the ranges in such a manner will eventually cause problems. So instead, I'd like to take advantage of the blank row in between the two charts (i.e. row 5) and use a formula along the lines of =INDEX(C$2:[next blank cell in C],MATCH($A7,$A$2:[next blank cell in A],0)). Normally I could accomplish this with C2:INDEX(C:C,COUNTA(C:C)), but that doesn't work in this case because there is more data below the first chart. I realize that this could easily be solves by putting the second chart to the right of the first instead of below it, or by transposing each chart, but I'm not sure I can convince the end user to accept that. Any ideas?

Claycrusher
  • 187
  • 3
  • 9
  • Have you tried using `=INDEX(C$2:ADDRESS(MATCH(REPT(“z”,50),C:C)+1,3),MATCH($A7:ADDRESS(MATCH(REP("z",50),A:A)+1,1)))` - this is untested but address should help you on your way. If you however aren't going to get rid of the formulas, the range should adjust if you insert rows before the empty row: i.e. `C$2:C5` should change to `C$2:C6` if you have inserted a new row before row 5 to add a new name. (I'm sorry if I'm wrong, it's getting late) – Notus_Panda Dec 10 '22 at 02:21
  • @Notus_Panda can you explain what =MATCH(REPT("z";50);C:C) does? – WeAreOne Dec 10 '22 at 07:14
  • From what I've read, it searches for the 50z's and since match can't find it, it'll return the last non-blank cell, like said, I wasn't able to test it. And it turns out, it's not what we need here since it'll give you the absolute last row. Found a better version however: `ADDRESS(MATCH(TRUE,ISBLANK(C$2:C30),0)+1,3)` this should give you the address of first blank cell found in the C-column but this as well doesn't work the way we're intending to. I tried INDEX like said here (https://stackoverflow.com/questions/33143942/use-calculated-cell-reference-in-formula) but I get a #REF error.. – Notus_Panda Dec 10 '22 at 17:23
  • Actually, wouldn't it just work using `INDEX(C:C,MATCH($A7,A:A,0))` since the MATCH will always go for the first row found and the names with the corresponding dollar amounts are found above anyway? – Notus_Panda Dec 10 '22 at 22:07

1 Answers1

0

you could use tables. To expand, mark the empty row and insert a sheet row, then expand the upper table by dragging or adding data.

enter image description here

Formula you gave I would recommend to change. For example to this one:

=INDEX($C$2:$C$5;MATCH($A8;$A$2:$A$5;0))

Witout table you could do like this:

enter image description here

=INDEX($E$2:$E$5;MATCH($A7;$A$2:$A$5;0);0)

Inserting a row, will update the formulas as well.

WeAreOne
  • 1,310
  • 1
  • 2
  • 13