Pulling data from one sheet and updating certain cells daily

[myshadeofglory]

I work for a manufacturing company that has many different clients who we produce parts for. We have an excel workbook called the "Master Schedule" containing all of our orders which our contract administrator (Lisa) is constantly updating with relevant front end information (i.e. changed need by dates, changed order quantities, etc).

We also have three sales engineers who want to be able to access, update and manipulate their own clients' back end information (i.e. adding their own columns, cell formatting, and basically their own unique ways of tracking their data) so they can track where parts are in the production process, when they last contacted the customer regarding their order, etc.

Since we don't have Microsoft Access and all of our company doc's are on a server, our current solution is to create four different workbooks (one for each of our three top customers and then an "other customers" workbook) that will utilize a macro, pulling entire rows of information based upon the value in the "Customer [Name]" column of our Master Schedule and adding them to the next blank row on the corresponding Sales Team doc's.

For example, if in the "Customer" column (column F) on the Master Schedule there is a value of "GE Atlanta," we want our "GE Atlanta Schedule" workbook to pull that entire row of data concerning that project into its sheet1.

The "Customer" column and "PO#" column will always remain the same, but we need the "Quantity" column, "Need by date" column, etc. to update automatically (whenever the sales team opens their documents) if Lisa changes anything on the Master Schedule.

I realize that I'm trying to turn excel into a complex custom data management system, more or less. Not even sure if it can be done. Any help here?

Thanks all, in advance.

[arlu1201]

Maybe if we can see a sample of the data, we can suggest something.

[JBeaucaire]

This could be done without VBA. By adding an INDEXing formula in a new column of the master database, you could provide a unique "key" for each row that allows the other workbooks to spot it as relating its customer and display info from that row.

Assuming the "customer" is in column A, over in an empty AA2 you put this formula:

=$AA2 & "-" & COUNTIF($A$2:$A2, $A2)

Copy that down so it's on every row... see what it creates?

GE Atlanta-1
GE Atlanta-2
IBM-1
GE Atlanta-3
IBM-2
...etc.

With that, you're done making changes to that sheet.

Now in your GE Atlanta Scheduling workbook, add a field in AA1 to count the number of values from the other workbook that relate this this one:

=COUNTIF('[Master Schedule.xls]Sheet1'!$A:$A, "GE Atlanta")

Now in AA2, put this formula and copy down:

=IF(ROW(A1)>$AA$1, "", "GE Atlanta-" & ROW(A1))

Now look... you have a unique matching value on both sheets. With this, INDEX/MATCH can bring across the matching row data. Over in A2, put this;

=IF($AA2="", "", INDEX('[Master Schedule.xls]Sheet1'!B:B, MATCH($AA2, '[Master Schedule.xls]Sheet1'$AA:$AA, 0))

Copy that down and across your table... adjust the indexed column to display the information you want.

[myshadeofglory]

The formula "=$A2 & "-" & COUNTIF($A:$A, $A2)" seems to give me the total count, not a running count thus far. Am I miss something in my formula?

[myshadeofglory]

So, I figured out a quick way to generate the outcome you wanted it's:

=$A2 & "-" & COUNTIF($A$2:$A2, $F2)

Giving step two a go now.

[myshadeofglory]

Thanks, Jerry. This is exactly what I'm trying to do. Great and creative ideas. I have one final question. I'd prefer my cells without values to remain blank. I've tried the standard, " " at the end of the final formula, but I keep getting an error. If there a way to make that happen given the current formula?

=IF($BA2="", "", INDEX([MASTERSCHEDULE.xls]MSTRSCHEDULE!R:R, MATCH($BA2, [MASTERSCHEDULE.xls]MSTRSCHEDULE!$AQ:$AQ, 0)))

Thanks, again!

[JBeaucaire]

Your formula looks correct. What is wrong with it, what is happening you want to change?

[myshadeofglory]

Empty cells default to 0 now. I think I can just go back and delete them. Wasn't sure if there was a way to have them remain blank if they are blank on the Master Schedule.

[JBeaucaire]

You can turn off the display of "zero" on just that one sheet, go to TOOLS > OPTIONS > VIEW > [x] Zero Values....turn that off.