 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007Increment Forumula Series by 4 each row
I have a set of forumulas that I want to drag down an Excel Sheet and have the Cells increment by 4 each time rather than 1. I can't find an automated way to do this in Excel so I'm having to hand edit each formula line. Here is an example forumula and the next one that I'd like incremented by 4 Cells: =IF('Message Details'!C93 <= 0,"",'Message Details'!C93) I want the next Cell below this one to be: =IF('Message Details'!C97 <= 0,"",'Message Details'!C97) But if I use the Drag AutoFill the formula's only incrment by 1. Any ideas how to fill a column that increments by 4 on each of the Cells used for Calculation? Thanks, DevDave =whatever*ROW(A1)*4 & drag down  SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com >I have a set of forumulas that I want to drag down an Excel Sheet and > have the Cells increment by 4 each time rather than 1. I can't find an [quoted text clipped - 17 lines] > Thanks, > DevDave That didn't seem to work - but I'm sure there is some way... > =whatever*ROW(A1)*4 > & drag down [quoted text clipped - 24 lines] > > Thanks, > > DevDave modify to suit. Obviously, won't work for the FIRST one. =IF(INDIRECT("sheet22!a"&ROW(A1)*4)<=0,"",INDIRECT("sheet22!a"&ROW(A1)*4)) SignatureDon Guillett SalesAid Software dguillett1@austin.rr.com > That didn't seem to work - but I'm sure there is some way... > [quoted text clipped - 26 lines] >> > Thanks, >> > DevDave Don was saying to use this formula (I modified it) & fill down: =IF(OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0) <= 0,"",OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0)) Bob Umlas Excel MVP > That didn't seem to work - but I'm sure there is some way... > [quoted text clipped - 26 lines] > > > Thanks, > > > DevDave =OFFSET(A1,ROW(A1)*3-3,0) bob i think *3-3 be more correct on the skipping part.... aloha n happy new year, orbii > Don was saying to use this formula (I modified it) & fill down: > =IF(OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0) <= 0,"",OFFSET('Message [quoted text clipped - 34 lines] >> > > Thanks, >> > > DevDave Bob - This: =IF(OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0) <= 0,"",OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0)) Works Great. I guess I don't understand how 'offsets' work as I don't know what "Row(a1)*4-4,0" is doing that makes this work. But it is producing the correct offset and result from the other worksheet. > Don was saying to use this formula (I modified it) & fill down: > =IF(OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0) <= 0,"",OFFSET('Message [quoted text clipped - 33 lines] > > > > Thanks, > > > > DevDave So I went and looked at what OFFSET does and what ROW does and this all makes perfect sense now! You would think that with Excel - there would be an easier way to OFFSET based on a Selected Series that increments in a set pattern, but this worked fine :) Thanks for all your input. DevDave > Bob - > [quoted text clipped - 47 lines] > > > > > Thanks, > > > > > DevDave Here's another way ... which is non-volatile. This procedure also *doesn't* lend itself to a description of being easier, but it can be perhaps more intuitive, if you just examine the math involved =IF(INDEX('Message Details'!C:C,4*ROWS($1:23)+1)<= 0,"",INDEX('Message Details'!C:C,4*ROWS($1:23)+1)) And copy down as needed. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > So I went and looked at what OFFSET does and what ROW does and this all > makes perfect sense now! [quoted text clipped - 57 lines] > > > > > > Thanks, > > > > > > DevDave
|
Reply to this Message |
 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.