 |
 | 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 / August 2008Increasing cell address by a set amount
Dear All I was orginally helped with a similar problem by Sandy Mann but I have come to grief again. Can anybody give a formula that can do the Business In b8 I have ' date details'!c5 In b10 I have 'date details'!c7 In b11 I have 'date details'!c3 In b14 I have 'date details'!c16 In b88 I wan to have ' date details'!d5 In b90 I want to have 'date details'!d7 In b91 I want to have 'date details'!d3 In b94 I want to have 'date details'!d16 In b168 I wan to have ' date details'!e5 In b170 I want to have 'date details'!e7 In b171 I want to have 'date details'!e3 In b174 I want to have 'date details'!e16 and on and on Thanks in advance for looking B8: =INDIRECT("'date details'!"&ADDRESS(5,(INT(ROW()/80)+3))) B10: =INDIRECT("'date details'!"&ADDRESS(7,(INT(ROW()/80)+3))) B11: =INDIRECT("'date details'!"&ADDRESS(3,(INT(ROW()/80)+3))) B14: =INDIRECT("'date details'!"&ADDRESS(16,(INT(ROW()/80)+3))) and so on  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Dear All > [quoted text clipped - 20 lines] > > Thanks in advance for looking Dear Bob Thank you. This does the Job. So now I am trying learn why. Why +3. The rest I think I have got Best Stew > B8: =INDIRECT("'date details'!"&ADDRESS(5,(INT(ROW()/80)+3))) > B10: =INDIRECT("'date details'!"&ADDRESS(7,(INT(ROW()/80)+3))) [quoted text clipped - 27 lines] > > > > Thanks in advance for looking Column C (3) is your base. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Dear Bob > [quoted text clipped - 36 lines] >> > >> > Thanks in advance for looking Dear bob The answer that you gave me works Perfectly for what I needed which was For the formula to roll on Individual pieces of information on to cells whose address in crease by 80 every time. However it has worked so well it gives me another application for it but I cannot quite get it to work Perhaps you could Help This is the page layout for an Itininary that is 300 daily pages long each cell is repeated in an 49 row increase when the C address changes to an D Address and then 49 more and then an E Address etc etc. Hope I have explained myself well enough In C178 I HAVE ='DATE DETAILS'!C7 IN H178 I HAVE ='DATE DETAILS'!C17 this column runs through to This column runs through to C186 in which is='DATE DETAILS'!C15 H186 in which is ='date details'!C25 This column then restarts on This column restarts on C188 in which is ='DATE DETAILS'!C39 H188 in which is ='date details'!C55 This then runs through to This column runs through to C202 In which is=DATE DETAILS'!C52 H202 in which is='DATE DETAILS'!C68 This column then restarts on C208 In which is='DATE DETAILS'!C70 This then runs through to C220 Inwhich is ='DATE DETAILS'!C82 THANK YOU AGAIN FOR ALL THE HELP YOU HAVE GIVEN > B8: =INDIRECT("'date details'!"&ADDRESS(5,(INT(ROW()/80)+3))) > B10: =INDIRECT("'date details'!"&ADDRESS(7,(INT(ROW()/80)+3))) [quoted text clipped - 27 lines] > > > > Thanks in advance for looking C178: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+7,3)) H178: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+17,3)) C186: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+15,3)) H186: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+25,3)) C188: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+39,3)) H188: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+55,3)) SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Dear bob > [quoted text clipped - 62 lines] >> > >> > Thanks in advance for looking Dear Bob Works on the first page but does not copy and paste on the second page succesfully any thoughts? Stew > C178: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+7,3)) > H178: =INDIRECT("'date details'!"&ADDRESS((INT(ROW()/24)-7)*45+17,3)) [quoted text clipped - 69 lines] > >> > > >> > Thanks in advance for looking Is the second page also meant to refer to 'date details'? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Dear Bob > [quoted text clipped - 86 lines] >> >> > >> >> > Thanks in advance for looking I'm not sure what "and on and on" means, but you could change the lastrow variable to the row number where you want to stop: Option Explicit Sub testme() Dim iRow As Long Dim LastRow As Long Dim FirstRow As Long Dim myCol As Long Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks FirstRow = 8 LastRow = 248 myCol = 2 For iRow = FirstRow To LastRow Step 80 myCol = myCol + 1 .Cells(iRow, "B").Formula _ = "='Date Details'!" & .Cells(5, myCol).Address(0, 0) .Cells(iRow + 2, "B").Formula _ = "='Date Details'!" & .Cells(7, myCol).Address(0, 0) .Cells(iRow + 3, "B").Formula _ = "='Date Details'!" & .Cells(3, myCol).Address(0, 0) .Cells(iRow + 6, "B").Formula _ = "='Date Details'!" & .Cells(16, myCol).Address(0, 0) Next iRow End With End Sub > Dear All > [quoted text clipped - 19 lines] > > Thanks in advance for looking SignatureDave Peterson Dear bob The answer that you gave me works Perfectly for what I needed which was For the formula to roll on Individual pieces of information on to cells whose address in crease by 80 every time. However it has worked so well it gives me another application for it but I cannot quite get it to work Perhaps you or Bob could Help This is the page layout for an Itininary that is 300 daily pages long each cell is repeated in an 49 row increase when the C address changes to an D Address and then 49 more and then an E Address etc etc. Hope I have explained myself well enough In C178 I HAVE ='DATE DETAILS'!C7 IN H178 I HAVE ='DATE DETAILS'!C17 this column runs through to This column runs through to C186 in which is='DATE DETAILS'!C15 H186 in which is ='date details'!C25 This column then restarts on This column restarts on C188 in which is ='DATE DETAILS'!C39 H188 in which is ='date details'!C55 This then runs through to This column runs through to C202 In which is=DATE DETAILS'!C52 H202 in which is='DATE DETAILS'!C68 This column then restarts on C208 In which is='DATE DETAILS'!C70 This then runs through to C220 Inwhich is ='DATE DETAILS'!C82 THANK YOU AGAIN FOR ALL THE HELP YOU HAVE GIVEN Stew > I'm not sure what "and on and on" means, but you could change the lastrow > variable to the row number where you want to stop: [quoted text clipped - 53 lines] > > > > Thanks in advance for looking I can't see a pattern to the addresses/rows that receive the formulas and I can't see a pattern in the cells that send the value. I think you'll have to provide that layout for me to help. Receiving address Date Details address c178 c7 c186 c15 c188 c39 ... Give all the mapping and then say when it starts to repeat. Same with the column layout. And how do you know when to stop populating the rows and how do you know how to stop populating the columns? > Dear bob > [quoted text clipped - 87 lines] > > > > Dave Peterson SignatureDave Peterson I have sent you an e-mail with two attachments Thanks for your time Stew > I can't see a pattern to the addresses/rows that receive the formulas and I > can't see a pattern in the cells that send the value. [quoted text clipped - 104 lines] > > > > > > Dave Peterson my e-mail to you has bounced bac . do you have another address? > I can't see a pattern to the addresses/rows that receive the formulas and I > can't see a pattern in the cells that send the value. [quoted text clipped - 104 lines] > > > > > > Dave Peterson Dear Dave. 1ST Item is the first 3 pages of the 300 page itininary layout after that is the source of the data which is on another work sheet called Date Details. The itininary is on a worksheet called Itininary Hope this helps, and thank you for your time Column c Column H row 174 Date 01/01/2010 Country 42 Day Monday Capacity 52 Town 41 Venue 43 Promoter 53 44 54 45 55 46 56 47 57 Telephone 48 Telephone 57 Fax 49 Fax 59 E-mail 50 E-mail 60 Tech E-mail 51 Teck e-mail 61 Hotel A hotel a Hotel B Hote b 0 c 0 d 2nd last e last f Telephone tel Telephone tel Fax fax Fax fax E-mail email E-mail email Tech e-mail teck Tech e-mail tech e Health Club hc Health Club hcc WIFE wiwf WIFE wifi Room Service rs Room Service rs Restaurant rest Restaurant rest Parking parking Parking park Travel 29 30 31 32 33 34 35 36 37 Comments 38 39 40 5 row 223 Date 02/01/2010 Country 43 Day Tuesday Capacity 53 Town 42 Venue 44 Promoter 54 45 55 46 56 47 57 48 58 Telephone 49 Telephone 58 Fax 50 Fax 60 E-mail 51 E-mail 61 Tech E-mail 52 Tack e-mail 62 Hotel A 0 Hotel B 0 0 0 0 0 0 0 0 0 Telephone 0 Telephone 0 Fax 0 Fax 0 E-mail 0 E-mail 0 Tech e-mail 0 Tech e-mail 0 Health Club 0 Health Club 0 WIFE 0 WIFE 0 Room Service 0 Room Service 0 Restaurant 0 Restaurant 0 Parking 0 Parking 0 Travel 30 31 32 33 34 35 36 37 38 Comments 39 40 41 6 row 272 Date 03/01/2010 Country 44 Day Wednesday Capacity 46 Town 43 Venue 45 Promoter 55 46 56 47 57 48 58 49 59 Telephone 50 Telephone 59 Fax 51 Fax 61 E-mail 52 E-mail 62 Tech e-mail 53 Skye 63 Hotel A 0 Hotel B 0 0 0 0 0 0 0 0 0 Telephone 0 Telephone 0 Fax 0 Fax 0 E-mail 0 E-mail 0 Tech e-mail 0 Tech e-mail 0 Health Club 0 Health Club 0 WIFE 0 WIFE 0 Room Service 0 Room Service 0 Restaurant 0 Restaurant 0 Parking 0 Parking 0 Travel 31 32 33 34 35 36 37 38 39 Comments 40 41 42 Column C D E Line 3 01 January 2010 02 January 2010 03 January 2010 Monday Tuesday Wednesday City 41 42 43 Country 42 43 44 Venue Venue or Day Off 43 44 45 44 45 46 45 46 47 46 47 48 47 48 49 Tele No 48 49 50 Fax No 49 50 51 E-Mail 50 51 52 Teck E-maIL 51 52 53 Capacity 52 53 54 Promoter 53 54 55 54 55 56 55 56 57 56 57 58 57 58 59 Tele No 57 58 59 Fax No 59 60 61 E-Mail 60 61 62 Teck E-maIL 61 62 63 Deal Guarantee Against Percentage Ticket Breakdown Hotels Inc 1 Yes or 0 No Sound and Lights inc 1 Yes or 0 No Internal Transport Inc 1 Yes or 0 No Merchandise Deal 1 Yes or 0 No Backline Included 1 Yes or 0 No Driving Distance from Previous show Hotel Required For Band 1 Yes or 0 No 0 0 0 Hotel A hotel a 0 0 0 0 0 0 0 0 2nd last 0 0 last 0 0 Tele No tel 0 0 Fax No fax 0 0 E-Mail email 0 0 Teck E-maIL teck 0 0 Health Club hc 0 0 WIFI wiwf 0 0 Room Service rs 0 0 Restaurant rest 0 0 Parking parking 0 0 Hotel Required For crew 1 Yes or 0 No 0 0 0 Hotel B Hote b 0 0 c 0 0 d 0 0 e 0 0 f 0 0 Tele No tel 0 0 Fax No fax 0 0 E-Mail email 0 0 Teck E-maIL tech e 0 0 Health Club hcc 0 0 WIFI wifi 0 0 Room Service rs 0 0 Restaurant rest 0 0 Parking park 0 0 Travel 29 30 31 Travel 30 31 32 Travel 31 32 33 Travel 32 33 34 Travel 33 34 35 Travel 34 35 36 Travel 35 36 37 Travel 36 37 38 Travel 37 38 39 Comment 38 39 40 Comment 39 40 41 Comment 40 41 42 > I can't see a pattern to the addresses/rows that receive the formulas and I > can't see a pattern in the cells that send the value. [quoted text clipped - 104 lines] > > > > > > Dave Peterson That's not enough for me to do anything with. Maybe someone else will help. > Dear Dave. 1ST Item is the first 3 pages of the 300 page itininary layout > after that is the source of the data which is on another work sheet called [quoted text clipped - 334 lines] > > > > Dave Peterson SignatureDave Peterson Thank you for your time Best regards Stew > That's not enough for me to do anything with. > [quoted text clipped - 275 lines] > > > > > I'm not sure what "and on and on" means, but you could change the lastrow > > > > > variable to the row number where you want to stop: |
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
|
©2010 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.