Using = to copy info from a cell to another sheet, but the 2 sheets have different layouts

[mickyd67]

Hi All - this one has been driving me mad.

In Sheet 1 I have a list of 200 item codes. Basically running from Cell A1 to A200

In Sheet 2 I have rows of data related to each of the 200 item codes - but this data set has 9 different elements. So between each item code on the sheet there are 9 rows. Every 10th row has a new item code.

So in Cell A1 I have the formula =Sheet1!A1, In Cell A10 the formula needs to be =Sheet1!A2

However, which will come as no surprise, when I copy the formula down from the above cell it automatically comes up as: =Sheet1!A10.

Is there a way for Excel to automatically reference the next row on my source data to save me from copying the formula down and manually going into each row and changing the formula manually? I am sure there must be a way.

Thanks all

 

[fhqwgads]

assuming the codes go in A10, A20, A30 etc besides the first code in A1 then you can try putting this formula into all of column A in Sheet2

Book4.xlsm
	A
1	1
2	2
3	3
4	4
5	5
6	6
Sheet1

Book4.xlsm
	A
1	1
2
3
4
5
6
7
8
9
10	2
11
12
13
14
15
16
17
18
19
20	3
21
22
23
24
25
26
27
28
29
30	4
Sheet2

Cell Formulas
Range	Formula
A1:A30	A1	=IF(AND((QUOTIENT(ROW(),10)=0),MOD(ROW(),10)=1),INDIRECT("Sheet1!"&"R"&QUOTIENT(ROW(),10)+1&"C1",FALSE),IF(AND(QUOTIENT(ROW(),10)>0,MOD(ROW(),10)=0),INDIRECT("Sheet1!"&"R"&QUOTIENT(ROW(),10)+1&"C1",FALSE),""))

 

[mickyd67]

Hi at

assuming the codes go in A10, A20, A30 etc besides the first code in A1 then you can try putting this formula into all of column A in Sheet2

Book4.xlsm
	A
1	1
2	2
3	3
4	4
5	5
6	6
Sheet1

Book4.xlsm
	A
1	1
2
3
4
5
6
7
8
9
10	2
11
12
13
14
15
16
17
18
19
20	3
21
22
23
24
25
26
27
28
29
30	4
Sheet2

Cell Formulas
Range	Formula
A1:A30	A1	=IF(AND((QUOTIENT(ROW(),10)=0),MOD(ROW(),10)=1),INDIRECT("Sheet1!"&"R"&QUOTIENT(ROW(),10)+1&"C1",FALSE),IF(AND(QUOTIENT(ROW(),10)>0,MOD(ROW(),10)=0),INDIRECT("Sheet1!"&"R"&QUOTIENT(ROW(),10)+1&"C1",FALSE),""))

Hi there.

Firstly - thanks for this. I have no idea how it works (I did try and decipher it) but it works!

So... can I ask for something else please?

Let's say I want to start this in the following tab / cell & look in another tab / cell as follows:

My source data is on a tab called: 02) Lookup Tables
The data starts in Cell F7 on that tab, and runs down the page F8, F9 and so on...

The sheet I want the data to appear on is a tab called: 04) Supply & Demand
The data starts in Cell A4, the next cell down will be A12, then A20 and so on....

If you can chanve what you typed above to meet that criteria I will be one happy Scotsman!

 

[Fluff]

Is this what you mean?

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2
3
4
5
6
7						F7
8						F8
9						F9
10						F10
11						F11
12						F12
13						F13
14						F14
15						F15
16
Lookup Tables

+Fluff 1.xlsm
	A
1
2
3
4	F7
5
6
7
8
9
10
11
12	F8
13
14
15
16
17
18
19
20	F9
21
22
23
24
25
26
27
28	F10
29
30
Sheet3

Cell Formulas
Range	Formula
A4:A30	A4	=IF(MOD(ROWS(A$4:A4)-1,8)=0,INDEX('Lookup Tables'!$F$7:$F$15,INT((ROWS(A$4:A4)-1)/8)+1),"")

 

[Fluff]

Another thing, what version of Excel are you using?

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

 

[mickyd67]

Is this what you mean?

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2
3
4
5
6
7						F7
8						F8
9						F9
10						F10
11						F11
12						F12
13						F13
14						F14
15						F15
16
Lookup Tables

+Fluff 1.xlsm
	A
1
2
3
4	F7
5
6
7
8
9
10
11
12	F8
13
14
15
16
17
18
19
20	F9
21
22
23
24
25
26
27
28	F10
29
30
Sheet3

Cell Formulas
Range	Formula
A4:A30	A4	=IF(MOD(ROWS(A$4:A4)-1,8)=0,INDEX('Lookup Tables'!$F$7:$F$15,INT((ROWS(A$4:A4)-1)/8)+1),"")

This works!!

Many many thanks. Really helpful.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[mickyd67]

Hi all - I have another question in relation to this formula, hoping someone can help:

I'm using the formula like this: =IF(MOD(ROWS(A$8:A8)-1,14)=0,INDEX('02) Safety Level Status'!$B$5:$B$5000,INT((ROWS(A$8:A8)-1)/14)+1),"")

In the tab "Safety Level Status" if someone deletes rows I want the red part of the formula to remain intact.

I've been using 'indirect' in my formulas to stop those values changing - which I got help with on this thread: =SUM formula changing when I delete rows

However I am not sure where to add the indirect formula above. Anyone any ideas?

@Joe4 - wondering if you'll know this one? I owe you a virtual Scotch Whisky at this rate!

 

[Fluff]

What version of Excel are you using?

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

 

[mickyd67]

What version of Excel are you using?

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

Hello - Excel for MS365 (it's on my work laptop)