linking cells

[Joanne]

I am using msoffice 2003 and winxp pro

We have a master workbook that contains worksheets for all vendors we do
business with, each vendor having it's own worksheet in the workbook.

We have workbooks for each of our customers, each workbook containing a
worksheet for each vendor the customer buys products from.

The master workbook contains the pricing for all products. When prices
change, we are currently making those changes on the customer worksheets
manually. I want to make this automatic by linking the customer
worksheet to the appropriate vendor worksheet in the master workbook so
entering the changes once will reflect in all of the customer workbooks
that contain the worksheet that was changed in the master.

I know how to do simple cell linking between worksheets/workbooks. My
question is if this is the correct way to generate this project when we
are talking about maybe 250 customer workbooks each containing several
worksheets, all being linked to the master workbook containing about 40
vendor worksheets.

My concern is if this is the most efficient way to do the job, if you
can have this much linking going on without ending up with a 'spaghetti'
type mess, or if there is a better way of doing this, perhaps using vba.

Because I hate doing things more than once, I thought I would ask for
advice before proceeding. Any hints and tips, advice you can give me to
get this organized and up and running would surely be appreciated. You
will be giving me your time and saving me innumberable hours of
scrambling around.

Thank you
Joanne

 

[Jim Cone]

Joanne,
I would not try to link to that many workbooks.

Make an estimate of the time/cost incurred to do the updating now
and use that cost to justify hiring a contract VBA programmer.
He/she would be able to write code to open each workbook and
update each sheet with the new pricing. Press a button and 10 minutes
later the workbooks would all be updated.
Budget time for supervision/consultation and testing.

If you are contemplating going to XL2007, be aware that any code
written for xl2003 would have to be reviewed or rewritten before you
could trust it in XL2007.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel add-ins)



"Joanne"
<[email protected]>
wrote in message
I am using msoffice 2003 and winxp pro

We have a master workbook that contains worksheets for all vendors we do
business with, each vendor having it's own worksheet in the workbook.

We have workbooks for each of our customers, each workbook containing a
worksheet for each vendor the customer buys products from.

The master workbook contains the pricing for all products. When prices
change, we are currently making those changes on the customer worksheets
manually. I want to make this automatic by linking the customer
worksheet to the appropriate vendor worksheet in the master workbook so
entering the changes once will reflect in all of the customer workbooks
that contain the worksheet that was changed in the master.

I know how to do simple cell linking between worksheets/workbooks. My
question is if this is the correct way to generate this project when we
are talking about maybe 250 customer workbooks each containing several
worksheets, all being linked to the master workbook containing about 40
vendor worksheets.

My concern is if this is the most efficient way to do the job, if you
can have this much linking going on without ending up with a 'spaghetti'
type mess, or if there is a better way of doing this, perhaps using vba.

Because I hate doing things more than once, I thought I would ask for
advice before proceeding. Any hints and tips, advice you can give me to
get this organized and up and running would surely be appreciated. You
will be giving me your time and saving me innumberable hours of
scrambling around.

Thank you
Joanne

 

[Joanne]

Thanks for your input Jim. It has given me pause regarding taking on
this project. Thing is, we are just a small struggling company and
cannot really afford much in the way of professional services.

I was thinking if maybe this approach would work instead.
The vendor worksheets are already in a workbook, having a column for
each of the individual pricing levels.

What if I create a worksheet (or some type of splash screen with all
customers listed) listing all customers, and when you click on a
customer name, the code running behind that particular cell would open
the worksheets that have the vendor info and proper pricing column
needed for that customer. If I opened the worksheets directly from the
Master workbook itself, I wouldn't need to do any linking at all because
we would be looking at the source and it would always have the latest
and greatest pricing in it.

The reason for doing this would be for the sales personnel to be able to
generate a printout for the customer with the customer's pricing on it,
then simply close the worksheet and the job is done. Also, if a customer
wanted to know their special pricing from more than one of the vendors,
all the vendors' worksheets would be open and ready for the sales person
to investigate.

It would be great if the code behind the customer name would open all of
the relevant worksheets in a 'temp' workbook for ease of use, then just
close them all and dump the 'temp' workbook until someone else needs to
generate the info.

Am I barking up the wrong tree, or could this be a viable approach?
I sure do appreciate your time and efforts to help me get this project
in perspective and decide if I can make it work in some manner.