Prev: Inserting Lines Makes Formula Inactive
Next: Deleting worksheets in a macro
From: Serendipity on 28 Jan 2010 18:59
Sigh.... Can only get it to work halfway. I'm thinking I've had to much
caffeine and overlooking something. Or my very basic skills just are not up
to the task. LOL Sometimes a little knowlege sometimes gets dangerous!

What I have and need:
*2 worksheets:BILLS(think checkbook register), RAP (think bank statement)
*if column L on RAP matches column W on BILLS return value in column P on
BILLS even if it does not match. Sometimes things are slightly off due to
taxes.
*I then use this number to subtract from column O on RAP to show differences
froms the logged bills vs the RAP so I can filter down to the ones that don't
balance and research why. I'm balancing a high volume of invoices and looking
for a solution without having to go invoice by invoice for 3000+ invoices
every month.

What I tried that somewhat works:
=IF(ISERROR(VLOOKUP($L2,Bills!P$2:$W450,1,FALSE)),0,VLOOKUP($L2,Bills!P$2:$W450,1,FALSE))

=VLOOKUP($O4,Bills!P$2:$W452,1,FALSE)

my sumif and sumproduct just went to #value.

Thanks,
Kenirae

Problem:
=IF only returns the value in column P of BILLS if column O on RAP matches
exactly. I want it to post the amount if the L & W matches if if there is no
$$ logged in P.

=Vlookup only returns the value in column P of BILLS if column O on RAP
matches exactly. I want it to post the amount if the L & W matches if if
there is no $$ logged in P. Right now it returns #n/a if no exact match.


From: Don Guillett on 28 Jan 2010 19:37
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Serendipity" <Serendipity(a)discussions.microsoft.com> wrote in message
news:1F44E3F7-B3C8-4B8E-B646-8B8E1AE9C2A7(a)microsoft.com...
> Sigh.... Can only get it to work halfway. I'm thinking I've had to much
> caffeine and overlooking something. Or my very basic skills just are not
> up
> to the task. LOL Sometimes a little knowlege sometimes gets dangerous!
>
> What I have and need:
> *2 worksheets:BILLS(think checkbook register), RAP (think bank statement)
> *if column L on RAP matches column W on BILLS return value in column P on
> BILLS even if it does not match. Sometimes things are slightly off due to
> taxes.
> *I then use this number to subtract from column O on RAP to show
> differences
> froms the logged bills vs the RAP so I can filter down to the ones that
> don't
> balance and research why. I'm balancing a high volume of invoices and
> looking
> for a solution without having to go invoice by invoice for 3000+ invoices
> every month.
>
> What I tried that somewhat works:
> =IF(ISERROR(VLOOKUP($L2,Bills!P$2:$W450,1,FALSE)),0,VLOOKUP($L2,Bills!P$2:$W450,1,FALSE))
>
> =VLOOKUP($O4,Bills!P$2:$W452,1,FALSE)
>
> my sumif and sumproduct just went to #value.
>
> Thanks,
> Kenirae
>
> Problem:
> =IF only returns the value in column P of BILLS if column O on RAP matches
> exactly. I want it to post the amount if the L & W matches if if there is
> no
> $$ logged in P.
>
> =Vlookup only returns the value in column P of BILLS if column O on RAP
> matches exactly. I want it to post the amount if the L & W matches if if
> there is no $$ logged in P. Right now it returns #n/a if no exact match.
>
>

 | 
Pages: 1
Prev: Inserting Lines Makes Formula Inactive
Next: Deleting worksheets in a macro