I do invoicing for a large trucking company.
Each invoice contains hundreds of ticket numbers that
can belong to any of 40 trucks.
In an attempt to avoid errors, I have created a checking sheet, where
each truck has all of its tickets entered, complete with total $
and ticket #.
I am trying to create a formula that will search through my invoices
and verify that the combination of ticket & truck match up
to the totals that are displayed on both invoice and check sheet.
I have created formulas that tell me if a complete invoice does
not balance, but I am looking for a formula that will actually
show me WHICH ticket does not balance.

I think information about your worksheet layout may be necessary. However,
have you tried conditional formatting? See:
http://www.officearticles.com/excel/...ft_ excel.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Arla" wrote in message
...
I do invoicing for a large trucking company.
Each invoice contains hundreds of ticket numbers that
can belong to any of 40 trucks.
In an attempt to avoid errors, I have created a checking sheet, where
each truck has all of its tickets entered, complete with total $
and ticket #.
I am trying to create a formula that will search through my invoices
and verify that the combination of ticket & truck match up
to the totals that are displayed on both invoice and check sheet.
I have created formulas that tell me if a complete invoice does
not balance, but I am looking for a formula that will actually
show me WHICH ticket does not balance.

I understand that I could use the conditional formatting to show which
numbers are not equal to one another; but first I need a formula that
validates that the Ticket # and Truck # on the invoice match up on the check
sheet, and I can compare the $ to see if those match.

My check sheet is set up as a series of tabs - one tab for each truck, and I
counted this morning and there is almost 50 trucks. In the check sheet, I
have the date, hours, ticket #, invoice, and then total $ for each truck.
There is probably 100 tickets for each truck. So, as you can see I am
checking 5,000 entries. Right now, I have a sumif formula that looks up the
ticket # in the invoices, which are linked to this sheet. The formula
returns the sum of the ticket # in the invoice. I then use a True False
formula to compare the two values. However, this is not help me when a
person enters a wrong unit # ticket # combination. The formula searches the
ticket # and doesn't care what unit # was assigned to it, and it DOES matter.

Thanks for any help anyone can provide. This is a weekly nightmare for me.

"Anne Troy" wrote:

I think information about your worksheet layout may be necessary. However,
have you tried conditional formatting? See:
http://www.officearticles.com/excel/...ft_ excel.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Arla" wrote in message
...
I do invoicing for a large trucking company.
Each invoice contains hundreds of ticket numbers that
can belong to any of 40 trucks.
In an attempt to avoid errors, I have created a checking sheet, where
each truck has all of its tickets entered, complete with total $
and ticket #.
I am trying to create a formula that will search through my invoices
and verify that the combination of ticket & truck match up
to the totals that are displayed on both invoice and check sheet.
I have created formulas that tell me if a complete invoice does
not balance, but I am looking for a formula that will actually
show me WHICH ticket does not balance.

So is what I am really trying to do, is check 3 sets of values??? I am
confusing myself now. Someone take me back to my sanity!

"Arla" wrote:

I understand that I could use the conditional formatting to show which
numbers are not equal to one another; but first I need a formula that
validates that the Ticket # and Truck # on the invoice match up on the check
sheet, and I can compare the $ to see if those match.

My check sheet is set up as a series of tabs - one tab for each truck, and I
counted this morning and there is almost 50 trucks. In the check sheet, I
have the date, hours, ticket #, invoice, and then total $ for each truck.
There is probably 100 tickets for each truck. So, as you can see I am
checking 5,000 entries. Right now, I have a sumif formula that looks up the
ticket # in the invoices, which are linked to this sheet. The formula
returns the sum of the ticket # in the invoice. I then use a True False
formula to compare the two values. However, this is not help me when a
person enters a wrong unit # ticket # combination. The formula searches the
ticket # and doesn't care what unit # was assigned to it, and it DOES matter.

Thanks for any help anyone can provide. This is a weekly nightmare for me.

"Anne Troy" wrote:

I think information about your worksheet layout may be necessary. However,
have you tried conditional formatting? See:
http://www.officearticles.com/excel/...ft_ excel.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Arla" wrote in message
...
I do invoicing for a large trucking company.
Each invoice contains hundreds of ticket numbers that
can belong to any of 40 trucks.
In an attempt to avoid errors, I have created a checking sheet, where
each truck has all of its tickets entered, complete with total $
and ticket #.
I am trying to create a formula that will search through my invoices
and verify that the combination of ticket & truck match up
to the totals that are displayed on both invoice and check sheet.
I have created formulas that tell me if a complete invoice does
not balance, but I am looking for a formula that will actually
show me WHICH ticket does not balance.

