| 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")
Nifty, but I haven't noticed it increasing 01 to 02, 03, etc...
"Elkar" wrote: 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 |
|
#10
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;;") |
|
#11
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;;") |
|
#12
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;;") |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Thanks so much! It worked like a charm.
Thanks everyone for your help. It renewed my faith in the power of the good ol' Arapnet. "Harlan Grove" wrote: "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;;") |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
All is was well when suddenly...
I noticed that I will need the number to be counting the cell number, for example ImportID-01 Joe Kathy ImportID-03 Sam ImportID-04 Lewis ImportID-06 Trish Any ideas? Thanks again very, very much! "Outlook, eh?" wrote: Thanks so much! It worked like a charm. Thanks everyone for your help. It renewed my faith in the power of the good ol' Arapnet. "Harlan Grove" wrote: "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;;") |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
"Outlook, eh?" wrote...
.... I noticed that I will need the number to be counting the cell number, for example ImportID-01 Joe Kathy ImportID-03 Sam ImportID-04 Lewis ImportID-06 Trish Any ideas? Thanks again very, very much! .... Simpler. If the topmost name were in cell M2, =TEXT((TRIM(M2)<"")*ROWS(M$2:M2),"""ImportID-""00;;") |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Nice work Harlan! It worked just right with text such as ImportID-
How about if it were just a number and I would still like to have it grow by each row (if not blank).. Such as: 1 Lucy 3 Trina "Harlan Grove" wrote: "Outlook, eh?" wrote... .... I noticed that I will need the number to be counting the cell number, for example ImportID-01 Joe Kathy ImportID-03 Sam ImportID-04 Lewis ImportID-06 Trish Any ideas? Thanks again very, very much! .... Simpler. If the topmost name were in cell M2, =TEXT((TRIM(M2)<"")*ROWS(M$2:M2),"""ImportID-""00;;") |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
=IF(C1=""," ","ImportID-01")
Outlook, eh? wrote...
.... How about if it were just a number and I would still like to have it grow by each row (if not blank).. Such as: 1 Lucy 3 Trina .... Give someone a fish, and you feed them for a day. Teach them to fish, and you feed them for life. Time for you to fish. What do you think you'd need to remove from the last formula I gave to produce this new behavior? Try experimenting with the formula. |
| 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) | |||
Linear Mode
