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