Can anyone create spreadsheet for me?

I wonder if anyone could create a simple spread sheet for me

Gladly. But, since someone has already attempted this and it did not do what you wanted, it might be a tall order to do better/simpler without a whole lot of interaction.

Might I suggest that you look at this: http://quickbooks.intuit.com/

Look at their free version: http://quickbooks.intuit.com/commerc...00000008022902

Apparently the free version only handles up to 20 customers. But, if you like it, the full version is less than $100. If you do not like it, at least this will enable you to give more guidance (as to what you want / do not want) to the person who tries to build your next spreadsheet program for you.

NOTE: I have no relationship with Quicken. I am NOT endorsing their products in any way.

i would suggest the best way is to attach the file you already have, and list whats wrong with it, there maybe easy workarounds

I realise these files can look very daunting, but it depends on what you actually need in the end.

if you want basic information out, then putting basic information in is ok, but if you want detailed information out, then you need to enter it also.

this may just be a case of layout rather than content.

I would speak to your sister-in-law, let her run you through it, and see if she can get rid of the bits you dont like or change them into a form you do like.

there are other ways to "present" this data, but if you need all this information then there is not much choice.

if you cant get what you need from her, then a much more detailed conversation is needed on here.

i would hate to "make it less confusing" by removing something you need

I think you got the wrong message. There is no lack of willingness to help. Just not sure where to begin. Exactly.

I tend to agree with your assessment that this is too much to look at all at once and a bit unweildy. Looks like the same info needs to be entered in several places. I probably could be and should be simplified to a single place you enter data, then several different reports (different views for different purposes).

You will need a cash flow report. A balance sheet. An inventory report. But, they should all be able to feed off of a single set of data that is entered only once and done.

Do you have any sort of paper ledger you keep today? That might be the place to start from.

I would say getting those into a spreadsheet is the place to start, then. I if I could re-arrange that one sheet in a way that you could see everything you need to see without scrolling, would that be helpful?

Agree. The data entry should be once and done. There should be breaks between months. There should be double entry. All views should feed off of the single entry sheet.

You also should not need to enter the cost and price over and over.

Tell me, do you keep inventory?

Good. I guess. One less thing to keep track of for now.

OK ... this is what I have so far.
+ a sheet for keeping a list of the items you sell and their list prices and shipping cost (name of sheet: ListPrices)
+ a sheet for keeping a list of customers (name of sheet: CustomerList)
+ a sheet for entering an order ... this sheet will have most or all of the detail that your original sheet did; but, it is used for only one order at a time. (name of sheet: EnterNewSales)

For now, I am assuming a single order will not have more than 10 items being purchased by one person at one time. That will be easy to change later if necessary. For each item in the order, you will have a drop-down box from which you can select one of the items from the ListPrices sheet. When you select an item, its list price and shipping cost will automatically populate. You enter the number ordered, and the total price is calculated. If there is more than one item ordered, you use the next line, et cetera.

There is another drop-down list from which you can pick returning customers from the CustomerList. There billing and shipping info will automatically be filled in for you. If it is a new customer, you click a box labelled "New Customer"; in this case, you need to fill in the billing and shipping info. (I'll give you another box so that if they are the same you won't have to type it twice.)

There is a button you click when finished. It will move all of the info for that order to your "database".

Later, will need to come up with cash flow and profit statements. But, getting the database built is the first step. How does it sound so far?

OK ... I have added:
+ a button to add a new line to the order form
+ a column for Month of order to the database
+ a pivot table which will show summary of sales by month

Question: I think the order form is good for entering sales info to the database. How do we add cost and profit?

One thought is to expand the price list so that it shows your cost for each item. That way, we can calculate profit for each item sold. (That was in your original, but I have ignored it up to now.) Will need some further guidance from you on this part.

Am running into size constraint for uploading file to this forum.

hi MSP & Networkmom

looks like you got off to a good start, just one suggestion before you get way down the track as i feel it could be of help to the final file

would it not be a better option to establish dynamic variable names in as many places as possible to minimise the intervention needed as the workbook grows

eg define "Customer_Name" using the following

=OFFSET('CustomerList'!$A$2,0,0,COUNTA('CustomerList'!$A:$A),1)

ps. I would like to see a copy of this as it develops / grows as it could be of great use as a baseline for any number of different applications.

MSP one question, If there are anythings I would like to suggest / add, would you mind adding it to your workbook, otherwise we have the potential for many different versions.

Hi peejay,

