Help with pasting cells down a sheet but using a relative reference cell

[praveen23]

need help with something that is prob simple and maybe im just sleep deprived but cant come up with answer

I have approximately 50 of these tables pasted down a sheet. the numbers in the Base and Total tables are a formula based on the numbers in the columns on the left. For example, E4 = 3000 / 1000. The tricky thing is that all the values in the table reference the 1000 number, so I use absolute references for the formula to calculate them. When I copy and paste the tables for the 1000 entry down to the 2350 entry it still references the 1000 bc of the absolute formula. Is there a way to have the tables reference the 1000 but then copy and paste those down to the 2350 table and then reference the 2350?

Base

Total

1000​

Year 3

Year 5

Year 10

Year 3

Year 5

Year 10

I

II

III

IV

I

II

III

IV

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

Base

Total

2350​

Year 3

Year 5

Year 10

Year 3

Year 5

Year 10

I

II

III

IV

I

II

III

IV

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

 

[praveen23]

is OFFSET the best way to do this? then I can localize each cell in the table using offset to that reference cell which will always be the same number of relative rows and columns away?

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is this the sort of thing you mean? E4 is copied down to E8 and then to E13:E17 etc

21 09 01.xlsm
	B	C	D	E
1
2	1000
3				I
4				3
5				3
6				3
7				3
8				3
9
10
11	2350
12				I
13				1.2766
14				1.2766
15				1.2766
16				1.2766
17				1.2766
Formula

Cell Formulas
Range	Formula
E4:E8,E13:E17	E4	=3000/LOOKUP(9.99E+307,$B$2:$B4)