Referencing Cells when Filtering

[FluffyCat]

Good day all,

I am new to excel, please can you assist me with the following:

I have a spreadsheet where I want to reference the value in E9, I want B16 to reflect the value in E9. When I filter on the Budget Date (B8) and change this to a different month I want the new value in E9 to reflect in cell B16.

Please see attached,

Thank you!

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[FluffyCat]

Thank you Excel Ninja! Wow - need to try wrap my head around:

=MIN(IF($G$9:$G$12 = 1, ROW($G$9:$G$12) - MIN(ROW($G$9:$G$12)) + 1))

Too be honest I thought there was a easier way of doing this but it did the trick.

Thanks!

 

[p45cal]

You can try this by array-entering it (commit to the sheet using Ctrl+Shift+Enter, not just Enter) into a single cell:
=INDEX($E$8:$E$11,SMALL(IF(SUBTOTAL(3,OFFSET($E$8:$E$11,ROW($E$8:$E$11)-ROW($E$8),,1)),ROW($E$8:$E$11)-ROW($E$8)),2)+1)

or:
=INDEX($E$8:$E$11,SMALL(IF(SUBTOTAL(103,OFFSET($E$8:$E$11,ROW($E$8:$E$11)-ROW($E$8),,1)),ROW($E$8:$E$11)-ROW($E$8)),2)+1)

(they handle manually hidden rows differently)

 

[rahulshewale1]

hii @FluffyCat ,

Beautiful formula by narayan sir ,

another option using helper column one only .

Cell F9 :=SUBTOTAL(103, B9)

Cell B16 :=INDEX($E$9:$E$12,AGGREGATE(15,3,(ROW($F$9:$F$12)-ROW($F$9)+1)/($F$9:$F$12=1),1))

Regard
Rahul shewale

 

[bosco_yip]

hii @FluffyCat ,

Beautiful formula by narayan sir ,

another option using helper column one only .

Cell F9 :=SUBTOTAL(103, B9)

Cell B16 :=INDEX($E$9:$E$12,AGGREGATE(15,3,(ROW($F$9:$F$12)-ROW($F$9)+1)/($F$9:$F$12=1),1))

Regard
Rahul shewale

If helper F9 copied down : =SUBTOTAL(103,B9)

Then,

this shorter formula will get the same result

In B16 enter :

=INDEX(E$9:E$12,MATCH(1,F$9:F$12,0))

Regards
Bosco