I will share what I can. (Which up to now is pretty much everything because there are no actual customer names or business sensitive info in it yet.) Unfortunately, I put in a nice little splash screen, and the gif makes the file too large to upload. So, I'll need to rip that back out before posting.

On your suggestion for using dynamic ranges. I am of 2 minds on that subject. I use it myself and I am very comfortable with it. The downside of it is that the user can no longer use the Go To dialog to "trace" formulas that include Named Ranges.

In this case ... I decided to use a different approach. I believe it will keep the Names always current.
1. always start the data table in cell A1 (any cell would work, actually)
2. always use headers that will be unique
3. Use a worksheet_deactivate Event procedure, so you can edit the sheet as much as you like, but when you LEAVE the sheet, the procedure runs
4. the procedure takes the current region around cell A1 and does the VBA equivalent of Excel's Insert >> Name >> Create

It looks like this:

Please Login or Register  to view this content.

Same code, different comment, on the customer list sheet.

To me, that VBA approach is simple and effective. And still allows the user to use the GoTo dialog. I know that what I uploaded earlier did not have that code in it yet, so ... of course, the dynamic range suggestion is a big improvement over static named ranges.

I will strip out the flash screen and upload the workbook with dummy data later today. Would definitely like other suggestions you might have.

Also, if you see a flaw in my logic on the dynamic range vs. VBA approach, let me know. I am not married to the idea.

OK, here's the quick tour ...
When the workbook opens, you will see a Splash screen (image removed to make the file fit the upload limits). It will vanish in approximately 5 seconds on its own.

The main interaction will be with the EnterNewSales sheet.

When you click in the cell for Customer Name (C5), you will normally see an arrow indicating that a list of customers is available to choose (this is called "cell validation"). While this arrow is present, you cannot enter anything in this cell except what is available in the drop-down list. When you make a selection from the list, that customer's shipping & billing information will be populated from the information on the customer list sheet.

To enter a new customer, click on the check box for new customer. This removes the cell validation from C5, and also removes the look-up capability in the other customer information cells. For a new customer, you will need to enter all of the information manually. But, only once. When you have finished and click the button to enter the order into the database, data for any new customer will be copied to the CustomerList sheet, and will available for selection next time.

The order details form will have rows for a minimum of 3 items. To add more rows, click the down arrow to the left of the form. To reduce the number of rows, click the up arrow.

When you click in the cell for item description (C12 and below), you should always see an arrow indicating that a list of items is available to choose. If you need to enter an item that is not on the list, the form will allow it (you will see a warning message); but, there will be no price or cost information for this item. In this case you should immediately go to the Price List sheet and enter the information for this new item. When you return to the New Sales sheet, the item will now be available for selection from the list.

As soon as you select an item from the list, you should see the correct list price and shipping entered in columns D, and E, respectively. And, you should see that adjacent cells in columns F, G, and H have been highlighted in yellow. This is intended to remind you to enter a quantity ordered. As soon as you enter a quantity, the highlighting should disappear.

If any of these things do not happen, the form needs to be "reset". You can reset the form by clicking the "clear form" button. This not only removes old data, but it re-establishes all of the "logic" built into the sheet.

When finished entering the order information, click the button a the top right to copy the information to the Database. The workbook logic will check to be sure that a quantity has been entered for each item before any data is moved to the database.

If you have not entered a date in the cell for Order Date (C3), today's date will be used as the date for the order.

After copying the information to the database, you will find yourself looking at the Data sheet, with the window scrolled to the point where the data was added. Be sure to spot-check for any errors.

Each line item from the order form will appear as a separate row in the database. This is done so that, later on, you can analyze your sales by product or product line.

On the data sheet, columns A to K contain the order details. Columns L to V contain customer information. If these are hidden, click on the [+] button above column W to unhide. To hide them again, click on the [-] button above column W. Alternatively, to avoid scrolling, you can click on the [1] at the top left of the sheet to hide customer information or click on the [2], just below it, to show the customer information.

So far, there is only one summary report. It is a pivot table based on the data in the data sheet. When you access the sheet with the pivot table, the data are automatically updated. You can select to view one month or all months.

Comments? Suggestions?

Thanks MS, I will knock your file around a bit and give you some feedback.

your coding skills are somewhat more advanced than mine, and i did not think of coding the dynamic ranges, although i have done it before in previous codes i have written.

One comment on your code is that it will only trigger when the sheet de-activates and therefore by implication does the sheet have to be manually activated and maybe even displayed first?.

if my understanding is correct this is a classic way to slow excel down (am i correct?) and if you add new customers in your "EnterNewSales" sheet, you wont necessarily activate / de-activate the sheet (again i may need to be corrected here) so is it not possible to dynamically define the ranges using something like

