=INDIRECT Help Needed

=INDIRECT("H"&COLUMN()-5) produces a reference to
a cell in column H, but on the row that is 5 less
than the column number in which the formula has
been entered. Can't imagine why anyone would want
such a reference!

=INDIRECT("J"&COLUMN()-120) will produce a #REF!
error if it's entered into a column to the left
of column DQ.

Hi Mark,

Harry had early asked in msg:
17125.html

about transpositions. Anon answered him with a formula similar to what Harry posted here, the only difference being column A instead of H.

Harry: If you have your Excel help files installed, they are an excellent source for more info on it.

Kevin

I see... but,

Harry might be better off using the TRANSPOSE() function
or using ASAP Utilities ( http://www.asap-utilities.com ).

Thanks guys -

It's a long story . . . The others don't auto update.

Help is good, however, Anon added more into the statement then discussed in the HELP example.

In any case, great site, great FAST info - Keep it up!!!

Re: Thanks guys -

> The others don't "auto update".

Take another look at TRANSPOSE().

Yes, but..........

Disadvantages of using TRANSPOSE in this case instead of the INDIRECT formula :

- the formula would need to be entered into the same number of columns as the number of rows (not necessary with the indirect formula which can be filled/dragged into as many columns as required)

- if the number of rows is changed, the Transpose formula needs to be re-entered (not necessary with Indirect - just drag into more columns or delete as required).

- Transpose is a multi-cell array formula which will produce the dreaded "you cannot change part of an array" if attempts are made to change anything other than the whole array (not so with Indirect which is not an array formula)

Re: Yes, but..........

> the formula would need to be entered into the
> same number of columns as the number of rows
> (not necessary with the indirect formula which
> can be filled/dragged into as many columns as
> required)

Not necessarily so. If A1:A5 contained {1;2;3;4;5},
The formula, {=TRANSPOSE(A1:A100)}, can be entered
into C1:G1 and only {1,2,3,4,5} would be displayed.

> if the number of rows is changed, the Transpose
> formula needs to be re-entered (not necessary
> with Indirect - just drag into more columns or
> delete as required).

If you enter {6;7;8} into A6:A8 and you're
using {=TRANSPOSE(A1:A100)} as mentioned above,
then you just select cells C1:J1, press the F2
key, and then the Control+Shift+Enter key
combination. Not that much more difficult!
Granted this might be daunting to a neophyte,
but I think it's safe to say that they're here
to learn! : )

> Transpose is a multi-cell array formula which
> will produce the dreaded "you cannot change part
> of an array" if attempts are made to change any-
> thing other than the whole array (not so with
> Indirect which is not an array formula)

I've already shown that it's quite easy to extend
the array. If you need to change the array (as
in shortening it) you select the Current Array
using Edit | Go To... | Special, press the F2 key,
and then the Control+Enter combination. You can
now delete cells as you wish. The array can be
restored by selecting the desired range and using
Control+Shift+Enter as before. In fact, this
can be viewed as a "safety" feature. Unlike with
the INDIRECT() approach you can't inadvertantly
delete cells and affect the results of the
transposition. And I might add that with practice
this can be down with nary a thought!

I guess that pretty much covers the advantages
and disadvantages of both approaches. : )