List of names that needs to be identical and updated in different tables across sheets

Hi everyone,
I am new here and in a bind trying to figure out and have found a lot of useful information here, just have not come across a solution for my issue. Hope I can explain this properly and any help is appreciated in advance.

I have several sheets with tables that need to contain one identical common column called "product name" while all the other columns are different on each table. The issue is that this list in the "product name" column changes by adding, removing and even name edits and currently I need to edit each every table for each change. How can I only have one uniform list to modify that is represented on all these tables that updates.

I have tried a master list with links but causes issues when a product name row is deleted in the master it does not delete the row in the others as well as adding a new name to the master list requires me to recreate a link in every table which defeats the purpose of it trying to save me time having to modify something different on every table. Also each of these lists are sorted or filtered often which I want to avoid being reflected across all of the other tables and lists.

Thank you for your time and help.

I can create a small example if necessary.

We would really like to see a sample!

Regards
Alastair

Hello everyone,

Thanks for all the quick responses, I have come up with a quick example and some text in it to describe things. Let me know if you have any questions.

I uploaded it to my google docs page, here is the link

https://docs.google.com/file/d/0B7Mk...it?usp=sharing

not sure if you prefer a different source or anything.(just found the attachment ability but I will leave both sources available)

Thanks again!


Example spreadsheet.xlsx

Originally Posted by FDibbins

Hi and welcome to the forum

1st, instead of deleting a row that you no longer need, delete teh contents instead
2nd when you make your links, try is this way...
=IF(Sheet2!A3="","",Sheet2!A3)

This way, you can copy that wayyyy down, and if there is nothing in sheet2 then sheet1 will not show anything
If you need to "remove" the rows you deleted the into in (1 above), you can eithetr hide that row, or sort your data

If this doesnt do waht you want, let me know please (and why it doesnt work?)

Hi FDibbins,
so with the links I have found that it could only work if I make a specific master list to reference as using any other table causes issues with filters and sorting being applied as it messes up the order of the others. Also by just deleting the contents in the product name would not clear the many other columns for each table or require some if statement as well. Though I wonder if a conditional filter or something could solve that by IF the product name in the row is blank then delete the field. Though hat would have to be configured for every other cell. Also that does not fix the issue of needing to create a row or expanding the table when a new name is added to another column as they would have to reapply the filters. Great thinking though, let me know what you think and when you get a chance to look over the basic sample I have posted

Thanks

Hi FDibbins,

Thanks for the response, I have tried the formula sent and it does not seem to delete with rows only change that one to match the next line creating a duplicate and it leaves the rest of the row information when then changes the order making the stats for product4 now for product 5 on different tables. Also the problem I have had with links is the filters really mess it up and I would have to link to a unique master list. This also does not address the issue of adding items without recreating the formula on every table. I was hoping their was some relationship that could be made between columns in tables or something that would help me with this.

I the function of a pivot table pulling a set of rows from a source table is kind of the function I was looking for but I could not find a way to do this allowing me to create custom columns to the pivot table to enter text without linking to a source data. Not sure if that is clear, like putting a pivot table like thing with only the product list source data on each sheet table and then finding a way to add columns in it for the rest of the info. Hope that is explained properly, I can even post a sample of what I was trying with this or looking for to see if its possible.

Thanks
-Kirt

Hi Kirt

I'm not sure what you are trying to achieve (and don't worry - it's a problem I often have )

I can understand that if a product is added to Table1 you want it to be added to the other 6(?) sheets. I also understand the same for deletions. However changing a Product Name is not possible unless you have a Product reference (if Product1 becomes Product1a how will Table2 know that 1a replaces 1, raher than being a new product?)

Are you just interested in the Product name being kept in line with table1 or will there be other columns from Table1 that need to be updated (eg on Table 6 or whatever).

Finally - how many products are you dealing with?

Not sure if I can help, but certain I can't until I get my head round these questions!

regards
Alastair

Hi Kirt

