Increasing cell address by a set amount

S

stew

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
 
B

Bob Phillips

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)
 
D

Dave Peterson

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
 
S

stew

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
 
B

Bob Phillips

Column C (3) is your base.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

stew

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
 
S

stew

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
 
B

Bob Phillips

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)
 
S

stew

Dear Bob

Works on the first page but does not copy and paste on the second page
succesfully

any thoughts?

Stew
 
D

Dave Peterson

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?
 
S

stew

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
 
D

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
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
Click to expand...
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with a VBA subroutine 0
How to replace a constant from a cell containing date and time 1
Date stamping a cell change 3
query to show where no data in a stated column 0
Send email to a email address 7 days before the due date 1
Multiple logic functions with multiple sums 2
Multi level grouping in a report 2
Seeking a 'special' calendar template 1

Top