what function to use?

T

Tiffani

Here is my scenario, I have in column D either a C or an E. In column C, it
needs to reference column D so the E=1 or C=100. Can anyone shed some light
as to how I can do this without coping the cells because its for a price
change and there are like 7000 rows. Please help!!!
 
D

Duke Carey

=if(d1="E",1,if(D1="C",100,"Neither"))

If every single cell that you'll reference in col D has either an E or a C,
use

=IF(D1="E",1,100)
 
R

Roger Govier

Hi Tiffani

Could you give a little more clarification?I'm not understanding exactly
what you are after.
It's not that you want
=IF(D1="C",100,IF(D1="E",1,""))
is it?
 
R

random1970

Tiffani,

I'd use a VLOOKUP table.

Create a small table as follows, somewhere on your worksheet, or in a
separate worksheet in the same workbook if you like. (Each figure is in
a different cell)

C 100
E 1

Then select the range, go to the Insert Menu, Name, and give it a name
(e.g. prices)

In column C (say C2), type the formula =VLOOKUP(D2,prices,2,false)

Fill C2 down your 7000 rows. All price changes will be made.

The advantage of doing it this way is that if you ever have to change
prices again, you just change the VLOOKUP table, and all of the changes
will be made for you.

Hope this helps.

Let me know how it goes.
 
T

Tiffani

Duke,
Thank you so much. That did it. Here is another question!

This is a price update from excel to our ProTrac program that we use. This
particular one is Pass & Seymour and OnQ/Legrand. They are mixed together. Is
there a way we can extract out the OnQ to another worksheet. Or would we just
have to like cut and paste.
 
D

Duke Carey

When you are looking at the data, is there something that obviously
differentiates one set from the other? If so, you can use Excel's Data->Sort
features to segregate the data into 2 groups. That'd simplify the cutting
and pasting
 
Top