Automatic Update on Color

I think a sample workbook would help us understand the requirement better.

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

If the color was added manually, then a formula will bot bring that formatting back for you, you will need VBA

If the color was changed by Conditional Formatting, then you could probably use that same rule

If the color was added manually, then a formula will not bring that formatting back for you, you will need VBA

If the color was changed by Conditional Formatting, then you could probably use that same rule

The way I'm reading it, the OP wants to change the color if the revision number is changed. So some sort of change event might be in order.

probably, we will see what the OP says

Your sheet names dont quite match what uis in your file, but I think I get the jist

Thanks, and you want the color to change based on what is already in the referenced sheet, or based on the revision number changing?

And the color that was added to the referenced sheet (say, table 2 Q9:V9) was added manually?

By the way, you should try and avoid using merged cells like that, they cause all sorts of problems for formulas

If you are trying to pull back manually colored cells, then you wont be able to do that with a formula, you will need VBA (not my strong side)

Now that I have an example, I think I can take a shot at understanding the requirement.

You have a number of tables, each of which is identical in format. The Table 2 (1) in the sample workbook is there to show what the change was. You will not be adding a new sheet to indicate a change. You will be making the change in the existing worksheets themselves.

I am assuming that you will want a varying number of tables and that you are interested only in Flow Rates and Inlet Pressure.

1. You would like to produce an index of all "tables." Can I count on the sheet being labeled "Table" or do you have some other naming convention?

2. When a revision is made, you would like to highlight that change in the index. Do you want to also highlight the change in the table sheets? If subsequent revisions are made, how would you like to be notified? Do you have rules for when a revision ages out or times out?

3. Can I count on you putting the revision number in cell AB4?

Here is how I would go about designing this system – in addition to confirming the assumptions above and answering the questions, see if this will meet your needs.

There will be three “permanent” sheets: Index, Template and Parameter.

You will have a button to create a new table. When you click it, it will make a copy the template sheet and rename it either as the next “Table” in sequence or based on its contents or I can prompt you for a name.

Then I will create the index based on looping through all the sheets that aren’t named Index, template or parameters.

The parameters sheet will have a table to keep track of sheet names, revision numbers and when the revisions were made.

When the revision field is changed on any of the table sheets, it will kick off the program to highlight the changed values in the index.

In writing the requirement out, I think I answered one of my own questions: if Flow Rate Case 1 is changed in revision 2, it is highlighted. If Flow Rate Case 2 is changed in the next revision, Flow Rate case 1 gets unhighlighted and Flow Rate Case 2 gets highlighted. So the only things highlighted are those things that have changed in the latest revision.

This will take a bit of work, so I don’t want to press on until I get confirmation and clarification of the requirements.

Hi Flak
You right that I have added Table 2 (1) only for understanding this but in reality, I will make changes in the existing table.
Actually, I have considered flow rates and inlet pressure only to ease our understanding but in reality, the index will be much bigger and it will not only consider numbers also text.
1. All sheets will have name as Table 1, Table 2 consecutive numbers.
2. When a revision is made I will manually highlight the change in a colour which needs to get updated in the Index. Change in colour in table sheet is done manually. If I change data in subsequent revision for each revision I will be considering different colours for eg: Rev 1:yellow, Rev2: Red, Rev3 :green.
3. Yes cell AB4 is the revision number.
4. Actually this template is formed automatically from the system at Rev 0 and only on revisions we enter data’s manually. So we don’t have create a table again.

But I know this is complicated. To explain how tough is it. I have attached original sheet am using, actually its total 250 table but i have reduced due to size restriction.

If there is any formula or vba which can reflect the colour, value&text of cell will be much easier.

Here is my next try at understanding, but I think you've made the issue simpler. You change the color in the table manually, and you want to reflect the same color change in the index.

I see that the new index sheet has a column for every slot on the table sheet. We will have to develop a mapping of cell location in the tables to column location on the sheets. I will start this effort and explain to you how it is done, but due to the sheer size, I will leave it to you to complete it.

I get the impression that when you get a new sheet, you add it to the workbook manually. I'll add a button to detect the highest number table and add it to the index.

The challenge will be to detect when a change happens on one of the table sheets. This is normally done with code on the sheet itself but since you are importing the sheets, I have no way to put the code onto the sheet. I might have to give you a "sync" button. That is, you go out and make changes and change the revision number in cell AB4. When you click on the sync button, the program will look at this specific cell (AB4) on each sheet and compare it to a master list. If the revision number has changed, then the program will update it in the table, and also parse the sheet for color assignment in the index.

Does this sound like what you want?

I thought of a way to make this automatic when you change the revision number in the cell. I can have the template like I thought I needed I a previous post. When you add the new sheet, and click the make index button, I can copy the template, fill in all the cells from the newly-imported sheet, delete the imported sheet and change the template copy (now filled in with the correct data) to the new sheet name.

This will take a bit, so it may take several days if I can get over all the hurdles I see.

By mapping I mean I need to find out which cells in the index correspond to which cells in the tables. However, I see that you already defined those with your INDIRECT formulas. I assume that the index cells that do not have INDIRECT formulas are for manual entry.

I think I have enough to get started.

The solution I have in mind depends on detecting when the cell with the revision number I changed. This means I need to put a change event onto the page to detect when the cell is changed.

There are two ways to do this.

1. There is a way to copy code from a template sheet onto a new sheet, but it requires that the computer running the program set up something special in the File -> Option -> Trust Center -> Trust Center Settings -> Macro Settings.

2. I can give you a button that will make a copy of a template sheet. Then you can copy and paste the data into this sheet. I think this is more in line with how you do business now. You get new sheets and basically copy and paste them into a new blank sheet. Is this so? If so, then there won't be much change to the process. In fact it may be easier, the new sheet created will have the correct table number - it will also have the code behind it.

I will put the code to copy / paste code in and give you instructions for their one-time use. This way you can take the existing spreadsheet, and apply the code to all the existing tables.

Let me know if option 2 is acceptable to you.

Here is what I came up with. It makes some assumptions on how you get the Table Sheets into the workbook. I also included a very-well truncated version of the workbook that I used for development and testing.