Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2008

Tip: Looking for answers? Try searching our database.

Block Of Numbers Repeating in a Formula

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
colemanj4 - 28 Jan 2008 21:22 GMT
Hello,

I am trying to use the OFFSET($A$1, 1, 2) command. What I want to do
is to make the row count up 1-20 as I go down my column, but I need it
to do that about 20 times, repeating that block. I have tried to get
it to repeat, but I cannot.

If you need more clarification, please ask for it. Thank you for you
help.
Rick Rothstein (MVP - VB) - 28 Jan 2008 21:37 GMT
If I understand what you want to do correctly, you don't need the OFFSET
function. If you are starting you block counting in Row 1, then use this
formula and copy it down...

=1+MOD(ROW()-1,20)

If you are going to start in a row other than Row 1, then change the -1
to -rownum where you would use the starting row number in place of rownum.
So that, if you were starting your count in Row 4, then you would put this
in that cell and copy down....

=1+MOD(ROW()-4,20)

These formulas will preserve the correct numbering if you insert rows later
on (although you would have to copy the formula from one of the adjacent
cells to preserve the continuity of the formulas).

Rick

> Hello,
>
[quoted text clipped - 5 lines]
> If you need more clarification, please ask for it. Thank you for you
> help.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.