Array, Formula, Constant, IF Statement Question????

[SayWhatAuto]

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.

 

[pinmaster]

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]

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]

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]

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