Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
Excel 2000 - this is under help - keyword-null and doesn't work and I need it
to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
Place a an autofilter from the Data Menu, then select non blanks
select the columns you want the data from and then hit F5, click on Special select visible cells only and do your copy and paste if you get an error stating that the selection already visible cells only, then skip the F5 method select the cells you want and do a copy and paste -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
use a helper column in shhet one (c?)
in c1 enter =if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup( A1,sheet2!A:B,2,0)) copy and paste down to the end of your data. select column C copy select column B paste special values, delete column c "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
Michael - I am probablly missing something - here is what I did
Data/autofilter = never used before - saw the down arrows on col's didn't touch, right? I selected non blank cells (5) for test with the control key Then selected same col I just selected non blanks - right? F5/special/visible = all ok you then said to copy and paste - i copied col that I just selected non blanks/pasted over owner col. It pasted blank cells over owners names, but did copy over the financial ofcr over. I just need it to not paste blank over filled cell with name. what did I do wrong? Thanking you in advance -- claudia "Michael" wrote: Place a an autofilter from the Data Menu, then select non blanks select the columns you want the data from and then hit F5, click on Special select visible cells only and do your copy and paste if you get an error stating that the selection already visible cells only, then skip the F5 method select the cells you want and do a copy and paste -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
bj - thanks for your formula so quickly but Excel 2000 says there was an
error - here is what I did - see what I did wrong, please. your 1st sentence says 'use a helper col in sheet one (c?)' - my sheet 1 is the database so i inserted a sheet before it - yes? and entered your formula in (c) of sheet1 yes/no? you said copy and past down to the end of your data - do you mean go to sheet2 and copy the 2 col's with owner name and fin ofcr name? Bring to sheet1 and paste in col C? select col C - I get this copy - I get select col B paste special values, delete col c - I get. Please clarify. thanking you in advance. -- claudia "bj" wrote: use a helper column in shhet one (c?) in c1 enter =if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup( A1,sheet2!A:B,2,0)) copy and paste down to the end of your data. select column C copy select column B paste special values, delete column c "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
please respond to my clarification questions of 082307 thanks
-- claudia "bj" wrote: use a helper column in shhet one (c?) in c1 enter =if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup( A1,sheet2!A:B,2,0)) copy and paste down to the end of your data. select column C copy select column B paste special values, delete column c "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
Please respond to my reply of 08.23 - I need clarification - thank you so much
-- claudia "Michael" wrote: Place a an autofilter from the Data Menu, then select non blanks select the columns you want the data from and then hit F5, click on Special select visible cells only and do your copy and paste if you get an error stating that the selection already visible cells only, then skip the F5 method select the cells you want and do a copy and paste -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
I misread your initial submission and I missed your earlier response
in the data base if there is anything in Column insert a helper column in Column C in C1 enter =if(B1="",A1,B1) copy C1 and paste in column C down to the end of your data Select column c and copy select column A and paste special values delete column C "lilleke" wrote: please respond to my clarification questions of 082307 thanks -- claudia "bj" wrote: use a helper column in shhet one (c?) in c1 enter =if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup( A1,sheet2!A:B,2,0)) copy and paste down to the end of your data. select column C copy select column B paste special values, delete column c "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
will try now and let you know.
-- claudia "bj" wrote: I misread your initial submission and I missed your earlier response in the data base if there is anything in Column insert a helper column in Column C in C1 enter =if(B1="",A1,B1) copy C1 and paste in column C down to the end of your data Select column c and copy select column A and paste special values delete column C "lilleke" wrote: please respond to my clarification questions of 082307 thanks -- claudia "bj" wrote: use a helper column in shhet one (c?) in c1 enter =if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup( A1,sheet2!A:B,2,0)) copy and paste down to the end of your data. select column C copy select column B paste special values, delete column c "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
A1 did not come over to C1 like I understand your formula.
you said if b1 is "" (= blank, right), A1 else B1 B1 does copy over to C1 just fine. when i copied C over to A special values only the blank lines in C overwrote the filled cell i have excel 2000 - i am soooo close i see no reason for it not to work -- claudia "bj" wrote: I misread your initial submission and I missed your earlier response in the data base if there is anything in Column insert a helper column in Column C in C1 enter =if(B1="",A1,B1) copy C1 and paste in column C down to the end of your data Select column c and copy select column A and paste special values delete column C "lilleke" wrote: please respond to my clarification questions of 082307 thanks -- claudia "bj" wrote: use a helper column in shhet one (c?) in c1 enter =if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup( A1,sheet2!A:B,2,0)) copy and paste down to the end of your data. select column C copy select column B paste special values, delete column c "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
prevent copied blank cells from replacing existing data
GOT IT - THANKS SO SO MUCH - i looked in help and placed a space between ""
and IT WORKED - I AM SO EXCITED THANK YOU. -- claudia "lilleke" wrote: A1 did not come over to C1 like I understand your formula. you said if b1 is "" (= blank, right), A1 else B1 B1 does copy over to C1 just fine. when i copied C over to A special values only the blank lines in C overwrote the filled cell i have excel 2000 - i am soooo close i see no reason for it not to work -- claudia "bj" wrote: I misread your initial submission and I missed your earlier response in the data base if there is anything in Column insert a helper column in Column C in C1 enter =if(B1="",A1,B1) copy C1 and paste in column C down to the end of your data Select column c and copy select column A and paste special values delete column C "lilleke" wrote: please respond to my clarification questions of 082307 thanks -- claudia "bj" wrote: use a helper column in shhet one (c?) in c1 enter =if(iserror(vlookup(a1,Sheet2!A:B,2,0),b1,vlookup( A1,sheet2!A:B,2,0)) copy and paste down to the end of your data. select column C copy select column B paste special values, delete column c "lilleke" wrote: Excel 2000 - this is under help - keyword-null and doesn't work and I need it to work or am I reading wrong? I hv 2 databases w 2 col 1 for owner name and 1 for financial ofcr name I need to cut/paste fin ofcr name into owner name cell if there is a financial officer name because I would rather mailer go to fin ofcr. There isn't always name in fin ofcr col. I scroll until I find fin ofcr name, cut/paste to col to the left and overwrite the owner's name with fin ofcr name. about 10 fin ofcr names per visable screen. Painful on mouse hand. I want to highlight maybe whole fin ofcr col or at least visable page cut/paste and overlay col to left with fin ofc name but not make other cells blank. currently when I do this fin ofcr name is pasted and all other blank cells are pasted as blanks also. If copied blank cell overlays not blank cell I want blank cell not copied. Please help. -- claudia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to prevent copied data from appearing until triggered by anoth | Excel Worksheet Functions | |||
New data will not sort with existing data from copied file | Excel Worksheet Functions | |||
Replacing blank cells | Excel Worksheet Functions | |||
how to prevent blank cell copied when combining cells? | Excel Discussion (Misc queries) | |||
Prevent Hidden Column data from being copied/pasted? | Excel Discussion (Misc queries) |