Thanks for the update. I am assuming (I like to live dangerously!) that there is some good reason why you have to have separate sheets, rather than just one sheet with different views available to different people.

You make reference to 3 sheets (40, 21 and "less" columns) with the possibility of the 40 being split. Other than the name, is there any other commonality between the sheets or is the data in the 39, 20 and less-minus-1 all different? (I ask because if there is some commonality, will you require any alterations on (say) 21 to be reflected in the other 2 (or 3) sheets?

A workable way to do all this is to have a master sheet containing all columns, with a macro to update the other sheets when any change is made. Having a Product ID (which never changes) will considerably ease Product Name alterations, but if you insist on making things difficult, it can be achieved without a Product ID.

If a Master sheet is not possible, there are other possibilities to explore.

As I do not know to what use the data is being put, it may well be that a pivot table is a viable way forward. Are you aware that pivot tables can get data from different worksheets? Not something I have done to any great extent, but just so that you are aware.

You are way ahead of me with "table relationships" - I have not heard of this concept, but guess what I am about to Google?

Look forward to hearing from you.

Regards
Alastair

Hi Alastair,

Other than the product name their are no other columns that are in relation to other tabs at all, so no this column minus another type thing. I can safely say almost every column is general data and text that holds no value for calculations. It is just how the data is laid out and the sheets are used. One other column exists in all the tables beside the product name and that is a priority value which is just a number from 1-3 it is minimal concern, does not change unless a product is added or removed and sadly cannot provide a unique value per item to better associate with name changes. The other column is a name for the person responsible for the product and this also does not change per item and I do not care if it can be transferred or not as it is not important but also cannot distinguish the items. A product ID number or some sorting system would be great but other than specifically assigning ones for each item and keeping track of that it is not something available other than just using the name. Yes the information on each table in each sheet is unique and having different purposes and the information is all manually entered in by various people. at different times and referenced.

A master list is possible as I can easily create an extra tab to manage that but I am not sure if macros can take care of all I need and positive I do not know how to do it if it is possible. I am aware of pivot tables being able to pull from multiple sheets but sadly I do not want to consolidate information from many tables of information but use one table of information as the basis for more.

I had seen about table references online for 2013 with powerpivot or power something for excel, I was reading about it but was a little over my head on trying to figure out how if at all it could really help my problem other than a relationship between my tables was an idea of what I needed

Hi Kirt

newdoverman's solution works ok , but does not cope well with people sorting table2 onward. Also, If something is deleted from table1, the Product Name is deleted from the other sheets, but not the other sheet's details.

Are you happy wit these restrictions, or do we need to go further?

Regards
Alastair

Hi Alastair and new doverman,

The biggest issue with the solution that makes it not usable is the sorting problem. each table will be frequently sorted based on the owner name, priority tier and alphabetically for the product name which seems it just goes all wacky with the other columns. It is a great effort though, I guess I will just continue to do things the way I have and hope one day to come across a feature that can meet my needs. Thanks again for all your effort and help

-Kirt

Hi Kirt

You should not have too long to wait for something that meets your needs. I am working on a system that identifies changes and additions to each sheet and updates all other sheets. It is mainly based on a macro. Its biggest limitation is it gives no warning if sheet 2 and sheet 3 are changed. (I have to leave something for the MK2 modification)

Unfortunately, having got quite some way into it, I decided to change track so I have now run out of time, but I will get this cracked sometime this week (my day job gets in the way of my creative work!)

I understand where newdoverman is coming from, but I do not believe that I am "hammering" excel - more like giving it some real work to do .

I will get back to you as soon as I can

Regards
Alastair

Hi Kirt

Here is the first draft. There is no Master sheet and no Product IDs. I have set Product2 for deletion in Table 1, Amended Product 7 in Table2 and added a new Product15 in Table3.

The system will delete lines, but for testing purposes, I have merely highlighted the deletions

Needs tightening up in places, but is this something worth progressing or just something that works as an academic excercise but not for use in the real world?

Regards
Alastair