Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I clear all cells containing #n/a on a spreadsheet?
I use a lookup function in excel to pull data for selected users. When the
user is not listed in the range, the result is #n/a. Rather than selecting each #n/a cell and clearing contents, I was wondering if there is a function, formula or macro I can use that will just clear all cells returning #n/a value at one time. It would save me TONS of time every morning... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I clear all cells containing #n/a on a spreadsheet?
Edit Goto Special Formula Errors
and then clear all of them in one fell swoop!! -- Gary''s Student - gsnu200752 "haynheart" wrote: I use a lookup function in excel to pull data for selected users. When the user is not listed in the range, the result is #n/a. Rather than selecting each #n/a cell and clearing contents, I was wondering if there is a function, formula or macro I can use that will just clear all cells returning #n/a value at one time. It would save me TONS of time every morning... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I clear all cells containing #n/a on a spreadsheet?
On Nov 1, 12:49 pm, haynheart
wrote: I use a lookup function in excel to pull data for selected users. When the user is not listed in the range, the result is #n/a. Rather than selecting each #n/a cell and clearing contents, I was wondering if there is a function, formula or macro I can use that will just clear all cells returning #n/a value at one time. It would save me TONS of time every morning... You could also use the ISNA function to check the result of vlookup before displaying it, it effectively doubles the number of lookups so it isn't too efficient though. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I clear all cells containing #n/a on a spreadsheet?
Could you elaborate on how I would incorporate the ISNA function into the
formula? I've never used this fuction before... Thanks, "Robert" wrote: On Nov 1, 12:49 pm, haynheart wrote: I use a lookup function in excel to pull data for selected users. When the user is not listed in the range, the result is #n/a. Rather than selecting each #n/a cell and clearing contents, I was wondering if there is a function, formula or macro I can use that will just clear all cells returning #n/a value at one time. It would save me TONS of time every morning... You could also use the ISNA function to check the result of vlookup before displaying it, it effectively doubles the number of lookups so it isn't too efficient though. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I clear all cells containing #n/a on a spreadsheet?
Why do you want the clear the cells with #N/A ?
For display purposes? It screws up your calculations downstream? To add the ISNA function to a VLOOKUP formula and hide the error........... =IF(ISNA(VLOOKUP(G5,$C$1:$F$24,2,FALSE)),"",VLOOKU P(G5,$C$1:$F$24,2,FALSE)) This will make the cell look blank and allow calculations. Gord Dibben MS Excel MVP On Fri, 2 Nov 2007 20:53:00 -0700, haynheart wrote: Could you elaborate on how I would incorporate the ISNA function into the formula? I've never used this fuction before... Thanks, "Robert" wrote: On Nov 1, 12:49 pm, haynheart wrote: I use a lookup function in excel to pull data for selected users. When the user is not listed in the range, the result is #n/a. Rather than selecting each #n/a cell and clearing contents, I was wondering if there is a function, formula or macro I can use that will just clear all cells returning #n/a value at one time. It would save me TONS of time every morning... You could also use the ISNA function to check the result of vlookup before displaying it, it effectively doubles the number of lookups so it isn't too efficient though. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to clear contents of a spreadsheet | Excel Discussion (Misc queries) | |||
to clear contents of a spreadsheet | Excel Discussion (Misc queries) | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
clear all zero-length strings from spreadsheet? | Excel Discussion (Misc queries) | |||
Using control to clear spreadsheet | Excel Discussion (Misc queries) |