LOOKUP or INDEX/MATCH on visible cells only + finding a start/end of value changes in a linear column

hycday

New Member
Joined
Dec 6, 2019
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
hi everyone,

I am trying to build a graph from data taken out a table (price evoution per date).
the table can be filtered to only list the info from a given country, so some rows will be hidden in the table.
note that (and this is important): some countries have more data than others (i.e. more date, so more price data).

another info in the table data is the fact that a program was running or not at a given date (column will show yes or no at a given date, a program will always run more than one day).

in the graph, i want to two vertical bars, one for the starting date of the program, and one for the end date of the program.
the vertical bars are fetching the date from specific cells (not in the table).

i have everything setup (graph dynamically showing based on how table is filtered, two vertical bars etc), however there are some issues i am running into :

1- i want the cells with the start date and end date to be dynamic and based only on visible cells from the data table (i.e. if i change the filter on the table, the data will change, some rows will be hidden). the formula I have today (an index/match, but same issue with a lookup) doesnt look at visible cells only, so the values are wrong. also, since the size of data can vary from a country to another, the formula should be somewhat variable on the range...
2- i am struggling to find a formula to find the end date

I tried such formula
VBA Code:
=INDEX(values!$A$2:$D$26,MATCH("yes",IF(SUBTOTAL(3,OFFSET(values!$A$3,ROW(values!$A$3:$A$25)-ROW(values!$A$3),0)),COUNTIF(values!$A$3:A3, values!$A$3:$D$25)),0),1)
but it does work (even with ctrl+shift+enter)

any idea of what i did wrong ?

attached is a screenshot, not sure how to attach an excel file, hopefully that can help
 

Attachments

  • sshots00525.png
    sshots00525.png
    95.7 KB · Views: 5

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

hernantorres23

Active Member
Joined
Nov 21, 2019
Messages
269
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Web
Hi. If you have continuous update, I think that Power Query, is good choice. Another way, it would be to use Advanced Filter for getting data that you want, could base your graphic in the filtered data.

Please try prepare a data example using add-in specifically for this and it can be found here XL2BB
Pay attention to this post XL2BB 2 Squares
 

Forum statistics

Threads
1,136,787
Messages
5,677,729
Members
419,716
Latest member
MPunt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top