Formula Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kat - 29 Sep 2006 15:58 GMT

I have an excel sheet that has almost 4000 data rows. I need to compare the
old sheet to the new sheet and if the part number is equal, I need it to
show me the discount from the old sheet in a column in the new sheet.

Here is the formula I came up with: =LOOKUP(A4,old!A4:A4000,old!H4:H4000)
This compares the A column in the new sheet with the A column in the old
sheet and then will report the discount from the H column into the column
the formula is written. If I hand type the formula in ever cell changing
the row number for the look up cell it works fine. However, when I try to
drag the formula to all 3996 cells, all numbers in the formula add one and
then the formula doesn't work.

How can I enter this formula so that just the beginning cell number changes
but not the rest? Or is there a better formula?

What might I be doing wrong?

Kathy in WI

Reply to this Message

Dave Peterson - 29 Sep 2006 16:16 GMT

Maybe you want:

=LOOKUP(A4,old!$A$4:$A$4000,old!$H$4:$H$4000)

> I have an excel sheet that has almost 4000 data rows. I need to compare the
> old sheet to the new sheet and if the part number is equal, I need it to
[quoted text clipped - 14 lines]
>
> Kathy in WI

Signature

Dave Peterson

Reply to this Message

Kat - 29 Sep 2006 17:19 GMT

Thank you that did the trick. My next problem... =)

I now have the discount amount on the new sheet, however some of the new
prices don't actually reflect a price it has a code in the cell which means
call the factory for price. So when I multiply the new price with the
discount, some field reflect an error that reads "#VALUE!" is there anyway
to include in the formula "=G6*H6" that if a dollar amount is not reached
the the cell remains blank instead of the #VALUE! error?

Thanks!

Kathy

> Maybe you want:
>
[quoted text clipped - 22 lines]
>>
>> Kathy in WI

Reply to this Message

glenton - 29 Sep 2006 19:03 GMT

You could try

=IF(ISERROR(G6*H6),"",G6*H6)

Signature

Glenton
www.leviqqio.com
Quality financial modelling

> Thank you that did the trick. My next problem... =)
>
[quoted text clipped - 35 lines]
> >>
> >> Kathy in WI

Reply to this Message

Rate this thread: