Automatically generating spreadsheet B from spreadsheet A

[Clive]

Hi,

I'm trying to achieve the following but unsure how to go about it.
I have Excel 2007 and Access 2007.

I have a spreadsheet (A) with a list of plants. One column is a
"pick list". What I want to do is: if I put a non-zero figure in the
pick list, then I automatically generate a second spreadsheet (B)
which only contains those plants with non-zero quantity.

I send spreadsheet B to a wholesale supplier who sends back the
price next to the plants. I then "automatically" generate spreadsheet
C which has extra columns added which I use to calculate the retail
price of the plants.

Is there some straightforward way using macros or something to does
this spreadsheet creation? I'm only looking for guidance on which tool
to use, not for details. I can research the detail myself.


Thanks

Clive

 

[JLatham]

The answer is "yes - macros could handle all of this for you".

You've used the word "spreadsheet" a couple of times, and for purposes here
we need to be clear about what you mean. A 'workbook' is a .xls file, a
'sheet' or 'worksheet' is one tab within a workbook. When seeking further
help, be sure you use the more accurate terminology to get best answers.

But whether you want to create additional sheets within a workbook, or
create separate workbooks based on the contents of the source (A), macros can
do the job for you. You mentioned Access2007 - not sure why or if it comes
into play at all at this time. But as far as getting data out to your
suppliers, Excel is going to be the easier to use and probably the most
familiar to them. Also, when you send Excel files to them, be sure they are
in a format that they'll be able to use; not everyone has moved up to 2007
yet, so you'll need to send in a format compatible with earlier versions.

My logic for the first part of your task would probably be something like
this:
Examine the 'pick list' column in (A) and determine if there any non-zero
values at all - if there are not, then just quit. If there are non-zero
values, then create a new workbook/worksheet and copy non-zero related
information into (B).

For the second part of the task I'd have macro in (A) check to see if
another file is open (B) and if so, open up/create (C) [which would probably
be based on a template or standard .xl** file with your formulas for
calculating retail price already in it] and copy the information from (B)
into (C) to finish up the calculations.

There are some other interesting things that could be done with Access at
this point also - with a properly designed database, you could export all of
the information from (C) into Access so that you could have the ability to
search for any plant and get it's wholesale/retail pricing, or search by
supplier to see what plants you're getting from them at what cost, etc. The
advantage of this added step (which could actually replace the 2nd task
above) is that all of your information for your entire stock is in one place
rather than in numerous Excel files. The problem with using Access as the
total solution is one of getting the data exchange between yourself and your
wholesellers: fewer people have Access than have Excel 2007.

Hope this helps some.