Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new loca
Hello,
I need to determine if a cell in one worksheet has a specific string of characters, and if yes, copy the entire cell to a second worksheet. So, If 'Worksheet1!a1' contains the string "ACS", copy the content of 'Worksheet1!a1' to 'Worksheet2!a1' I've tried several things I've found on this BB, but not luck so far. THANKS! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new loca
=IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1!a1'))),'W orksheet1!a1',"") ???
Dave -- Brevity is the soul of wit. "Shelly" wrote: Hello, I need to determine if a cell in one worksheet has a specific string of characters, and if yes, copy the entire cell to a second worksheet. So, If 'Worksheet1!a1' contains the string "ACS", copy the content of 'Worksheet1!a1' to 'Worksheet2!a1' I've tried several things I've found on this BB, but not luck so far. THANKS! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new loca
Watch your apostrophes:
=IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1'!a1))),'W orksheet1'!a1,"") And sometimes just using =isnumber() makes the formula easier to read: =IF(ISnumber(SEARCH("ACS",'Worksheet1'!a1)),'Works heet1'!a1,"") Dave F wrote: =IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1!a1'))),'W orksheet1!a1',"") ??? Dave -- Brevity is the soul of wit. "Shelly" wrote: Hello, I need to determine if a cell in one worksheet has a specific string of characters, and if yes, copy the entire cell to a second worksheet. So, If 'Worksheet1!a1' contains the string "ACS", copy the content of 'Worksheet1!a1' to 'Worksheet2!a1' I've tried several things I've found on this BB, but not luck so far. THANKS! -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new
Good points, all. Agreed about ISNUMBER().
Dave -- Brevity is the soul of wit. "Dave Peterson" wrote: Watch your apostrophes: =IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1'!a1))),'W orksheet1'!a1,"") And sometimes just using =isnumber() makes the formula easier to read: =IF(ISnumber(SEARCH("ACS",'Worksheet1'!a1)),'Works heet1'!a1,"") Dave F wrote: =IF(NOT(ISERROR(SEARCH("ACS",'Worksheet1!a1'))),'W orksheet1!a1',"") ??? Dave -- Brevity is the soul of wit. "Shelly" wrote: Hello, I need to determine if a cell in one worksheet has a specific string of characters, and if yes, copy the entire cell to a second worksheet. So, If 'Worksheet1!a1' contains the string "ACS", copy the content of 'Worksheet1!a1' to 'Worksheet2!a1' I've tried several things I've found on this BB, but not luck so far. THANKS! -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new loca
Try this small macro:
Sub shelley() Set r1 = Sheets("Sheet1").Range("A1") Set r2 = Sheets("Sheet2").Range("A1") v = r1.Value If InStr(v, "ACS") 0 Then r1.Copy r2 End If End Sub If you are unfamiliar with VBA, See: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200702 "Shelly" wrote: Hello, I need to determine if a cell in one worksheet has a specific string of characters, and if yes, copy the entire cell to a second worksheet. So, If 'Worksheet1!a1' contains the string "ACS", copy the content of 'Worksheet1!a1' to 'Worksheet2!a1' I've tried several things I've found on this BB, but not luck so far. THANKS! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new loca
Formulas retrieve values--not really copy.
=if(countif('sheet1'!a1,"*acs*")0,'sheet1'!a1,"wh ateveryouwanthere") Shelly wrote: Hello, I need to determine if a cell in one worksheet has a specific string of characters, and if yes, copy the entire cell to a second worksheet. So, If 'Worksheet1!a1' contains the string "ACS", copy the content of 'Worksheet1!a1' to 'Worksheet2!a1' I've tried several things I've found on this BB, but not luck so far. THANKS! -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new
Thanks for the quick replies... I've tried the macro and ISERROR, but now I
see the need is more comlicated. The range that I am searching is C33 through C77 (in Worksheet1) If any of these cells contain the string "ACS" I need the value of that cell to also be put into a cell in the 2nd worksheet but the range for this is only A8 through A20 So the "target" isn't the same cell as the "source". At no time will the source list (C33 - C77) return more values than will fit in the target list (A8 - A20). Much Thanks! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to determine if a cell has a value, then copy it to a new
I'd apply data|Filter|autofilter and filter C33:C77 to show the cells that
contain ACS and then copy those visible rows to that other range. Shelly wrote: Thanks for the quick replies... I've tried the macro and ISERROR, but now I see the need is more comlicated. The range that I am searching is C33 through C77 (in Worksheet1) If any of these cells contain the string "ACS" I need the value of that cell to also be put into a cell in the 2nd worksheet but the range for this is only A8 through A20 So the "target" isn't the same cell as the "source". At no time will the source list (C33 - C77) return more values than will fit in the target list (A8 - A20). Much Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Cell to Word ? | Excel Discussion (Misc queries) | |||
AUTOMATIC way to copy the value of a cell in one spreadsheet | Excel Worksheet Functions | |||
How to Copy the value of a cell to any given cell | Excel Discussion (Misc queries) | |||
how to count the number of text frequencies and copy to other cell | Excel Worksheet Functions | |||
hpw do I logic test a cell then copy the row to diff. SS | Excel Worksheet Functions |