| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increasing 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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 stew wrote: 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 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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 "Bob Phillips" wrote: 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
Column C (3) is your base.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 "Bob Phillips" wrote: 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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 "Bob Phillips" wrote: 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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 "Dave Peterson" wrote: 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 stew wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 "Bob Phillips" wrote: 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
Dear Bob
Works on the first page but does not copy and paste on the second page succesfully any thoughts? Stew "Bob Phillips" wrote: 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)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 "Bob Phillips" wrote: 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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? stew wrote: 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 "Dave Peterson" wrote: 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 stew wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
I have sent you an e-mail with two attachments
Thanks for your time Stew "Dave Peterson" wrote: 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? stew wrote: 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 "Dave Peterson" wrote: 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 stew wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
my e-mail to you has bounced bac . do you have another address?
"Dave Peterson" wrote: 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? stew wrote: 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 "Dave Peterson" wrote: 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 stew wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
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 "Dave Peterson" wrote: 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? stew wrote: 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 "Dave Peterson" wrote: 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 stew wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
That's not enough for me to do anything with.
Maybe someone else will help. stew wrote: 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 "Dave Peterson" wrote: 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? stew wrote: 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 "Dave Peterson" wrote: 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 stew wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
Thank you for your time
Best regards Stew "Dave Peterson" wrote: That's not enough for me to do anything with. Maybe someone else will help. stew wrote: 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 "Dave Peterson" wrote: 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? stew wrote: 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 "Dave Peterson" wrote: 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: |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing cell address by a set amount
Is the second page also meant to refer to 'date details'?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... Dear Bob Works on the first page but does not copy and paste on the second page succesfully any thoughts? Stew "Bob Phillips" wrote: 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)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 "Bob Phillips" wrote: 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 -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "stew" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Increasing easily the number in a cell. | New Users to Excel | |||
| increasing a cell value on each use | Excel Worksheet Functions | |||
| sales tax total amount from one cell amount to another cell | Excel Discussion (Misc queries) | |||
| increasing a cell by 1 | Excel Worksheet Functions | |||
| increasing and decreasing a value in a cell | Excel Worksheet Functions | |||
Linear Mode
