Formula ABSOLUTE with INDIRECT - Is Fill Down Possible?
 

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

Formula ABSOLUTE with INDIRECT - Is Fill Down Possible?
 

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

Formula ABSOLUTE with INDIRECT - Is Fill Down Possible?
 

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

Formula ABSOLUTE with INDIRECT - Is Fill Down Possible?
 

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