Hi, Arla. I want to help but it seems pretty detailed. Can you send me the
file or upload it somewhere? If sending try my first name at the web address
below as an email address.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Arla" wrote in message
...
So is what I am really trying to do, is check 3 sets of values??? I am
confusing myself now. Someone take me back to my sanity!

"Arla" wrote:

I understand that I could use the conditional formatting to show which
numbers are not equal to one another; but first I need a formula that
validates that the Ticket # and Truck # on the invoice match up on the
check
sheet, and I can compare the $ to see if those match.

My check sheet is set up as a series of tabs - one tab for each truck,
and I
counted this morning and there is almost 50 trucks. In the check sheet,
I
have the date, hours, ticket #, invoice, and then total $ for each truck.
There is probably 100 tickets for each truck. So, as you can see I am
checking 5,000 entries. Right now, I have a sumif formula that looks up
the
ticket # in the invoices, which are linked to this sheet. The formula
returns the sum of the ticket # in the invoice. I then use a True False
formula to compare the two values. However, this is not help me when a
person enters a wrong unit # ticket # combination. The formula searches
the
ticket # and doesn't care what unit # was assigned to it, and it DOES
matter.

Thanks for any help anyone can provide. This is a weekly nightmare for
me.

"Anne Troy" wrote:

I think information about your worksheet layout may be necessary.
However,
have you tried conditional formatting? See:
http://www.officearticles.com/excel/...ft_ excel.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Arla" wrote in message
...
I do invoicing for a large trucking company.
Each invoice contains hundreds of ticket numbers that
can belong to any of 40 trucks.
In an attempt to avoid errors, I have created a checking sheet, where
each truck has all of its tickets entered, complete with total $
and ticket #.
I am trying to create a formula that will search through my invoices
and verify that the combination of ticket & truck match up
to the totals that are displayed on both invoice and check sheet.
I have created formulas that tell me if a complete invoice does
not balance, but I am looking for a formula that will actually
show me WHICH ticket does not balance.

Anne,
Thank you very much for your help.
I am going to enter all of my data in one spreadsheet rather than over
multiple sheets. Using a filter, like you suggested, I should be able to at
least simplify what data I already have. I really appreciate a different
view of the problem.

Arla

"Anne Troy" wrote:

Hi, Arla. I want to help but it seems pretty detailed. Can you send me the
file or upload it somewhere? If sending try my first name at the web address
below as an email address.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Arla" wrote in message
...
So is what I am really trying to do, is check 3 sets of values??? I am
confusing myself now. Someone take me back to my sanity!

"Arla" wrote:

I understand that I could use the conditional formatting to show which
numbers are not equal to one another; but first I need a formula that
validates that the Ticket # and Truck # on the invoice match up on the
check
sheet, and I can compare the $ to see if those match.

My check sheet is set up as a series of tabs - one tab for each truck,
and I
counted this morning and there is almost 50 trucks. In the check sheet,
I
have the date, hours, ticket #, invoice, and then total $ for each truck.
There is probably 100 tickets for each truck. So, as you can see I am
checking 5,000 entries. Right now, I have a sumif formula that looks up
the
ticket # in the invoices, which are linked to this sheet. The formula
returns the sum of the ticket # in the invoice. I then use a True False
formula to compare the two values. However, this is not help me when a
person enters a wrong unit # ticket # combination. The formula searches
the
ticket # and doesn't care what unit # was assigned to it, and it DOES
matter.

Thanks for any help anyone can provide. This is a weekly nightmare for
me.

"Anne Troy" wrote:

I think information about your worksheet layout may be necessary.
However,
have you tried conditional formatting? See:
http://www.officearticles.com/excel/...ft_ excel.htm

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"Arla" wrote in message
...
I do invoicing for a large trucking company.
Each invoice contains hundreds of ticket numbers that
can belong to any of 40 trucks.
In an attempt to avoid errors, I have created a checking sheet, where
each truck has all of its tickets entered, complete with total $
and ticket #.
I am trying to create a formula that will search through my invoices
and verify that the combination of ticket & truck match up
to the totals that are displayed on both invoice and check sheet.
I have created formulas that tell me if a complete invoice does
not balance, but I am looking for a formula that will actually
show me WHICH ticket does not balance.