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

David C

New Member
Joined
Aug 11, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

Excel Formula:
=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.
 
Upvote 0
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

Excel Formula:
=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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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
Back
Top