Reply
Thu 27 Mar, 2008 08:03 am
Okay, so imagine a QuickBooks database (Huge), with like 2000 customers, 500 items for sale, and roughly a dozen different price levels (determined by previous year's sales, manually calculated for each customer).
I about fell out of my chair laughing at the utter waste of time spent under the current system. For each item quoted; a Sales rep;
First looks in QuickBooks to see what price level the Company is at.
Next the catalog to see the published price.
Next uses a calculator to first cut the catalog price in half, then apply the price level change, specific to that company
then types it into an estimate.
Then repeats for each item of interest
It seems obvious to me that QuickBooks should be a robust enough program to make these calculations automatically. Needless to say; this is a tremendous waste of time.
The first thing I did was build an Excel calculator, so that when they type the catalog price; they can then just look at the Price Level to see the actual quote next to it.
Next; I started entering the Catalog prices into QuickBooks, where most of the products already existed; absent prices or correct prices. I finished the first chapter, so now I want to test it out.
I believe the next step is to set up the price levels. Where previously, the price levels were simply labels for our use, I assigned each a function (Decrease item prices by X-percentage.) Now I'm stuck.
Problems:
1. For starters; the price level doesn't seem to affect the "Create Estimates" page at all.
The effect on the "Sales Order" and presumably "invoice" pages is to offer a drop down box (with the price level options) after each item is entered in the "Rate" field.
2. If I could get it to do that in the "Create Estimates" page as well; that would be a lot better... but ideally I'd like to see the program apply the customer sensitive discounts automatically.
Note. Each Customer has already been assigned a "Price Level" in Customer Detail Center->Edit->Additional Info... so it seems to me it should already be working... but it's not.
Note. Under Edit->Preferences->Sales & Customers->Company Preferences; the "Use Price levels" tab has already been checked.
Any and all help will be greatly appreciated!
Update. A directly entered Sales Order DOES reflect the "Price Level" (Yay!) Now I just need to know how to make the "Create Estimates" page do so as well. Anybody?
Answer: Get QuickBooks 2007 or later. What QuickBooks claims is an upgrade; I consider a belated patch for an obvious glitch in their previous versions. Why would anyone want their sales pages to reflect price levels but not their estimates? (They wouldn't.)
Next Question. Should be an easy one for you smart people:
How do I compare two sheets and get Excel to tell me which records are unique to only one sheet? I know how to add the two and then reduce it to unique records only, but that's not what I'm after. I want to see only the records that don't already exist in both; so I can scrutinize them further before adding them to a database. Any help would be greatly appreciated!
O'Bill ... I know spit about QB. Happily, it seems you are able to answer your own questions thus far. Keep up the good work!
Thanks for the bump, Tico. Sucks figuring this sh!t out from scratch.
Smart people: How close am I?
Argument #1:
=IF(D5=(B2:B4000),1,0)
This seems to work on the first record, but validation doesn't work down the list.
Argument #2:
=OR(D5=(B2:B4000)
Again, this works on the first record (delivering TRUE or FALSE) but validation doesn't work down the list.
Note:
I've cut the two databases down to name and number for formula testing. B and D represent the two long lists of phone numbers. The above formual is written in E5 and is supposed to check the number in D5 against every number from B2 to B4000 and return a "1" if it's there, or a "0" if it's not. This way I'd know to scrutinize all of the "0"s before adding them to the database... and wouldn't be adding any duplicates (which are a pain in the ass in QuickBooks).
I'd also like to know how to validate the "D" column, without also validating the "B" column. (Thus far I've cheated by copying the A and B columns and pasting duplicates immediately below it, so when it scrolls down thru 4000 rows it will catch regardless, since there's really only about 2000 records).
Please help!
If anyone bumps into this looking for an answer, the correct argument goes like this:
=IF(COUNTIF($A$2:$A$65536,A2)=1,"UNIQUE","MULTIPLE")
It seems those little "$" signs prevent validation from altering that part of the code... while the "A2" does validate for each successive row to be checked for doubles against every other record in column A.
Stumble It!
Tweet This
Bookmark on Delicious
Share on Facebook
Share on MySpace