R
rwfrench
Hi, I work at a travel agency and I reconcile a corporate credit card that
might list a total charge of $800 by a hotel but in reality we'll have 2
reservations at that hotel for $400 each so the hotel charges $800 and the
credit card statement shows $800 but I reconcile the credit card statement
against the database report which shows 2 payments of $400. I'd like to
build a formula that I can put into conditional formatting that would look
for different cells in column k that add up to a specific total, in this case
$800. The thing is, I'd need the formula to look for up to any given 10
cells that add up to the specific total. It might only be 2 listings that
add up to $800 but it also might be up to 10 listings that add up to $800.
This is a herculian effort to do manually because you may have 3 at $200
each, 1 at $300 and then they apply a credit or refund of -$100 to the $300
room from the $8000 in refunds they owe us, but I have to combine the credit
card report and the refunds due from supplier report in order to account for
all possibilities using credits. We book at hotels all over the world so I
have daily currency rates to deal with, they charge the wrong amounts, their
DBA name is different from the supplier name so while the database report
might show one name the credit card statement will show a different name.
Sometimes they charge the card a month after the guest stayed, and sometimes
our people make mistakes and fax the credit card info to a hotel that is set
up for direct billing so we pay them on the card and send them a check. If
any of you gurus know a formula to help me add the cells for different
combinations to get a specific total I'll send you avirtual case of beer, but
if you know of a formula that would add the cells to give all combinations
that would be be plus/minus 5% of the total since they sometimes charge more
or less I'll send you a virtual keg! Thanks!
might list a total charge of $800 by a hotel but in reality we'll have 2
reservations at that hotel for $400 each so the hotel charges $800 and the
credit card statement shows $800 but I reconcile the credit card statement
against the database report which shows 2 payments of $400. I'd like to
build a formula that I can put into conditional formatting that would look
for different cells in column k that add up to a specific total, in this case
$800. The thing is, I'd need the formula to look for up to any given 10
cells that add up to the specific total. It might only be 2 listings that
add up to $800 but it also might be up to 10 listings that add up to $800.
This is a herculian effort to do manually because you may have 3 at $200
each, 1 at $300 and then they apply a credit or refund of -$100 to the $300
room from the $8000 in refunds they owe us, but I have to combine the credit
card report and the refunds due from supplier report in order to account for
all possibilities using credits. We book at hotels all over the world so I
have daily currency rates to deal with, they charge the wrong amounts, their
DBA name is different from the supplier name so while the database report
might show one name the credit card statement will show a different name.
Sometimes they charge the card a month after the guest stayed, and sometimes
our people make mistakes and fax the credit card info to a hotel that is set
up for direct billing so we pay them on the card and send them a check. If
any of you gurus know a formula to help me add the cells for different
combinations to get a specific total I'll send you avirtual case of beer, but
if you know of a formula that would add the cells to give all combinations
that would be be plus/minus 5% of the total since they sometimes charge more
or less I'll send you a virtual keg! Thanks!