vlookup formula (need to troubleshoot)

why are you using the choose formula?
when you just referring to the same specific cell?

from what i gather from your spreadsheet you are trying to get a list of items with the two codes in your legend
ie in sheet E

either E or I
however the way you are trying to control it is by manually entering which row you want to include

i propose a smarter solution by using a helper column with a cse (Ctrl+Shift+enter) formula

Formula:

Please Login or Register  to view this content.

i have put this into column I

this formula will only give you the row number of rows that have E or I

ps i only did sheet E17 and T13
i did not do the other two

you need to extend the formula to match how many rows you have in your accounts sheet
i have put comments on this revision to show you want i mean

also linked % to cell

have fixed your carwash balance as well

so really any sheet can be changed by changing the two orange cells

my suggestion
keep conditional format on the column for duplicates to highlight if duplicates are entered
this is much easier to control/manage then having to do something which physically stops users from entering same receipt number

http://office.microsoft.com/en-au/ex...102809491.aspx

basically highlight column B
click on conditional format>duplicate values
choose what colour/etc you want show and bobs your uncle

not a problem

If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Cheers
Hum

I have one more question for you. I realized that there is a problem with the carwash section. Everything works great currently, but we have no way to “use” the money from the carwashes on our sheet breakdown.

Example: carwash for category e = $500 – this should slot into the current automated details on tab “E 17%” as follows:

Details Receipt Income Expense Balance
Carwash c1 500 0 +500 to existing

So, it could look like: (as attached)

I hope this makes sense… so the carwash would be entered into “Account Details” as it currently is, but the formatting needs to change so that it slots into the appropriate tab based on the “category” (column i) it is entered under in “Account Details”.

Q:
why is carwash seperated like it is?

i thought carwash was completely seperated due to the way your spreadsheet is laidout

also is this a new issue as your original file did not have way of working for carwash

can you not just insert your first line to be carwash balance?
there is no time stamping so a single line will take care of your total

formula in g5 need to be extended

Formula:

Please Login or Register  to view this content.

was unsure how much area you required so only put 7
try keep as small as possible as to keep performance of sheet good

i have revised your sheet slightly to use named ranges to make it easier to update
Carwash ='Account Details'!$I$1:$I$8
Carwash_Income = ='Account Details'!$J$1:$J$8

started doing your helper column
run out of time
you will have to figure it out yourself