Formula/macro that ignores cells nonblanks

P

pmarques

I have this formula:
=IF(ISNA(VLOOKUP(B4;'C:\WINNT\Profiles\846783\Desktop\trabalho
excel\Referencias no
total\[Parcial.xls]Sheet1'!$A$2:$F$602;6;FALSE));"";IF(ISBLANK(VLOOKUP(B4;'C:\WINNT\Profiles\846783\Desktop\trabalho
excel\Referencias no
total\[Parcial.xls]Sheet1'!$A$2:$F$602;6;FALSE));"";VLOOKUP(B4;'C:\WINNT\Profiles\846783\Desktop\trabalho
excel\Referencias no total\[Parcial.xls]Sheet1'!$A$2:$F$602;6;FALSE)))

THAT I COPY DOWN ONCE A WEEK TO BRING UP TO DATE, THE PROBLEM IS THAT
ERASES THE RESULTS THAT WERE ALREADY THERE.
WHAT I WANTED (WHEN I COPY DOWN) IS TO IGNORE THE CELLS THAT ARE
NONBLANKS..
IDEAS PLEASE.
Thanks
 
B

Bob Phillips

You can do it with these steps

Copy the formula
Select the whole target range
Menu Edit>Goto>Special and check the Blanks button
Paste

That should do it.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
P

pmarques

Hi,
Until the select Blanks it's ok but wen i try to paste the formula it
gives the error:

"Data on the Clipboard is not the same size and shape as the selected
area. Do you want to paste the data anyway?"

or with another range

"The command you chose cannot be performed with multiple selections
Select a single range and click the command again."

Am i doing something wrong.
I copy the formula from the first cell. And i'm working with excell 97
Thanks

note: I had attached one exemple, the formula is in I2 and when copy
down should not modify the existing data.

-----------------------------------------------------------------------
 
B

Bob Phillips

Are you copying more than one cell?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
P

pmarques

No. I copy the formula from the cell I2 in the example 4 attached i
select the blanks using the metod you said and try to paste but with no
sucess.

Thanks


------------------------------------------------------
 
B

Bob Phillips

I just tried it in your workbook, it worked fine for me!


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
P

pmarques

Works for me to. It was making one thing wrong, I was copying th
formula from the formula bar, instead of copying the cell tha
contained the formula.
I´m an ass :) :)

Thanks for your Help

-----------------------------------------------------------------------
 
Top