Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically generating spreadsheet B from spreadsheet A
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically generating spreadsheet B from spreadsheet A
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. "Clive" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically update spreadsheet from another spreadsheet | Excel Worksheet Functions | |||
I want my spreadsheet to up date automatically every day.... | Excel Worksheet Functions | |||
Generating Simple Reports From A Master Spreadsheet | Excel Worksheet Functions | |||
How can I shade every other row in Spreadsheet automatically? | Excel Discussion (Misc queries) | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) |