Web Development

Ask something
Discussion Locked

Spreadsheet for sales receipts.

0 helpful
11 replies

by ffontan1

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am making a spreadsheet for sales receipts.
1.)? I want to be able to type in receipts at random by date.? I want to be able before saving the sheet have the item numbers remain the same which is in a column but I want the "Dates" which are in rows going across with the?information to arrange themselves in "Date Order.
2.)? I want to take any receipt and break it up by categories.? (For example food, electronics, tools, garden etc.)? Then have the amount in the category like food I want it to total it all and at the same time calculate the total tax in another column.? So when I type Food, amount and tax it will calculate total in another column.
3.)? The column that will be calculating tax will need to be able to do three (3) different tax rates.? For example T1 it will calculate $4.00 at a 7.25% tax rate and place it in a third column as a total amount T2 would be 8.0% and T3 would be 8.25% tax rate.?
?
?So when I am finish it should show for the whole year the total of food, total tax for food and for the the other categories like electronics, tools, garden etc.
?
4.)? Some when I am typing I forget to type in upper case.? Is there a way of highlighting and have the lower case change to Upper Case without having to retype?

11 replies

by dklippert

1) Use Data>Sort. Click on the Options button and select S"ort left to right"

2) Use a SumIf formula
=SUMIF(RangeWithCategory, "Category",RangeToSummerize)

3) After creating the formulas in 2) if you want the sum with tax, multiply by, for instance, 1.825.

4)Assign this macro from Chip Pearson to a toolbar button.

Sub SwitchCase()
Static N As Long
With ActiveCell
.Value = StrConv(.Text, N + 1)
End With
N = (N + 1) Mod 3
End Sub

by sgt_shultz

i am not a spreadsheet jockey but here are my thoughts. i know you are looking for actual formulas so forgive me for not giving them...
check out pivot tables. the are easy to set up. hightlight the data, pick Data/Pivot Table. play with it to see what it does. a set of pivot tables might do what you want and far as sorting and totaling the data, once all your columns and formula's are in place. as far as the taxes go, i sorta picture a column for the tax rate code (t1, t2 t3)? or are all three used for each transaction. if the latter, i think i would have three columns, one for each rate. if the former, i would set up a mini table with tax code and the rate then i would use a VLookup formula in my main spreadsheet that looks up the rate depending on the tax code entered. are you comfortable at all with formulas.
in Word you can highlight your all cap'ed text and then cycle thru re-formatting it as: all caps, first word capped, all smalls, by hitting shift + F3 on the keyboard. tryit. in Excel, you'd have to do it with a formula like this =LOWER(A1:D33). hope it gives you are starting point.
last 2 cents worth: if it were me, i would use Access for your project instead of Excel...

by sgt_shultz

i am not a spreadsheet jockey but here are my thoughts. i know you are looking for actual formulas so forgive me for not giving them...
check out pivot tables. the are easy to set up. hightlight the data, pick Data/Pivot Table. play with it to see what it does. a set of pivot tables might do what you want and far as sorting and totaling the data, once all your columns and formula's are in place. as far as the taxes go, i sorta picture a column for the tax rate code (t1, t2 t3)? or are all three used for each transaction. if the latter, i think i would have three columns, one for each rate. if the former, i would set up a mini table with tax code and the rate then i would use a VLookup formula in my main spreadsheet that looks up the rate depending on the tax code entered. are you comfortable at all with formulas.
in Word you can highlight your all cap'ed text and then cycle thru re-formatting it as: all caps, first word capped, all smalls, by hitting shift + F3 on the keyboard. tryit. in Excel, you'd have to do it with a formula like this =LOWER(A1:D33). hope it gives you are starting point.
last 2 cents worth: if it were me, i would use Access for your project instead of Excel...

by ffontan1

TO ANSWES 2, 3 last 2 cents worth: if it were me, i would use Access for your project instead of Excel... CAN YOU GET ME A SAMPLE WARE GO SEE. LOOKING TEMPLATES OR PROGRAMS

Do you no were I can find Access customizable templates or programs to be
help me create customized for sales receipts and ledger systems to be accessible for free.?

Some sampl bookkeeping that I can easily Edit, looking for sampl to customize them to
meet my particular needs. inventory, invoice, mileage, utilities, rebate, controls templates.

DO NOT NEED NO QUICKBOOS, MONEY, PEACHTREE ACC.

by sgt_shultz

check out the northwind sample database that either ships with access or is downloadable from ms.
i'm outta here, stingy.

This discussion is closed to new replies.