IF?ISNA?MATCH? Function?
 

I am trying to enter into one column an amount for a deposit and have several
other columns populate with a breakdown of that deposit. I've used a similar
function to enter in a check number and have the formula auto populate the
date/amount/payee of the check (naturally at a computer I can't access) using
some variation of a IF(ISNA(MATCH.... function, now I need something similar.

Here is a sample of what I am working with:
A B C D
1 Sales Sales Tax Deposit
2 1/1/08 29.50 1.43 30.93
3 1/2/08 300.00 8.25 308.25
....

This table is being pulled from another sheet with all of the transactions
and totalling it for the day using the date on the left. The goal for this
formula is to be able to go down the bank statement entering only the deposit
(using here the deposit for the 2nd). Let's say the deposit cleared the bank
on the 5th, although the credit card company shows it as the 2nd. I would
like to be able to enter:

A B C D
1/5/08 308.25

And have the formula return:

A B C D
1/5/08 300.00 8.25 308.25

Of course I need to do this for hundreds of deposits within a given month,
making a formula much more desirable.

Thanks
handya

IF?ISNA?MATCH? Function?
 

So, you want B1 and C1 to contain a formula? In the example you quote:

B1: =D1/1.0275
C1: =D1-B1

However, your other example seems to have a different tax rate (4.8%),
so you would need to specify this if you want a more universal
solution.

Hope this helps.

Pete

On Oct 2, 5:16*pm, handyaccountant
wrote:
I am trying to enter into one column an amount for a deposit and have several
other columns populate with a breakdown of that deposit. *I've used a similar
function to enter in a check number and have the formula auto populate the
date/amount/payee of the check (naturally at a computer I can't access) using
some variation of a IF(ISNA(MATCH.... function, now I need something similar.

Here is a sample of what I am working with:
* * * * * * * * A * * * * * * B * * * * * * *C * * * * * * * * * * D
1 * * * * * * * * * * * * * * *Sales * * * *Sales Tax * * * * Deposit
2 * * * * * * * 1/1/08 * * *29.50 * * * *1.43 * * * * * * * * 30.93
3 * * * * * * * 1/2/08 * * *300.00 * * *8.25 * * * * * * * * 308.25
...

This table is being pulled from another sheet with all of the transactions
and totalling it for the day using the date on the left. *The goal for this
formula is to be able to go down the bank statement entering only the deposit
(using here the deposit for the 2nd). *Let's say the deposit cleared the bank
on the 5th, although the credit card company shows it as the 2nd. I would
like to be able to enter:

A * * * * * *B * * * * * * * C * * * * * * * * * * D
1/5/08 * * * * * * * * * * * * * * * * * * * * * *308.25

And have the formula return:

A * * * * * *B * * * * * * * C * * * * * * * * * * D
1/5/08 * *300.00 * * * 8.25 * * * * * * * * 308..25

Of course I need to do this for hundreds of deposits within a given month,
making a formula much more desirable.

Thanks
handya

IF?ISNA?MATCH? Function?
 

I guess I didn't clarify well enough...

Further down in the worksheet i want to be able to enter a formula so that
the formula looks up the deposit value (308.25) assuming that is the only
deposit in the amount of $308.25 and then autopopulates the sales amount and
the sales tax in columns b and c.

"Pete_UK" wrote:

So, you want B1 and C1 to contain a formula? In the example you quote:

B1: =D1/1.0275
C1: =D1-B1

However, your other example seems to have a different tax rate (4.8%),
so you would need to specify this if you want a more universal
solution.

Hope this helps.

Pete

On Oct 2, 5:16 pm, handyaccountant
wrote:
I am trying to enter into one column an amount for a deposit and have several
other columns populate with a breakdown of that deposit. I've used a similar
function to enter in a check number and have the formula auto populate the
date/amount/payee of the check (naturally at a computer I can't access) using
some variation of a IF(ISNA(MATCH.... function, now I need something similar.

Here is a sample of what I am working with:
A B C D
1 Sales Sales Tax Deposit
2 1/1/08 29.50 1.43 30.93
3 1/2/08 300.00 8.25 308.25
...

This table is being pulled from another sheet with all of the transactions
and totalling it for the day using the date on the left. The goal for this
formula is to be able to go down the bank statement entering only the deposit
(using here the deposit for the 2nd). Let's say the deposit cleared the bank
on the 5th, although the credit card company shows it as the 2nd. I would
like to be able to enter:

A B C D
1/5/08 308.25

And have the formula return:

A B C D
1/5/08 300.00 8.25 308..25

Of course I need to do this for hundreds of deposits within a given month,
making a formula much more desirable.

Thanks
handya