Excel - Update/Insert records on new sheet on condition

May 12, 2015 at 11:40:17
Specs: Windows 7
Hi everybody, thanks for your help. Our employees receive lockers based upon need, location, and gender. Currently, I have a master worksheet (called 'Locker Master Data') in which I need to parse into several different worksheets based upon type of locker, employee, etc.

Column C contaions the area of the lockers. I would like to set up the workbook to automatically update and display the rows on a new sheet for all rows that have Column C = "Men: Clean"

Is there any way to set up excel to display and update the full row (record) that have "Men: Clean" in column C?

Here is some sample data, all N/A values will be replaced with real information.

Locker	Serial #	Area	Type	ID #	Employee Name	Dept. #	Job Title	Shift code
1001		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									
1002		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									
1003		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									
1004		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									
1005		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									
1006		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									
1007		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									
1008		Men: Clean	ADA / Visitor	N/A	N/A	N/A	N/A	N/A	N/A									

message edited by Pencemen


See More: Excel - Update/Insert records on new sheet on condition

Report •

#1
May 12, 2015 at 12:05:34
If you are open to a macro based solution, it would be a fairly simple excercise to write some code that would extract rows based on criteria supplied by the user or create a new sheet for each "area of the lockers" or copy rows to existing sheet(s) when rows are added to the master sheet.

If you could could a supply a little more detail about your process, such as when you want the updates to occur, how the updates are made, etc. we might be able to help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 12, 2015 at 12:14:43
I am open to a macro-based solution, but I would need a step-by-step on how to set it up. If there's any way to set it up to do the macro automatically, and update the records on the other sheets, that would be fantastic.

The 'Locker Master Data' sheet is updated manually each week. The information needs to be displayed on separate sheets when the user needs to view lockers by area such as the "Men: Clean" area. There are other areas such as "Women: Clean" and "Distribution" that have to be on their own separate sheets.


Report •

#3
May 12, 2015 at 12:44:42
re: "If there's any way to set it up to do the macro automatically, and update the records on the other sheets, that would be fantastic."

I think it could be done automatically, but I still need to know more about your process. Keep in mind that VBA (a macro) can't think on its own, it can only follow a step-by-step set of instructions.

For example, a simple, brute force process could be to wipe all of the individual sheets clean and recreate them all each time a change (any change) is made to the Master. That works, but it is inefficient.

A more elegant solution is to search for the Master record that was changed and only update that record on the separate sheet, perhaps by copying the entire row for the record that was changed. The question then becomes "When is that update made?" After any change to that record? Only after a specific cell in that row is changed? When a button is clicked after all changes are made? Your process and requirements are the answer to the "When is that update made?" question.

What about adding new records? Can that happen? If so, the code would need to know if the record was new (therefore add a row to the correct sheet) or existing (therefore find the existing record and update it). It’s obviously easier if there are never any new records since the code only has to deal with finding existing records, not adding any new ones.

All of this can (probably) be dealt with, but since the code needs to be told what to do, we need to be told the detail behind the process. Don’t be surprised if I keep asking for more details as I try to come with a solution. At some point, it might even be helpful if a copy of the workbook is uploaded to a site such as zippyshare.com so that we are not guessing as to the exact layout of your workbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question