Dynamically filter master sheet into multiple sheets based on columns with blank cells

[David C]

Its my first time posting in this forum but have been searching through it for a while and people seem to be really helpful. I am having a very hard time figuring out the best way to accomplish what I am trying to do. I have a master sheet with multiple columns and hundreds of rows and none of the cells have the same values to filter by other than those containing blank cells in specific columns. I am trying to split the master sheet into 3 sheets named open cases, finished cases and paid/closed. The logic that will place a row onto each sheet is based on having blank cells in different columns. As I progress on a case inputting data in the row it will move from sheet to sheet, with the master sheet having all of the cases regardless of the status. I have tried writing macros, using filters, lookup functions, but everything I keep trying doesn't seem to work. I also need this to work dynamically as I continue to add on to the master sheet which is where I keep running into problems. Would love some input. Thanks.

-David

 

[SanjayGMusafir]

Its my first time posting in this forum but have been searching through it for a while and people seem to be really helpful. I am having a very hard time figuring out the best way to accomplish what I am trying to do. I have a master sheet with multiple columns and hundreds of rows and none of the cells have the same values to filter by other than those containing blank cells in specific columns. I am trying to split the master sheet into 3 sheets named open cases, finished cases and paid/closed. The logic that will place a row onto each sheet is based on having blank cells in different columns. As I progress on a case inputting data in the row it will move from sheet to sheet, with the master sheet having all of the cases regardless of the status. I have tried writing macros, using filters, lookup functions, but everything I keep trying doesn't seem to work. I also need this to work dynamically as I continue to add on to the master sheet which is where I keep running into problems. Would love some input. Thanks.

-David

Not at all difficult
Supposing Your master Data is on Sheet1 -
You want column A to appear on new sheet
and want to filter out blank Values of Column B

=FILTER(Sheet1!A:A,Sheet1!B:B<>"")

Try it.

I'm sure once you will start, you will many more questions to ask.

But one thing you must remember that any work you will be able to do on Master Sheet only. FILTER function only fetches data based on conditions you give.

 

[David C]

Not at all difficult
Supposing Your master Data is on Sheet1 -
You want column A to appear on new sheet
and want to filter out blank Values of Column B

=FILTER(Sheet1!A:A,Sheet1!B:B<>"")

Try it.

I'm sure once you will start, you will many more questions to ask.

But one thing you must remember that any work you will be able to do on Master Sheet only. FILTER function only fetches data based on conditions you give.

Right, my issue is that I want to be able to update the master sheet by adding in data to the subcategory sheets. I also want to be able to have the entire row of the master sheet on each new sheet so that I have all of the needed information to go off of while working.

 

[SanjayGMusafir]

Right, my issue is that I want to be able to update the master sheet by adding in data to the subcategory sheets. I also want to be able to have the entire row of the master sheet on each new sheet so that I have all of the needed information to go off of while working.

Pulling entire row is not difficult - I was expecting that question

Challenge is that you want to work on subcategory sheets and want that data to be updated back to master sheet

That can be done but you need to choose one way of working on data and pulling back -
Either working on subcategories and pulling from Subcategories to Master
or best is to work on Master and pull data on subcategories

Both way sync - I don't think it happens unless you create a data set on all sheets separately.

Probabaly someone tells you a way here and I also learn from that.

 

[SanjayGMusafir]

I also work with such a database where 2 directional sync is done. And believe me it makes system so slow during the sync.

But even with that you can sync any chosen column(s) in one direction only and pull rest of the data from one sheet to another.