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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1

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" <u4102@uwe wrote in message
news:58019ecb02160@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1

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

Peo Sjoblom wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1

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

Peo Sjoblom wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200511/1