Formula ABSOLUTE with INDIRECT - Is Fill Down Possible?

[Sam via OfficeKB.com]

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

 

[Peo Sjoblom]

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

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Sam via OfficeKB.com]

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

Hi All,

[quoted text clipped - 12 lines]

Thanks
Sam

 

[Sam via OfficeKB.com]

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

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

Hi All,

[quoted text clipped - 12 lines]

Thanks
Sam