ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE?

G

Guest

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.
 
G

Guest

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!
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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.
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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

:

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.

:

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.


:

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.

:

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



:

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)"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.


:

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

:

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.

:

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.


:

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.

:

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



:

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)"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.


:

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

:

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.

:

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.


:

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.

:

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



:

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)"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.


:

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

:

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.

:

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.


:

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.

:

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



:

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)"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.


:

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

:

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.

:

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.


:

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.

:

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



:

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)"
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok... IF(MID($B$5,1,1)="0","0"&$B$5,"0"&$B$5).........seems to work pretty
good. Now to my importing list to replace (p/n) portion


JLatham said:
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 said:
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 said:
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.


:

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

:

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.

:

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.


:

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.

:

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



:

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)"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
Excel Help to copy information from one row at a time to another sheet 1
Array, Formula, Constant, IF Statement Question???? 4
Copying formulas across columns 2
Array Constant 3
Help with SUMIF & AND function 2
How to use autofilter in excel with formula reference changing 1
Write formula for simple copy and paste to another cell 1

Top