Thread: Creating a Cell That References a Value That Changes When Data is Filtered?

I have attached a file for further clarification of my problem. Above is a bit of a simplification, and I may be asking the wrong question here. The attached worksheet has a graph based on the data to the left. I set it up to look like a timeline of the dates provided in the data. I have applied a filter to the data so that I can choose to look at different categories ("Work Holidays" or "Birthdays" in this case) and the graph will update based on the data that is currently showing in the filter. The problem I'm running into is with the labels I've applied to the data points in the graph. When the data is filtered to show only the "Birthdays" category, the data points on the graph are correct, but the labels still show the "Work Holidays" value. How can I create this graph so the data labels update dynamically with the rest of the graph? It would also be nice if I could get the Chart Title to update based upon which category is selected in the filter, Seems like this is the same issue and if we can solve the labels issue, we can solve the title issue with the same method.

Timeline.xls

Can anyone help me?

Assuming that Column A will always be sorted in ascending order:

=INDEX(C2:C7,SUBTOTAL(5,A2:A7))

Haseeb A & Whizbang, I'm hoping you can give me a little more context to your replies. Looks like you're both doing something similar, but I'm unclear as to what data your formulas are refering to, is this in reference to the sample data in my first post, or the data I provided in the attachment file in the second post? The data ranges you each use in your formulas are confusing me.

Thank you both.

Retry at attachment...

My formula is in reference to your data as per your original post.

Originally Posted by guyute76

A | B
-----------
1| X | 10
2| X | 20
3| X | 30
4| Y | 40
5| Y | 50
6| Y | 60

Although my formula assumes that row 1 has header information.

Bump with edit to my last post. Still trying to solve this one. Can anyone help me extrapolate Whizbang's original formula to give me the second value for each subgroup as explained in the post above?

Haseeb A's answer was much more on track than my simple solution. It ensures that the first value will be returned, no matter the sort.

Here is Haseeb A's formula, modified to account for returning multiple results.

=INDEX(C2:C20,SMALL(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C20)-ROW(C2),))=1,ROW($C$1:$C$19)),ROW(1:1)))
This is confirms with CTRL+SHIFT+ENTER

Then copy down the number of row that you desire.

Originally Posted by Whizbang

Haseeb A's answer was much more on track than my simple solution. It ensures that the first value will be returned, no matter the sort.

Here is Haseeb A's formula, modified to account for returning multiple results.

=INDEX(C2:C20,SMALL(IF(SUBTOTAL(3,OFFSET(C2,ROW(C2:C20)-ROW(C2),))=1,ROW($C$1:$C$19)),ROW(1:1)))
This is confirms with CTRL+SHIFT+ENTER

Then copy down the number of row that you desire.

OK, this is great, you guys are really on to it here. Can I ask one more favor, and sorry to be a pain, but this formula is way over my head. Instead of saying "copy down the number of row that you desire", can you spell out exactly what this formula would look like if I wanted to return the second data value from each category? I think if I saw that, it would help me understand the formula better.

I can't thank you all enough, great forum here.

A small correction on the formula:

=INDEX($C$2:$C$20,SMALL(IF(SUBTOTAL(3,OFFSET(C2,ROW($C$2:$C$20)-ROW(C2),))=1,ROW($C$1:$C$19)),ROW(1:1)))


Copied down one cell, it would be:
=INDEX($C$2:$C$20,SMALL(IF(SUBTOTAL(3,OFFSET(C3,ROW($C$2:$C$20)-ROW(C3),))=1,ROW($C$1:$C$19)),ROW(2:2)))

But you don't need to manually adjust the formula. Simply select the first cell and Copy it. Then select the desired number of cells below and then Paste. The formula will automatically adjust. That is the beauty of cell referencing. It can be fixed using the $ symbol, or relative by omiting the $. Do a Google search on cell referencing and read up on Relative, Fixed and Mixed references.

You can also take advantage of the AutoFill function. Do a search on that for more information.