Copying and pasting data when using ISBLANK

[svon]

Hi,

Ok, let's see if you can follow my description of the problem... Pls let me know if you need more clarification...

I did a VLOOKUP and pulled data from another file into a column (say column C). Some rows have data and some don't. I used ISERROR and blanked out rows that didn't have data instead of leaving the unsightly #N/A.

In column D, I wanted to pull in column B (Price info), for all Column C that has data. Here's what I did:
1. Copied column C and used Paste special as "values" so there is no formulas in column C.
2. In column D, I used the formula =IF(ISBLANK(C2),"",B2)

Here's my problem:
When I copied the above formula in column D, it pulls all column B data and not the data for which there is a match from Column C. If I individually click in each cell of column C, then column D pulls the right data and shows the formula working properly.

What should I do to avoid having to click on all column C cells to get the right value in column D. Any help is greatly appreciated.

Here's a sample layout:

Col A Col B Col C Col D
ABC 1.50
BCD 2.00 10
CDE 2.50
DEF 3.00 20

Desired outcome:
I want Column D to show 2.00 in row 2 and 3.00 in row 4. Because of using copy and paste special as "values" in col C, it seems like I have to click in each cell, in order to get my ISBLANK formula in Col D to work right.

Hope I've explained my problem sufficiently. Appreciate your help.

Thanks.

 

[Tazguy37]

In column D, you could also use: =IF(C1,B1,"")

You might check to see if your calculation is set to automatic or manual.

 

[svon]

Awesome. it works!

How do I check to see if calculation is set to auto/manual?

Thanks for your help!!

 

[Tazguy37]

Tools, Options, Calculation tab.

Glad you got it working.