| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula/macro that ignores cells nonblanks
I have this formula: =IF(ISNA(VLOOKUP(B4;'C:\WINNT\Profiles\846783\Desk top\trabalho excel\Referencias no total\[Parcial.xls]Sheet1'!$A$2:$F$602;6;FALSE));"";IF(ISBLANK(VLOOKU P(B4;'C:\WINNT\Profiles\846783\Desktop\trabalho excel\Referencias no total\[Parcial.xls]Sheet1'!$A$2:$F$602;6;FALSE));"";VLOOKUP(B4;'C:\WI NNT\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 -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula/macro that ignores cells nonblanks
You can do it with these steps
Copy the formula Select the whole target range Menu EditGotoSpecial and check the Blanks button Paste That should do it. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "pmarques" wrote in message ... I have this formula: =IF(ISNA(VLOOKUP(B4;'C:\WINNT\Profiles\846783\Desk top\trabalho excel\Referencias no total\[Parcial.xls]Sheet1'!$A$2:$F$602;6;FALSE));"";IF(ISBLANK(VLOOKU P(B4;'C :\WINNT\Profiles\846783\Desktop\trabalho excel\Referencias no total\[Parcial.xls]Sheet1'!$A$2:$F$602;6;FALSE));"";VLOOKUP(B4;'C:\WI NNT\Pro files\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 -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula/macro that ignores cells nonblanks
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. ----------------------------------------------------------------------- Bob Phillips Wrote: You can do it with these steps Copy the formula Select the whole target range Menu EditGotoSpecial and check the Blanks button Paste That should do it. -- -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula/macro that ignores cells nonblanks
Are you copying more than one cell?
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "pmarques" wrote in message ... 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. ----------------------------------------------------------------------- Bob Phillips Wrote: You can do it with these steps Copy the formula Select the whole target range Menu EditGotoSpecial and check the Blanks button Paste That should do it. -- -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula/macro that ignores cells nonblanks
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 ------------------------------------------------------ Bob Phillips Wrote: Are you copying more than one cell? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "pmarques" wrote in message ... 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. ----------------------------------------------------------------------- Bob Phillips Wrote: You can do it with these steps Copy the formula Select the whole target range Menu EditGotoSpecial and check the Blanks button Paste That should do it. -- -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula/macro that ignores cells nonblanks
I just tried it in your workbook, it worked fine for me!
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "pmarques" wrote in message ... 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 ------------------------------------------------------ Bob Phillips Wrote: Are you copying more than one cell? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "pmarques" wrote in message ... 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. ----------------------------------------------------------------------- Bob Phillips Wrote: You can do it with these steps Copy the formula Select the whole target range Menu EditGotoSpecial and check the Blanks button Paste That should do it. -- -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula/macro that ignores cells nonblanks
Works for me to. It was making one thing wrong, I was copying the formula from the formula bar, instead of copying the cell that contained the formula. I´m an ass :) :) Thanks for your Help ----------------------------------------------------------------------- Bob Phillips Wrote: I just tried it in your workbook, it worked fine for me! -- -- pmarques ------------------------------------------------------------------------ pmarques's Profile: http://www.excelforum.com/member.php...o&userid=25110 View this thread: http://www.excelforum.com/showthread...hreadid=510969 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
| Counting only active cells | Excel Discussion (Misc queries) | |||
| Fill cells from non-adjacent cells | Excel Discussion (Misc queries) | |||
| Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
| How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions | |||
Linear Mode
