| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=IF(C1=""," ","ImportID-01")
Hey all,
As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
This requires that row 1 be blank. Change the $A$1:A1 portion to reference
the column whre you place the formula =IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00")) "Outlook, eh?" wrote: Hey all, As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Almost there Duke!
It is placing 0s in the field next to where the data is. So it is finding the correct fields, but it isn't placing ImportID-01 and then increasing by 1. Thanks so far! "Duke Carey" wrote: This requires that row 1 be blank. Change the $A$1:A1 portion to reference the column whre you place the formula =IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00")) "Outlook, eh?" wrote: Hey all, As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Did you change the $A$1:A1 reference to the column of interest??
"Outlook, eh?" wrote: Almost there Duke! It is placing 0s in the field next to where the data is. So it is finding the correct fields, but it isn't placing ImportID-01 and then increasing by 1. Thanks so far! "Duke Carey" wrote: This requires that row 1 be blank. Change the $A$1:A1 portion to reference the column whre you place the formula =IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00")) "Outlook, eh?" wrote: Hey all, As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Yes. In my case your formula would look like this:
=IF(M2=""," ","ImportId-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00")) BTW This is what the data file really looks like: K L M Header Header Blank (Blank until function puts info in) Old Data 802-888-8777 "Duke Carey" wrote: This requires that row 1 be blank. Change the $A$1:A1 portion to reference the column whre you place the formula =IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00")) "Outlook, eh?" wrote: Hey all, As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Your formula works perfectly for me. I'm using Excel 2007, but can't see any
reason the version would cause a difference "Outlook, eh?" wrote: Yes. In my case your formula would look like this: =IF(M2=""," ","ImportId-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00")) BTW This is what the data file really looks like: K L M Header Header Blank (Blank until function puts info in) Old Data 802-888-8777 "Duke Carey" wrote: This requires that row 1 be blank. Change the $A$1:A1 portion to reference the column whre you place the formula =IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00")) "Outlook, eh?" wrote: Hey all, As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
I am using 2003. I can't find any reason for the problem to be related to the
version. Either. Sigh... "Duke Carey" wrote: Your formula works perfectly for me. I'm using Excel 2007, but can't see any reason the version would cause a difference "Outlook, eh?" wrote: Yes. In my case your formula would look like this: =IF(M2=""," ","ImportId-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00")) BTW This is what the data file really looks like: K L M Header Header Blank (Blank until function puts info in) Old Data 802-888-8777 "Duke Carey" wrote: This requires that row 1 be blank. Change the $A$1:A1 portion to reference the column whre you place the formula =IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00")) "Outlook, eh?" wrote: Hey all, As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
I'm wondering if you're making this more difficult than it needs to be. Try
this in cell M2: =IF(K2="","","ImportID-"&TEXT(COUNTIF($K$2:K2,"<"&""),"00")) Copy down as needed. HTH, Elkar "Outlook, eh?" wrote: I am using 2003. I can't find any reason for the problem to be related to the version. Either. Sigh... "Duke Carey" wrote: Your formula works perfectly for me. I'm using Excel 2007, but can't see any reason the version would cause a difference "Outlook, eh?" wrote: Yes. In my case your formula would look like this: =IF(M2=""," ","ImportId-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00")) BTW This is what the data file really looks like: K L M Header Header Blank (Blank until function puts info in) Old Data 802-888-8777 "Duke Carey" wrote: This requires that row 1 be blank. Change the $A$1:A1 portion to reference the column whre you place the formula =IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00")) "Outlook, eh?" wrote: Hey all, As you can see the formula will fill a column next to any fields that are not blank with ImportID-01. This works really swell, but I would like to also increase the 01 by 1 so that it looks like this: ImportID-01 Ringo ImportID-02 Paul ImportID-03 John ImportID-04 George Any ideas?? Thanks, you guys have never failed me! I appreciate it so much.. Bryce |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Outlook, eh? wrote...
.... =IF(M2="","","ImportId-" &TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00")) .... Maybe try =TEXT(SUMPRODUCT((TRIM(M2)<"")*(TRIM(M $2:M2)<"")),"""ImportID-""00;;") |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Thanks , but I got the message:
The formula you typed contains an error.. Did I have to change M to the empty cells in L, in the second part? "Harlan Grove" wrote: Outlook, eh? wrote... .... =IF(M2="","","ImportId-" &TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00")) .... Maybe try =TEXT(SUMPRODUCT((TRIM(M2)<"")*(2TRIM(M $2:M)<"")),"""ImportID-""00;;") |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
"Outlook, eh?" wrote...
Thanks , but I got the message: The formula you typed contains an error.. Awkward word wrapping. I'll fix it below. Did I have to change M to the empty cells in L, in the second part? .... No. If the names are in column M, then that's the only column to which the formula would need to refer. =TEXT(SUMPRODUCT((TRIM(M2)<"")*(TRIM(M$2:M2)<"") ), """ImportID-""00;;") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Hybrid Mode
