Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
I was approached today by one of the VP's of the company I work for. He would
like to be able to enter information and then have the information sent to where it needs to go. For Example: One person would enter Salesman; Company Sold To; State; Pairs sold. So Column A would be the salesman; Column B would be the Company Sold To Column C would be the State the company sold to is in Column D would be the Amount of Pairs sold to the company. Then after all that information is entered; He wants to be able to see the percentage of the salesman's sales, in a given state. So let's have it sort by the state; The salesman is assigned 6 states. I can sort by the state, that's not a problem. I am just wondering if this would be easiest in Excel or another program, access or something. And If I do it in excel; I'm not very familiar w/ macro's. So if you may be able to point me somewhere for Macro Knowledge; it would be appreciative. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
Once you do this, chances are the data analysis will change, i.e., he'll want
more detail, better refinement, or new fields. You would be better off in MS-Access. Take the time to plan the database, the tables, and the data "normalization". You will be glad you did. Plus, Access will have better reporting capability. Question? It surprises me that these sales are not being entered somewhere else, and that application should be capable of making these "reports". If so, you can either put a 'hook' into that database with Excel, or a third-party report writer, to get this done. This would eliminate the requirement for Excel or Access. You can do the extraction of the data to MS-Access, too. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: I was approached today by one of the VP's of the company I work for. He would like to be able to enter information and then have the information sent to where it needs to go. For Example: One person would enter Salesman; Company Sold To; State; Pairs sold. So Column A would be the salesman; Column B would be the Company Sold To Column C would be the State the company sold to is in Column D would be the Amount of Pairs sold to the company. Then after all that information is entered; He wants to be able to see the percentage of the salesman's sales, in a given state. So let's have it sort by the state; The salesman is assigned 6 states. I can sort by the state, that's not a problem. I am just wondering if this would be easiest in Excel or another program, access or something. And If I do it in excel; I'm not very familiar w/ macro's. So if you may be able to point me somewhere for Macro Knowledge; it would be appreciative. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
Well Our company software does; Although, we only want to track future orders
that won't be entered into the company software, only because it can't really keep track of what is future and what is at once orders. So that is why we don't do that now, or your right, we would have that ability. But trying to save money, this is what you get, extra work when it shouldn't be needed. "Kooster" wrote: Once you do this, chances are the data analysis will change, i.e., he'll want more detail, better refinement, or new fields. You would be better off in MS-Access. Take the time to plan the database, the tables, and the data "normalization". You will be glad you did. Plus, Access will have better reporting capability. Question? It surprises me that these sales are not being entered somewhere else, and that application should be capable of making these "reports". If so, you can either put a 'hook' into that database with Excel, or a third-party report writer, to get this done. This would eliminate the requirement for Excel or Access. You can do the extraction of the data to MS-Access, too. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: I was approached today by one of the VP's of the company I work for. He would like to be able to enter information and then have the information sent to where it needs to go. For Example: One person would enter Salesman; Company Sold To; State; Pairs sold. So Column A would be the salesman; Column B would be the Company Sold To Column C would be the State the company sold to is in Column D would be the Amount of Pairs sold to the company. Then after all that information is entered; He wants to be able to see the percentage of the salesman's sales, in a given state. So let's have it sort by the state; The salesman is assigned 6 states. I can sort by the state, that's not a problem. I am just wondering if this would be easiest in Excel or another program, access or something. And If I do it in excel; I'm not very familiar w/ macro's. So if you may be able to point me somewhere for Macro Knowledge; it would be appreciative. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
Well; I've decided to do this in excel; Only because I am more familiar w/
excel than access. Is there an easy way to illustrate the percentages each state has bought according to pairs? "JeremyH1982" wrote: Well Our company software does; Although, we only want to track future orders that won't be entered into the company software, only because it can't really keep track of what is future and what is at once orders. So that is why we don't do that now, or your right, we would have that ability. But trying to save money, this is what you get, extra work when it shouldn't be needed. "Kooster" wrote: Once you do this, chances are the data analysis will change, i.e., he'll want more detail, better refinement, or new fields. You would be better off in MS-Access. Take the time to plan the database, the tables, and the data "normalization". You will be glad you did. Plus, Access will have better reporting capability. Question? It surprises me that these sales are not being entered somewhere else, and that application should be capable of making these "reports". If so, you can either put a 'hook' into that database with Excel, or a third-party report writer, to get this done. This would eliminate the requirement for Excel or Access. You can do the extraction of the data to MS-Access, too. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: I was approached today by one of the VP's of the company I work for. He would like to be able to enter information and then have the information sent to where it needs to go. For Example: One person would enter Salesman; Company Sold To; State; Pairs sold. So Column A would be the salesman; Column B would be the Company Sold To Column C would be the State the company sold to is in Column D would be the Amount of Pairs sold to the company. Then after all that information is entered; He wants to be able to see the percentage of the salesman's sales, in a given state. So let's have it sort by the state; The salesman is assigned 6 states. I can sort by the state, that's not a problem. I am just wondering if this would be easiest in Excel or another program, access or something. And If I do it in excel; I'm not very familiar w/ macro's. So if you may be able to point me somewhere for Macro Knowledge; it would be appreciative. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
This really is a database application, so set Excel up to "roll-up" the data
into an unused portion of the worksheet (use the DATA functions) based on your criterion. Then, do your calcs there. You could produce a graph, too. You could use Data Form for the data entry to speed the process and prevent errors. You can learn MS-Access quickly for this application (no complex tables and relationships). If I were you, I would get a book, or run a tutorial (Help, or MS-Office Web site). Your existing application - In place of future orders, can you enter quotes or estimates (like in job costing)? You could manage the future orders there, then convert them to an order at the time of sale. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: Well; I've decided to do this in excel; Only because I am more familiar w/ excel than access. Is there an easy way to illustrate the percentages each state has bought according to pairs? "JeremyH1982" wrote: Well Our company software does; Although, we only want to track future orders that won't be entered into the company software, only because it can't really keep track of what is future and what is at once orders. So that is why we don't do that now, or your right, we would have that ability. But trying to save money, this is what you get, extra work when it shouldn't be needed. "Kooster" wrote: Once you do this, chances are the data analysis will change, i.e., he'll want more detail, better refinement, or new fields. You would be better off in MS-Access. Take the time to plan the database, the tables, and the data "normalization". You will be glad you did. Plus, Access will have better reporting capability. Question? It surprises me that these sales are not being entered somewhere else, and that application should be capable of making these "reports". If so, you can either put a 'hook' into that database with Excel, or a third-party report writer, to get this done. This would eliminate the requirement for Excel or Access. You can do the extraction of the data to MS-Access, too. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: I was approached today by one of the VP's of the company I work for. He would like to be able to enter information and then have the information sent to where it needs to go. For Example: One person would enter Salesman; Company Sold To; State; Pairs sold. So Column A would be the salesman; Column B would be the Company Sold To Column C would be the State the company sold to is in Column D would be the Amount of Pairs sold to the company. Then after all that information is entered; He wants to be able to see the percentage of the salesman's sales, in a given state. So let's have it sort by the state; The salesman is assigned 6 states. I can sort by the state, that's not a problem. I am just wondering if this would be easiest in Excel or another program, access or something. And If I do it in excel; I'm not very familiar w/ macro's. So if you may be able to point me somewhere for Macro Knowledge; it would be appreciative. Thank you. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
Your existing application - In place of future orders, can you enter
quotes or estimates (like in job costing)? You could manage the future orders there, then convert them to an order at the time of sale. your right, we can; But we would have to pay someone to create that report. It would be using Crystal Reports; not sure if you've heard of it or not. But that would be more costly than just doing this excercise in excel. I'm not very familiar w/ the function "DATA". But I believe I have already made the spreadsheet in excel; it's pretty plain. But it serves it's purpose. Thank you though. "Kooster" wrote: This really is a database application, so set Excel up to "roll-up" the data into an unused portion of the worksheet (use the DATA functions) based on your criterion. Then, do your calcs there. You could produce a graph, too. You could use Data Form for the data entry to speed the process and prevent errors. You can learn MS-Access quickly for this application (no complex tables and relationships). If I were you, I would get a book, or run a tutorial (Help, or MS-Office Web site). P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
I have the spreadsheet setup; Now I'm wondering about graphs and stuff.
The data entry part is on Sheet 1; The states are sorted on Sheet 2; the Salesman on Sheet 3. I would like to know how to make a graph, for Sheet 2 and Sheet 3, based on the corresponding information. Such as on Sheet 3, I would love to see a graph, that shows percentage of the the sales of the salesman, meaning if salesman 1 sold 265 prs, and salesman 2 sold 345 prs. Add 265 & 345 to get 610; so, Salesman 1 sold 43% of total sales, and salesman 2 sold 57% of total sales. Sheet 2 I would love the same thing, only since it's the state one, have the graph show which state sold what percentage of total sales. "Kooster" wrote: This really is a database application, so set Excel up to "roll-up" the data into an unused portion of the worksheet (use the DATA functions) based on your criterion. Then, do your calcs there. You could produce a graph, too. You could use Data Form for the data entry to speed the process and prevent errors. You can learn MS-Access quickly for this application (no complex tables and relationships). If I were you, I would get a book, or run a tutorial (Help, or MS-Office Web site). Your existing application - In place of future orders, can you enter quotes or estimates (like in job costing)? You could manage the future orders there, then convert them to an order at the time of sale. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: Well; I've decided to do this in excel; Only because I am more familiar w/ excel than access. Is there an easy way to illustrate the percentages each state has bought according to pairs? "JeremyH1982" wrote: Well Our company software does; Although, we only want to track future orders that won't be entered into the company software, only because it can't really keep track of what is future and what is at once orders. So that is why we don't do that now, or your right, we would have that ability. But trying to save money, this is what you get, extra work when it shouldn't be needed. "Kooster" wrote: Once you do this, chances are the data analysis will change, i.e., he'll want more detail, better refinement, or new fields. You would be better off in MS-Access. Take the time to plan the database, the tables, and the data "normalization". You will be glad you did. Plus, Access will have better reporting capability. Question? It surprises me that these sales are not being entered somewhere else, and that application should be capable of making these "reports". If so, you can either put a 'hook' into that database with Excel, or a third-party report writer, to get this done. This would eliminate the requirement for Excel or Access. You can do the extraction of the data to MS-Access, too. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: I was approached today by one of the VP's of the company I work for. He would like to be able to enter information and then have the information sent to where it needs to go. For Example: One person would enter Salesman; Company Sold To; State; Pairs sold. So Column A would be the salesman; Column B would be the Company Sold To Column C would be the State the company sold to is in Column D would be the Amount of Pairs sold to the company. Then after all that information is entered; He wants to be able to see the percentage of the salesman's sales, in a given state. So let's have it sort by the state; The salesman is assigned 6 states. I can sort by the state, that's not a problem. I am just wondering if this would be easiest in Excel or another program, access or something. And If I do it in excel; I'm not very familiar w/ macro's. So if you may be able to point me somewhere for Macro Knowledge; it would be appreciative. Thank you. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
It sounds like you set this up like a relational database, i.e., multiple
tables (sheets). Excel works as a flat-file database (one file/table, multiple fields and records). You can use lookup functions on Excel to help with data entry, e.g., salesperson number produces name in another column. You can use the data form to produce a data entry screen in field order. You can produce an area in the spreadsheet to summarize data and act on it. Look this over: http://office.microsoft.com/en-us/as...186941033.aspx Personally, I would use Crystal Reports (has a better report writer), or Excel using Microsoft Query. Look he http://office.microsoft.com/en-us/as...864661033.aspx P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: I have the spreadsheet setup; Now I'm wondering about graphs and stuff. The data entry part is on Sheet 1; The states are sorted on Sheet 2; the Salesman on Sheet 3. I would like to know how to make a graph, for Sheet 2 and Sheet 3, based on the corresponding information. Such as on Sheet 3, I would love to see a graph, that shows percentage of the the sales of the salesman, meaning if salesman 1 sold 265 prs, and salesman 2 sold 345 prs. Add 265 & 345 to get 610; so, Salesman 1 sold 43% of total sales, and salesman 2 sold 57% of total sales. Sheet 2 I would love the same thing, only since it's the state one, have the graph show which state sold what percentage of total sales. "Kooster" wrote: This really is a database application, so set Excel up to "roll-up" the data into an unused portion of the worksheet (use the DATA functions) based on your criterion. Then, do your calcs there. You could produce a graph, too. You could use Data Form for the data entry to speed the process and prevent errors. You can learn MS-Access quickly for this application (no complex tables and relationships). If I were you, I would get a book, or run a tutorial (Help, or MS-Office Web site). Your existing application - In place of future orders, can you enter quotes or estimates (like in job costing)? You could manage the future orders there, then convert them to an order at the time of sale. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: Well; I've decided to do this in excel; Only because I am more familiar w/ excel than access. Is there an easy way to illustrate the percentages each state has bought according to pairs? "JeremyH1982" wrote: Well Our company software does; Although, we only want to track future orders that won't be entered into the company software, only because it can't really keep track of what is future and what is at once orders. So that is why we don't do that now, or your right, we would have that ability. But trying to save money, this is what you get, extra work when it shouldn't be needed. "Kooster" wrote: Once you do this, chances are the data analysis will change, i.e., he'll want more detail, better refinement, or new fields. You would be better off in MS-Access. Take the time to plan the database, the tables, and the data "normalization". You will be glad you did. Plus, Access will have better reporting capability. Question? It surprises me that these sales are not being entered somewhere else, and that application should be capable of making these "reports". If so, you can either put a 'hook' into that database with Excel, or a third-party report writer, to get this done. This would eliminate the requirement for Excel or Access. You can do the extraction of the data to MS-Access, too. P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ "JeremyH1982" wrote: I was approached today by one of the VP's of the company I work for. He would like to be able to enter information and then have the information sent to where it needs to go. For Example: One person would enter Salesman; Company Sold To; State; Pairs sold. So Column A would be the salesman; Column B would be the Company Sold To Column C would be the State the company sold to is in Column D would be the Amount of Pairs sold to the company. Then after all that information is entered; He wants to be able to see the percentage of the salesman's sales, in a given state. So let's have it sort by the state; The salesman is assigned 6 states. I can sort by the state, that's not a problem. I am just wondering if this would be easiest in Excel or another program, access or something. And If I do it in excel; I'm not very familiar w/ macro's. So if you may be able to point me somewhere for Macro Knowledge; it would be appreciative. Thank you. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Database
The Data Form helps.. for other people entering the information.
I would love to be able to use Crystal Reports to create my own thing; But I don't know the first thing to it. I appreciate your time. I am going to keep this spreadsheet simple. It does what I need it to do. I always have a habit of making things better, but I'm done. It's over, and it works. Always the best part. Thanks again, Jeremy "Kooster" wrote: It sounds like you set this up like a relational database, i.e., multiple tables (sheets). Excel works as a flat-file database (one file/table, multiple fields and records). You can use lookup functions on Excel to help with data entry, e.g., salesperson number produces name in another column. You can use the data form to produce a data entry screen in field order. You can produce an area in the spreadsheet to summarize data and act on it. Look this over: http://office.microsoft.com/en-us/as...186941033.aspx Personally, I would use Crystal Reports (has a better report writer), or Excel using Microsoft Query. Look he http://office.microsoft.com/en-us/as...864661033.aspx P. -- Overcome Sales Barriers http://salesbarriers.typepad.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you Sparse a field in Excel??? and Database Query?? | Excel Discussion (Misc queries) | |||
Query Excel database from desktop | Excel Discussion (Misc queries) | |||
Set Database in Excel | Excel Discussion (Misc queries) | |||
Excel Database | Excel Discussion (Misc queries) | |||
Query a Access database that has a module from Excel | Excel Discussion (Misc queries) |