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

Hi

I've notice that Elkar has responded to your previous post and I know for a
fact that he is probably 10 times more knowledgeable on excel than me so I
would take his advice and give more precise details. It wouldn't hurt for
sure!

Regards
Jean-Guy

"SayWhatAuto" wrote:

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

It does seem like I could use the INDIRECT function instead of
"(IF($B$2="","",$B$2))" and I could use the CONSTATUATE (sp?) instead of
using & after each section, that would simplify, but still doesn't address
the array.

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.

Does that clear it up?

"pinmaster" wrote:

Hi

I've notice that Elkar has responded to your previous post and I know for a
fact that he is probably 10 times more knowledgeable on excel than me so I
would take his advice and give more precise details. It wouldn't hurt for
sure!

Regards
Jean-Guy

"SayWhatAuto" wrote:

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

Hi,

Still not sure what your trying to do but maybe something like this:

=LOOKUP(COLUMN(),{1,6,11,16,21,26,31,36},{"A","B", "C","D","E","F","G","H"})
when dragged across will produce AAAAABBBBBCCCCC etc...

does that help?
Jean-Guy

"SayWhatAuto" wrote:

It does seem like I could use the INDIRECT function instead of
"(IF($B$2="","",$B$2))" and I could use the CONSTATUATE (sp?) instead of
using & after each section, that would simplify, but still doesn't address
the array.

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.

Does that clear it up?

"pinmaster" wrote:

Hi

I've notice that Elkar has responded to your previous post and I know for a
fact that he is probably 10 times more knowledgeable on excel than me so I
would take his advice and give more precise details. It wouldn't hurt for
sure!

Regards
Jean-Guy

"SayWhatAuto" wrote:

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

THanks, I'm going to look at this and see if I can apply it. Thanks for
helping out.

"pinmaster" wrote:

Hi,

Still not sure what your trying to do but maybe something like this:

=LOOKUP(COLUMN(),{1,6,11,16,21,26,31,36},{"A","B", "C","D","E","F","G","H"})
when dragged across will produce AAAAABBBBBCCCCC etc...

does that help?
Jean-Guy

"SayWhatAuto" wrote:

It does seem like I could use the INDIRECT function instead of
"(IF($B$2="","",$B$2))" and I could use the CONSTATUATE (sp?) instead of
using & after each section, that would simplify, but still doesn't address
the array.

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.

Does that clear it up?

"pinmaster" wrote:

Hi

I've notice that Elkar has responded to your previous post and I know for a
fact that he is probably 10 times more knowledgeable on excel than me so I
would take his advice and give more precise details. It wouldn't hurt for
sure!

Regards
Jean-Guy

"SayWhatAuto" wrote:

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