Formula ABSOLUTE with INDIRECT - Is Fill Down Possible?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sam - 27 Nov 2005 22:12 GMT

Hi All,

I have a 5 Column by many Rows Table that has "Sorting" applied to it, the
five individual cells that comprise one Row must stay together when sorted; I
use the Formula below to absolute the relevant cells in each Row. However, I
change the absoluted Row Numbers ("1:1") and ("2:2") individually on each Row.

Is it possible to ABSOLUTE the Rows and Fill Down in this Formula?

=TEXT(ROW(INDIRECT("1:1")),"00 ")&TEXT(ROW(INDIRECT("2:2"))," 00")

Thanks
Sam

Reply to this Message

Peo Sjoblom - 27 Nov 2005 23:34 GMT

This will increment if filled down while staying the same if you insert rows
above, assume you want to put it in row 4 and want 1 and copied down to row
5 it will return 2 and so on,

=ROW()-ROW($C$4)+1

assume your formula is in B2 then you can replace the indirect part with

=TEXT(ROW()-ROW($B$2)+1,"00 ")&TEXT(ROW()-ROW($B$2)+2," 00")

Signature

Regards,

Peo Sjoblom

(No private emails please)

> Hi All,
>
[quoted text clipped - 12 lines]
> Thanks
> Sam

Reply to this Message

Sam - 28 Nov 2005 00:48 GMT

Hi Peo,

Thank you very much for your assistance. Your Formula did the job great.

>assume your formula is in B2 then you can replace the indirect part with
>
>=TEXT(ROW()-ROW($B$2)+1,"00 ")&TEXT(ROW()-ROW($B$2)+2," 00")

Cheers,
Sam

>This will increment if filled down while staying the same if you insert rows
>above, assume you want to put it in row 4 and want 1 and copied down to row
[quoted text clipped - 11 lines]
>> Thanks
>> Sam

Reply to this Message

Sam - 28 Nov 2005 02:17 GMT

Hi Peo,

When I posted and said your Formula worked, it does Fill Down but as soon as
my file is re-calculated it does not keep the sorted data together, it
reverts to its original position before I sorted. Whereas, my original
Formula did keep all the cells of data that belonged to a certain Row
together when sorted (but I manually changed the Row references on each Row
when I used INDIRECT in the Formula).

Further assistance appreciated.

Cheers,
Sam

Reply to this Message