Extract entire row when condition is met & create a funnel between the tabs

[djon2007]

Hello!

I am trying to find a way of extracting entire rows from one tab to another when a condition is met and create like a funnel between the tabs. I saw on different forums that it was possible to do it using VBA or Kutools, however as we are many people using this file at the same time, we have to use Excel Online, so only basic functions...

The first tab contains all the raw data with the following columns: "Internal ID - City - Region - Potential?". When the last column contains a "yes", I would like Excel to automatically move the entire row to the second tab called "to analyze" in order to shortlist the first tab. And then, repeat this process in a 3rd, 4th, 5th tab, etc, (each tab represents a different step : raw data, to analyse, analysed, etc ...). I managed to do something similar with the Filter() function. However, I either get a Spill error, or Excel extracts the last column as well which I don't want, because in the second tab I would like to have a different condition instead, and then repeat the process (enter "yes" and it would shortlist again in a third tab).
Also, if one is changed from "yes" to "no" in the first tab, I would like the formula to automatically delete the row on the other tabs, but it doesn't seem to work properly... I don't know if I'm being clear, and sorry if bad english

I have made a model and is available on the wetransfer link (I couldn't manage to attach it here): we.tl/t-eSjTGOfAq3

Thank you!

Djon

[Glenn Kennedy]

I am a bit confused about what you want... but surely all you need to do is filter ONLY the first 3 columns, using a criterion in the 4th...

[djon2007]

Thank you for your help Glenn, I apologize for the confusion

I'll try to make it simpler, let's say I would like to have 3 tabs :
- 1st tab "Database" where on each row we put all our findings/raw data
- 2nd tab "To analyse", i.e a shortlist of this 1st tab
- 3rd tab "Analysed", i.e a shortlist of this 2nd tab
As you probably understood from the short model, in each tab there would be a specific column used for data validation ("yes/no"). When entering "yes", the row would automatically paste in the next tab/step.
The filter function seems to be working but to a certain extent. When I enter "yes" for one row in the 1st tab, it is indeed going into the 2nd tab automatically, same from 2nd to 3rd tab. So far it is exactly what I am looking for.

However, let's now say that one row is now on 1st, 2nd and 3rd tab. But after consideration, I would like to change from "yes" to "no" in the 1st tab. It would indeed delete this row from the 2nd and 3rd tab, but the column with data validation doesn't move (the "yes" and "no" are staying on the same row), shifting all the data (the "yes" are not aligned with the correct rows in tab 2 and 3...)

I hope this is clearer... Thank you for your help!

Djon

[djon2007]

Thank you for your help Glenn, I apologize for the confusion

I'll try to make it simpler, let's say I would like to have 3 tabs :
- 1st tab "Database" where on each row we put all our findings/raw data
- 2nd tab "To analyse", i.e a shortlist of this 1st tab
- 3rd tab "Analysed", i.e a shortlist of this 2nd tab
As you probably understood from the short model, in each tab there would be a specific column used for data validation ("yes/no"). When entering "yes", the row would automatically paste in the next tab/step.
The filter function seems to be working but to a certain extent. When I enter "yes" for one row in the 1st tab, it is indeed going into the 2nd tab automatically, same from 2nd to 3rd tab. So far it is exactly what I am looking for.

However, let's now say that one row is now on 1st, 2nd and 3rd tab. But after consideration, I would like to change from "yes" to "no" in the 1st tab. It would indeed delete this row from the 2nd and 3rd tab, but the column with data validation doesn't move (the "yes" and "no" are staying on the same row), shifting all the data (the "yes" are not aligned with the correct rows in tab 2 and 3...)

I hope this is clearer... Thank you for your help!

Djon

[Glenn Kennedy]

Is this closer (all on one sheet).

The final results are shown in I2:K whatever, using:

=FILTER(Table2[[Internal ID]:[Region]],Table2[Analysed]="Yes")

however, if D4 (the left-hand end of a string of Yes values, is changed to No, a little VBA removes the Yes from E4 and F4 and the formula removes 245-03 from the final table...

[djon2007]

Thank you for this!

This looks very good, but as we're using Excel Online on a Sharepoint (as many people work on this file simultaneously), the macros won't work...

Do you know if this would be possible without using macros?

Many thanks!

Djon

[Glenn Kennedy]

You could use conditional formatting to highlight rows where a NO is followed (incorrectly) by a Yes... as in row 4 of the attached file.

[djon2007]

Thank you for your answer. While I like the idea of conditional formatting, it is not really what I am trying to do. I have put together a model with (hopefully) a better representation of what I am looking for with clearer instructions.


Djon

[JeteMc]

I believe that the way to overcome the issue of changing no to yes for 245-05 is to have all of the decision columns on tab one as modeled in the attached file.
I used the older INDEX based formula as the FILTER function isn't supported by my 2019 version, however I imagine that the same results could be obtained using FILTER.
Note that to get the data for 245-05 passed to Tab3 a "Yes" would have to be entered in cell E6 in Tab1.
Let us know if you have any questions.

[ChemistB]

And here's that funnel you were asking for.