EXCEL AUTOMATION

[franccar]

Hello Folks
I am new to this forum so be patient with me.
I work in Commercial Pre-Sales Support and I am trying to automate my day to day activity.
What I have is several sheets in the same workbook since we divides the items we sell by product family. Also each product family is subdivided into i.e cables, cards, connectors etc. I have created another sheet in the same workbook where I can drop only the codes a need and the respective quantity and via an embedded vlookup in the other sheets calculated the unit price and total price.
Since I do not know if vlookup can be used to search across all the tabs in the workbook I have copied the content of all the sheets into one and linked all the cells so that when a price or a code change is automatically updated. My issue kicks in when I need to add new rows in any of the Product family sheet, the additional row is not linked to the main sheet which all the codes...is there any way I can link ANY changes I make in one sheet to a master sheet?
How can I make sure that the linked sheet is also updated with the new row?
I Hope It Make sense.
Cheers
Franco

 

[SkipVought]

hi,

Your question, "Since I do not know if vlookup can be used to search across all the tabs in the workbook," belies your problem. You were smart enough to realize that you need all the data in a single table in order to do a lookup. In fact, this is how your design should have begun; with one table.

If you want different tabs with different products, and I really don't understand why users are so adamant about this, then use a pivot table or a query table to generate a REPORT of that item. But all your table maintenance should be in one table on one sheet. You definietly do not want linked sheets for each item.

Also take a look at AutoFilter -- ONE sheet for everything! Just filter on the item you want to display.

Using MS Query to get data from Excel faq68-5829

What are LIST & TABLE PRINCIPLES for Spreadsheet Users faq68-5184


Skip,

_[red][/red]

 

[franccar]

Thanks for the info, I will check and ask more questions for sure
Thanks you
Franco