Sheets("CustomerList").Activate
ActiveWorkbook.Names.Add Name:="Customer_Name", RefersTo:="=" & Sheets("CustomerList").Range(Range("a2"), Range("a1").End(xlDown)).Address
Sheets("EnterNewSales").Activate

this type of code routine could be used for any of the dynamic ranges at any time in the main code and then attached to the 2 buttons on the EnterNewSales sheet so everytime an order is entered or the form is cleared the ranges are updated and the user never needs to goto the sheets (obviously it can also be placed in the worksheet de-activate event just in case the user does manually go to the sheet and add / modify the names
(or am i just looking at it in a too simplistic way?)

Thanks for the feedback. Two good catches. I will fix both.

Hi MSP, having taken another look today, hope you dont think i am being picky, but i have also the following comments.......

(1) I have noticed the Sales Tax(or the tax exempt number) is missing on the EnterNewSales sheet.

Does this need to be added, then there could be a button to automatically print the invoice which i presume is issued with the despatch.

It would also prevent the need for the user to manually go into the data sheet and add tax exempt number or tax rate

(2) It may also be useful to have another checkbox for the ship address, so that if it is the same as the bill address the data is automatically populated therefore saving time and the potential for mistakes.

(3) Would it be better for the Clear Form button to take the list back down to 3 rows as the default start point(at the moment it just removes the contents of the items orders no matter how many were ordered)

Any comments yet from Networkmom????

Hi peejay, sorry I did not respond yesterday. I am juggling a couple of projects at the moment.

I appreciate all of your comments. I promise to upload the final product (without any of the user's data, of course) over the weekend.

Feedback from network mom so far:
+ the Event procedures that update the named ranges caused a VB error when she deleted all of the test data from one of the list sheets, then left the sheet before adding any real data ... I fixed that by telling VB to ignore this error. Your solution using dynamic ranges defined by OFFSET function would have obviated this error! So, maybe that is a learning.

+ the updating of the pivot table data range was not correct; it works fine when entering an order for an existing customer; but, when entering an order from a new customer the pivot table data range was wrong. This problem could have been fixed either by moving the the 2 lines of code that update the pivot table data range or using a different variable name for "next row" on the data sheet vs. the customer list. I chose the former.

+ ignore sales tax for now; not something the user needs right now; current tax laws in the US exempt most internet sales from sales tax

+ I had put in an auto-counter for numbering sales orders; user preferred to manually enter these, so changed code for that.

We had one other start-up glitch. I failed to send instructions for setting macro security to Medium. So, nothing worked at all at first.

I also put in your suggestions on formatting and making e-mail addresses hyperlinks.

I meant to put in the button you suggest for auto-copying if the ship-to and bill-to are same address (as they seem to be in nearly all cases). Just slipped my mind in the crush of business. Will add that.

Keep your suggestions coming.

Hi MSP,
thanks for the feedback.

not a suggestion this time but a question.

in the DATA sheet there is a column at the end called "Collected", i presume this is to record the invoice value has been paid????

If so it might be a useful idea to have a user form/code routine to allow the user to recall an invoice and mark it as paid.(simple check box)

I presume when the reports are done (and i also assume there will be quite a few of them), one of the reports will relate to "outstanding invoices" if Networkmom works with certain customers having a credit line. This information will also be required for Cash Flow / bank balance reports etc

i look forward to seeing the completed version over the weekend and will certainly provide you with feedback after i have "knocked it about" a bit

(Note for Networkmom - should you wish me too "knock around" the version that includes your customer data, please PM me and i will give you an email address - If you dont want that, its no problem, i understand the confidentiality issues)

peejay, regarding "collected" column ... the data sheet is a modified version of what we started with. In other words, I did not put the column in, nor did I take it out.

I am waiting for networkmom to tell me what to do with that column. Everything that has been done up to now assumes that this is a cash at purchase company. We have not taken the cost of credit into account at all ... yet. But, even if the only form of credit is accepting credit cards, there is a cost associated with it, and it has not yet been captured. So, I suspect we will come back to this at some point.

But, I do like your idea ... in general, if that column is going to be used at all (currently it is not), one would want to be able to "run a query" (which might be nothing more than an autofilter in this case) to display all records for which payment has not been received.

Extending the thought a little bit ... if some customers pay by mailing a check, and assuming that the business holds shipment until the check is received and cleared, then there needs to be other "status" columns to be able to see "orders in flow".

MSP, thanks for your comments.

Two things come to mind........

+ I also think that there will be a need to re-visit this area later, and also can forsee some other possibilities, such as outstanding invoices (as mentioned), order status (as you suggest), stock position and as the original workbook has CASH IN-OUT there will be a need for recording expenses, calculation of cash flow and bankbalance (or have you already done something on this?).

+ One other area I do not see in the original workbook is the facility to take into account any RETURNED products (i know no business wants them but they all have them for various reasons) so maybe it would be an idea to add the facility for a "negative" value order, just in case there needs to be a refund given for goods returned.

Hope I am not adding to your burden, just thinking in various directions.......

This version has an expense entry form that more-or-less mimics the order entry form. And a vendor list that mimics the customer list. On the vendor list is a facility for adding monthly recurring expenses ... each time the workbook is opened or the vendor list sheet is accessed, a routine runs that checks to see if any items in the recurring list need to be moved to the data sheet.


My GUESS is that networkmom will want to work on cash flow next. But, that is just a guess.

Hello,

Great community where people of your skill level are willing to help out. Would you guys be interested in another project when this one is completed? if not I understand how there is only so much time. Very nice work on this one.

Mark

Hi Mkmed

this is a help forum where all are welcome to post their questions and problems.

I have also found it to be full of extremely helpful people, and as a result have learned a lot myself.

why not just post your problem / project I have no doubt you will get reply's

Hi mkmed, I agree with peejay on both counts. A new thread tends to create wider interest at first.

Hi peejay, your last edit really put your finger on the biggest problem with the last version. Namely, the recurring expense logic is not tied at all to the expense form. It is more like an after-thought. OK, so it really was an after-thought! It needs to be integrated. Networkbookmom told me not to bother. But, it feels incomplete as is. I am a little bit swamped with work this week. (You might have noticed I have been invisible in the forum for a few days.) Will fix this as soon as I have time.

Can the Summary sheet have an addition so that the Sales and Expenses are easily seen, just to make it easier to read. (expenses highlighted in light yellow for example)

Sounds like a good idea. I have never added conditional formatting to a Pivot Table. Is it just like normal conditional formatting, or is there a trick to it?

As for the other ideas ... all good. It comes down to the same point in my mind. Which is ... need to start working on a cash flow analysis. I have not got my head wrapped around doing that yet. I might have to break down and read a few chapters from one of my old financial accounting books.

Hi MSP

I have never tried conditional formatting on pivot tables in VB, but in "normal" mode the conditional format works the same as in a normal cell, so column G can be conditionally formatted "if Cell value < 0" (not sure how you would apply that to a whole row due to the way you are putting your data together, so maybe just formatting this colmn is enough), however......."thinking out of the box", you could have another column with the words "Sale" or "Expense" but this would have to be as a text value rather than the GETPIVOTDATA formula that comes up when the cells in the table are selected, format the cells as white on white so it cant be seen and then conditionally format based on that cell
(I really cant say i have tried this before either!!!)

I did think you must be busy right now, it gets a bit hectic at times.......
Networkmom's comments (recently added) are more todo with order / payment tracking, i guess it would be easier to resolve all these "functional issues" first then do the cash flow at the end,

Note for networkmom, the Data sheet does show you the order details inc the customer (see column M), the outstanding payments would be highlighted in "collected" column, so for eg if it says "yes" the payment has been recieved and the order can be shipped...or are we talking about a credit line here where goods are shipped prior to payment?

hi MSP

I been trying various ways to get this to work, and have not suceeded.
i think its just because of the fact that the data is in a pivot, so i got a workaround as follows

in cell AA6 type =G6
(note - type it DONT select the cell G6 otherwise you get the =pivot tble formula and it doesn't work)

formula fill down as far as you like

then in the pivot table set up a standard conditional format using a formula as follows
formula is ...... =$aa6<0
select the colour.

paint it accross the pivot table
paint it down the pivot table (in fact pant it down to row 1000 for when the pivot table expands)

the number formats dissappear till you exit and re-enter the sheet then it works ok.

(its a simplistic approach but it works)

------------------------------------------------------------
from networkmom's reply seems like the file needs to cater for credit lines as well, so maybe another report is required for ourtstanding invoices (and expenses)

in previous files i have worked, current cash-flow is not a major problem, its just a case of opening bank balance + sales reciepts - expenses. it only really becomes more complicated when your trying to predict forward based on sales forecasts, estimated expenses etc etc, so i think for this project the "current" cash position would be adequate, perhaps networkmom should confirm this point.