 |
 | 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 / General Excel Questions / March 2008Copy records that are 4 rows apart
Dear reader, I have a bit of a problem copying from one worksheet to another. I need to copy some dates from worksheet B to worksheet A. Currently these worksheets are set up this way WORKSHEET A - A B 1 Part Date 2 xy 3/24/08 Formula used: =(worksheetb!B5) 3 yz (I would like a formula that will skip four rows and copy cell B9 WORKSHEET B (contains the data I need to copy) A B 1 Part Date 2 3 (Cells A2:A5 are merged) 4 5 xy 3/24/08 6 7 (Cells A6:A9 are merged) 8 9 yz 3/25/09 Please help! In cell B2 of Worksheet A put this formula: =INDIRECT("worksheetb!B"&ROW(A1)*4+1) Format as a date, then copy the formula down for as many rows as are required. Hope this helps. Pete > Dear reader, > [quoted text clipped - 21 lines] > > Please help! Hi Pete, I tried this formula, and it keeps saying that the result is "volatile". I even tried changing the A1 logical value from True to False to further experiment, but it still didn't work. The formula is finding the target row, but it is returning a #REF! error. I look forward to any additional input. Cheers, Pablo > In cell B2 of Worksheet A put this formula: > [quoted text clipped - 32 lines] > > > > Please help! I've not come across that message before - are you using XL2007, and is this another one of those "helpful" messages that MS programmers seem prone to use? Changing the value in A1 will have no effect on this formula - the ROW(A1) part returns the number 1, which gets multiplied by 4 and has 1 added on (giving 5). When it is copied down it becomes ROW(A2), ROW(A3), ROW(A4) etc, returning 2, 3, 4 etc, which in turn become 9, 13, 17, which are the rows you want to access. Did you use the correct worksheet name in your formula? If you have spaces in the sheet name then you will have to include apostrophes, like so: =INDIRECT("'worksheet b'!B"&ROW(A1)*4+1) (one after the first quote and one before the exclamation mark). Hope this helps. Pete > Hi Pete, > [quoted text clipped - 50 lines] >> > >> > Please help! Pete, It worked! I inserted the apostrophes and the target column to the new formula. Thanks a lot for your help! This formula really helps us a lot. Cheers, Pablo > I've not come across that message before - are you using XL2007, and is this > another one of those "helpful" messages that MS programmers seem prone to [quoted text clipped - 71 lines] > >> > > >> > Please help! Glad to hear that, Pablo - thanks for feeding back. Pete > Pete, > [quoted text clipped - 89 lines] >> >> > >> >> > Please help! |
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.