Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Basically I think I need to create an array or constant like
(A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
CONCATENATE sorry
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Let me look at this, per your request, and think about it some. Have to rush
out right now, so can't focus on it right this minute, but I wanted you to know that you did get my attention. And perhaps while I'm out and about, someone else will provide a solution anyhow! "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Thank you very much, I've been trying to get an answer but it seems like no
one knows what I'm trying to accomplish. It should be simple but I haven't figured it out. Thanks again ahead of time. "JLatham" wrote: Let me look at this, per your request, and think about it some. Have to rush out right now, so can't focus on it right this minute, but I wanted you to know that you did get my attention. And perhaps while I'm out and about, someone else will provide a solution anyhow! "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
If what I understand from what I read, then what you want is a formula that
will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Thank you, I think this will do, I'm going to work at it a bit and see if I
can get the results I am looking for. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Ok, you got me. Now I'm just wanting to do more....How do you think I could
tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Ok, I figured out the ROW counting, Still trying the others in my question.
Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
I'm not sure what you mean by count? Do you mean an actual count of the
total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Thats amazing and I don't understand it, put I put it in and got the result.
So, Now If I had a Sheet named "Row1" and wanted one cell to read "Row" and one to read "1" , to split that would it be possible for it to read that? "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect
result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
An encyclopedia might be stretching it some. Quick, condensed pocket
reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
If I can get this one formula perfected I won't know what to do.
what I'm trying to do is if it sees a "01" literally then it will ad a "000" and if it reads a "10", "11", "12" etc. then it will only ad a "00", so basically I can have room for it to count from 0001 to 0100 or 1000 etc, but i'm not getting the exact result I'm wanting., i've even tried changing the sheet name from 1 to 01 so here is what I have so far: =CONCATENATE("0", $B$3, $B$4, IF(RIGHT(CELL("filename",A1),2))="0","00","000"), $B$5, "-", OFFSET($A$2,0,COLUMN()-COLUMN($A$2)), OFFSET($C$3,ROW()-ROW($C$3),0), "-", "(p/n)") Then I'm also trying to import a list to replace the "(p/n)" at the end but I haven't gotten there yet "JLatham" wrote: An encyclopedia might be stretching it some. Quick, condensed pocket reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
I've tried LEN, RIGHT, MID.....just haven't gotten the result, I've been
replacing "TTT", and "FFF" so that I can see the true & false results but it doesn't seem to read my IF statement right, if I need to be using if at all "JLatham" wrote: An encyclopedia might be stretching it some. Quick, condensed pocket reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Are you busy now? Can I pick at your brain for a bit?
"JLatham" wrote: An encyclopedia might be stretching it some. Quick, condensed pocket reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Are you busy now? Can I pick at your brain for a bit?
"SayWhatAuto" wrote: I've tried LEN, RIGHT, MID.....just haven't gotten the result, I've been replacing "TTT", and "FFF" so that I can see the true & false results but it doesn't seem to read my IF statement right, if I need to be using if at all "JLatham" wrote: An encyclopedia might be stretching it some. Quick, condensed pocket reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Ok, it seems like this should work but it's not giving me the correct result.
=IF(RIGHT(CELL("contents",$F$29),2)="1",T,F) where basically i'm trying to say if there is a two digit number like "12" then it will produce one answer and if it is a one digit (or double) like "1" or "01" then it will produce a second result "JLatham" wrote: An encyclopedia might be stretching it some. Quick, condensed pocket reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
alright well i figured out the T and F needed to be in "", but thats still
just letting me know that if the last two digits are "12" then it's true or false, but I'm trying to just find out if it's a "01" or "0" then one result and if it's double digit like "12", "15" then another result "JLatham" wrote: An encyclopedia might be stretching it some. Quick, condensed pocket reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?
Ok... IF(MID($B$5,1,1)="0","0"&$B$5,"0"&$B$5).........se ems to work pretty
good. Now to my importing list to replace (p/n) portion "JLatham" wrote: An encyclopedia might be stretching it some. Quick, condensed pocket reference might be closer. There are certainly areas of Excel and features/functions of it that I am not that strong in. I don't even always get to the final destination by the smoothest road, but I usually manage to get there. Glad to have been of some help to you. "SayWhatAuto" wrote: Ok, great. I just used simply =RIGHT(CELL("filename")) and got a perfect result. You are an excel encyclopedia Latham! I need to get you on speed dial! haha "JLatham" wrote: I'm not sure what you mean by count? Do you mean an actual count of the total number of cells? Or a total of the values in all of those cells. Are you talking about one column only? Excel does have some functions that work across multiple sheets; let's say you have 3 sheets: Sheet1, Sheet2, and Sheet3 - and they are in that order in the workbook. You can put a formula in a cell like this: =SUM(Sheet1:Sheet3!A5) and you would get the total value of Sheet1!A5 + Sheet2!A5 and Sheet3!A5 =SUM(Sheet1:Sheet3!A1:A15) would give you the total of range A1:A15 on all 3 sheets. Look at the CELL function as a way to get a sheet's name dynamically. Specifically check out =Cell("filename") and you type it just like that, with the word filename, not the actual name of your file. It won't return anything until the workbook has been saved to disk at some point in time. That is, if you start with a new workbook and enter that, it won't show anything until you save the workbook. If you open an existing workbook and enter it, you'll get a result. The sheet name is at the very end of the returned value, all after right-square-bracket. This formula will pull out the sheet name: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) More properly, if you're going to look for sheet names or other info out of it pertaining to the current sheet, you should reference a cell on the sheet as part of it also: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"))) =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1))) Help will explain why that's needed at times. "SayWhatAuto" wrote: Ok, I figured out the ROW counting, Still trying the others in my question. Also, is there a way to have one cell in everysheet count from 1st sheet to last? 1-50 "SayWhatAuto" wrote: Ok, you got me. Now I'm just wanting to do more....How do you think I could tell it to format the -00 "$B$4", into a ### digit so that if $B$4 is a single digit it will have "00" in front but if $B$4 is a double digit it will only lay down a "0". Additionally, the +11 at the end of the formula is because I'm counting out from 1 to 12.....is there a simple formula that would be in place of +11,+10,+9, etc. And last, if I were to import a listing that would be say in column A1 through row 20,000 or so, could I set in something to call in place of (p/n)? Again thanks for the help, It seems that Excel is so versatile now, It's hard to know where to start. "JLatham" wrote: If what I understand from what I read, then what you want is a formula that will automatically change the & $C$1 & portion of your formula to reference value in row 1 of the same column, no matter what column you happen to be in. If that is correct then I think this will do for that part of it: OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) When that is put into any row of column C it will return the value in C1, when put into column AA, it will return the value in AA1, same for I, O and U. So your & $C$1 & becomes & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & in your formula. I've shown the formula as COLUMN()-COLUMN($A$1) more to show how it's working than anything else, but since the value of COLUMN($A$1) is always 1 (one), then you could rewrite that, and all instances below that use it as COLUMN()-1 to be specific, it could be simplified to: & OFFSET($A$1,0,COLUMN()-1) & By the way, your IF statements don't need to be IF statements, consider that when a cell is = "" you want to put up "", and if it isn't "" then you want what's in the cell. So you can do away with the IFs. your formula (with my change) could become: ="0" & $B$2 & $B$3 & "-00" & $B$4 & OFFSET($A$1,0,COLUMN()-COLUMN($A$1)) & $B$5+11 & "(p/n)" You could write that with CONCATENATE as: =CONCATENATE("0", $B$2, $B$3, "-00", $B$4, OFFSET($A$1,0,COLUMN()-COLUMN($A$1)), $B$5+11, "(p/n)") Hope this helps. If I missed the mark, let me know. "SayWhatAuto" wrote: Basically I think I need to create an array or constant like (A,B,C,D,E,F,G,H) and then every other 6 columns have it pull from a next one in the list, but the problem is I'm trying to create one formula that will work for every cell, every page, so that I don't have to create, copy, paste, edit, fill, for each 45 columns, 13 rows, 50 pages. My formula so far: ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))&$C$1&(IF($B$5="","",$ B$5))+11&"(p/n)" Which seems like I could simplify with INDIRECT in place of IF statement and CONSTITUATE insead of all the "&" signs but besides that my main problem remains the $C$1 reference that I'm having to change and a smaller +11,+10, that I could simplify somehow and then eventually the (p/n) which I've got nothing for yet, but it would be nice to eventually import a list and then have a reference to pick from that list and replace (p/n) in cells C2:AX13 with "part #'s" from a list for example A1:A25000 or something along those lines. Tried posting question in other group but i could not explain clear enough to get an answer. Seems like it should be a simple solution, I'm just not good at expressing verbally what I'm trying to visually accomplish. I'm trying to create a formula that I can use in cells C2:AX13 for sheets 1:50 Each sheet will be pulling different info from cells B2:B5 where each sheet will contain different info, and each cell will also pull from C1,I1,O1,U1,AA,AG,AM,AS (which will be the same for each page, but different from each block C:H,I:N, etc.) Other Threads were as follows: Continued from yesterday's thread, still unable to find answer for problem: I'll try to explain further: What I've Set up is 50 pages where on each page I've got 50 or so rows with 13 columns. So on Column 1 is my "helper" that I can change different references with $A$1, $A$2, $A,$3, ect. so that I'm using the same formula for my 50 pages, 50 rows, x 13 colums, so that I won't have to change anything but the helper column. On the 1st Row, I'm using it like a Header Row, that I've got A,B,C,D,etc.....with about 5 columns between each letter. Not Formulated but for example as follows; A2="A", B2="", C2="", D2="", E2="", F2="B", ect so that the header letter is only every 5 or so columns, (the blank columns are data within that block, A001, A002, A003, A004, etc.) So if thats not confusing enough, Every page is going to have the same exact format: 01XX001-A12-(####) with variables changing per row,column, and page thats where the formula comes in but I'm stuck with having to change 2 pieces manually, one is the count of "A12", "A11", "A10", which isn't bad because once I do that I can to a Ctrl-R and fill the other columns but with the $C$1 reference I'm having to change that as $C$1,$I$1,$O$1,$U$1,$AA$1 for every several colums. If this makes any sense please help. "Elkar" wrote: I'm not sure I completely understand your question, but try looking up help on the INDIRECT function. That may be what you're looking for. If not, then perhaps a clarification with more specific examples may help. HTH, Elkar "SayWhatAuto" wrote: I've got a workbook with 50 sheets or so and I want to make a statement that will work with every sheet so that I don't have to manually change each sheet. I'm trying to do a $C$1,$I$1,$O$1,$U$1,$AA$1 because I'm using rows 2-13 and I want row 1 to be my header that it pulls from. So is there an array constant that will work so that sheets 1-50 all pull the same info, without having to do 5 separate formulas?, this is what I have so far.*****being where i'm stuck ="0"&(IF($B$2="","",$B$2))&(IF($B$3="","",$B$3) )&"-"&"00"&(IF($B$4="","",$B$4))& *****$C$1****** &(IF($B$5="","",$B$5))+11&"(p/n)" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect ref in array formulas | Excel Worksheet Functions | |||
Array constant issue | Excel Worksheet Functions | |||
Can INDIRECT return a range array? | Excel Worksheet Functions | |||
Indirect Range Referencing | Charts and Charting in Excel | |||
VBA Import of text file & Array parsing of that data | Excel Discussion (Misc queries) |