Many Rows = Many Column Values

[cellblock]

I will attempt to capture my question in writing.

I'm running a scheduling shop utilizing Excel 2003 showing days on and
days off, meetings, etc. A cell might have an "O" for Off, or "M" for
meeting, or "1" for working, etc.

I enter these values (both text and numerical) into the cell blocks.
(Eg. B1, B2, B3, B4, B5), which is not a problem.

However, on the same worksheet, I wish to replicate the values from the
columns across a row. (Eg. AA1 = B1, AA2 = B2, AA3 = B4, AA5 = B5). I
don't want to physically have to select every single cell and type in
what it equals to. (Eg. Currently, I click on AA1, hit =, then select
B1, Enter, click on AA2, hit =, then select B2, etc). It is too
laborious to go through this process with an entire year's schedule.

When I click, Copy, then paste, it's replicates the wrong cells. (Eg.
AA1 = B1, AA2 = C1, AA3 = D1). This is not what I want. I DO want
this. ---> AA2 should equal B2, and AA3 should equal B3, etc.

Is there a way for me to do this without manually entering each formula
and clicking on the corresponding cell?

I've done a search on the forum, but don't exactly know what to search
for.

Thanks in advance.
Cellblock.

P.S. How does the "attach file" function work? I just add it like any
other file for all to see if they open it?

 

[Frank Kabel]

Hi
[snip]

However, on the same worksheet, I wish to replicate the values from
the columns across a row. (Eg. AA1 = B1, AA2 = B2, AA3 = B4, AA5 =
B5). I don't want to physically have to select every single cell and
type in what it equals to. (Eg. Currently, I click on AA1, hit =,
then select B1, Enter, click on AA2, hit =, then select B2, etc). It
is too laborious to go through this process with an entire year's
schedule.

try the following formula in AA1
=INDIRECT("B" & COLUMN()-26)

HTH
Frank

 

[Gord Dibben]

cell

cell

In AA1 enter =INDIRECT(ADDRESS(COLUMN()-26,2))

Drag/copy across the row.

Gord Dibben Excel MVP

 

[cellblock]

In AA1 enter =INDIRECT(ADDRESS(COLUMN()-26,2))

Drag/copy across the row.

Gord Dibben Excel MVP<<

Thanks Gentlemen. I just tried it, I'm obviously entering it
incorrectly.

In my exact example, in AA1, the formula should look like??

=INDIRECT(B(COLUMN(B)-26,2))

OR

=INDIRECT(B & COLUMN(B)-26)

OR

=INDIRECT("B"&COLUMN()-26)

I keep getting #REF or #NAME depending on Frank or Gord's formula.

John

 

[Frank Kabel]

Hi
just enter the formulas as described in AA1:
=INDIRECT(ADDRESS(COLUMN()-26,2))
or
=INDIRECT("B" & COLUMN()-26)

no nedd to change anything

Frank

 

[Frank Kabel]

Soory, there is one typo in the second one:
=INDIRECT("B:" & COLUMN()-26)

 

[Gord Dibben]

Don't replace the word ADDRESS with B in my version.

Use as written.

Gord

 

[cellblock]

Thanks again guys. I tried it out and it worked after a couple of tries.
I feel like a dummy...and I thought that I knew a little bit about
excel. I can see I haven't even scratched the surface.

Would it be too much to ask exactly what that formula is doing?
It only works when I cut and paste your exact formulas and copy it into
a test worksheet...cell AA1, with B1 through B10 filled in. I guess
that's why I'm now asking about the mechanics of the formula. I'm not
comfortable that I can repeat the process as silly as that sounds. How
come it won't work when I cut and paste that formula in across a row
starting at C1? I'm not seeing how the formula is working. I think my
next stop might be to buy the book advertised on this forum.

Thanks,
cellblock.

 

[cellblock]

Hey gents, I messed around with it a bit more and figured it out! Work
like a champ!
=INDIRECT(ADDRESS(COLUMN()-26,2))

"INDIRECT" means referencing via different axis.
"ADDRESS" is just the formula precursor anchor.
"COLUMN" means that's the function replicated.
"26" is the number of columns away from the originating "address
anchor...in this case, AA1 was 26 columns away. I can change that t
"2" or whatever value I need to account for the lateral location o
placement.
"2"...not sure on that one...must mean replication...or maybe it's
"font" function since Gord's is Brown, and Frank didn't have it and hi
font is Blue. But it works either way.

Thanks again Guys.

John...breaking out of the Cellblock again

 

[Frank Kabel]

Hi John
[snip]

"2"...not sure on that one...must mean replication...or maybe it's a
"font" function since Gord's is Brown, and Frank didn't have it and
his font is Blue. But it works either way.

no it has nothing to do with the font. If you have a look at the syntax
of ADDRESS: ADRESS(row_index, column_index,..)
so the second parameter '2' is the column index. In this case the
second column ant that is column B.

HTH
Frank

 

[cellblock]

Got it. Thanks Frank!

Joh