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

It's not clear where the formula will be entered, but if it's in column
C, you can use C$1, to lock the reference to row 1. When you copy the
formula to a different column, it will refer to that column.

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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.




"Debra Dalgleish" wrote:

It's not clear where the formula will be entered, but if it's in column
C, you can use C$1, to lock the reference to row 1. When you copy the
formula to a different column, it will refer to that column.

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

It's still not clear to me where the formula in your first post is
entered, or how and when is should refer to a cell other than C1.

What's in cells C1, I1, etc.? Can you enter those values in every
column, then just have each formula refer to row 1 in its own column?

SayWhatAuto wrote:
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.




"Debra Dalgleish" wrote:


It's not clear where the formula will be entered, but if it's in column
C, you can use C$1, to lock the reference to row 1. When you copy the
formula to a different column, it will refer to that column.

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I see that you've started at least three other threads on this question,
so perhaps you'll find a solution in one of those.

SayWhatAuto wrote:
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.




"Debra Dalgleish" wrote:


It's not clear where the formula will be entered, but if it's in column
C, you can use C$1, to lock the reference to row 1. When you copy the
formula to a different column, it will refer to that column.

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html