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)"