Copy link to offset sheet
 

I have two sheets, and am trying to link cells (copy the same vallue) from
the second sheet into the first. The problem is, the first sheet has 10
blank rows between each link I'm trying to paste in from the seccond sheet.
The second sheet does not have any blank rows between the data.

I'd like a formula that would take into account this offset as I copy the
link on first sheet down. I.E. once I link cell A1 from second sheet to cell
A1 in the first sheet, I want to copy cell A1 in first sheet to cell A11 in
first sheet, but want it to reference cell A2 in the second sheet.

Copy link to offset sheet
 

try:
=INDEX(Sheet2!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

I have two sheets, and am trying to link cells (copy the same vallue) from
the second sheet into the first. The problem is, the first sheet has 10
blank rows between each link I'm trying to paste in from the seccond sheet.
The second sheet does not have any blank rows between the data.

I'd like a formula that would take into account this offset as I copy the
link on first sheet down. I.E. once I link cell A1 from second sheet to cell
A1 in the first sheet, I want to copy cell A1 in first sheet to cell A11 in
first sheet, but want it to reference cell A2 in the second sheet.

Copy link to offset sheet
 

This seems to get close, but can't quite make it work. The second sheet is
actaully called "DATA", so should I replace "Sheet2" with "SheetDATA" to make
it work, or Sheet2 should reference the second sheet no matter what the
second sheet tab is labeled as? Maybe after typing in formula, I can
highlisht "Sheet2" and click on the second sheet's tab to refer it into the
formula.

"JMB" wrote:

try:
=INDEX(Sheet2!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

I have two sheets, and am trying to link cells (copy the same vallue) from
the second sheet into the first. The problem is, the first sheet has 10
blank rows between each link I'm trying to paste in from the seccond sheet.
The second sheet does not have any blank rows between the data.

I'd like a formula that would take into account this offset as I copy the
link on first sheet down. I.E. once I link cell A1 from second sheet to cell
A1 in the first sheet, I want to copy cell A1 in first sheet to cell A11 in
first sheet, but want it to reference cell A2 in the second sheet.

Copy link to offset sheet
 

Since the sheet name was not included, I had to make one up. Try:
=INDEX(Data!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

This seems to get close, but can't quite make it work. The second sheet is
actaully called "DATA", so should I replace "Sheet2" with "SheetDATA" to make
it work, or Sheet2 should reference the second sheet no matter what the
second sheet tab is labeled as? Maybe after typing in formula, I can
highlisht "Sheet2" and click on the second sheet's tab to refer it into the
formula.

"JMB" wrote:

try:
=INDEX(Sheet2!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

I have two sheets, and am trying to link cells (copy the same vallue) from
the second sheet into the first. The problem is, the first sheet has 10
blank rows between each link I'm trying to paste in from the seccond sheet.
The second sheet does not have any blank rows between the data.

I'd like a formula that would take into account this offset as I copy the
link on first sheet down. I.E. once I link cell A1 from second sheet to cell
A1 in the first sheet, I want to copy cell A1 in first sheet to cell A11 in
first sheet, but want it to reference cell A2 in the second sheet.

Copy link to offset sheet
 

Awesome...works perfectly. Thanks!

"JMB" wrote:

Since the sheet name was not included, I had to make one up. Try:
=INDEX(Data!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

This seems to get close, but can't quite make it work. The second sheet is
actaully called "DATA", so should I replace "Sheet2" with "SheetDATA" to make
it work, or Sheet2 should reference the second sheet no matter what the
second sheet tab is labeled as? Maybe after typing in formula, I can
highlisht "Sheet2" and click on the second sheet's tab to refer it into the
formula.

"JMB" wrote:

try:
=INDEX(Sheet2!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

I have two sheets, and am trying to link cells (copy the same vallue) from
the second sheet into the first. The problem is, the first sheet has 10
blank rows between each link I'm trying to paste in from the seccond sheet.
The second sheet does not have any blank rows between the data.

I'd like a formula that would take into account this offset as I copy the
link on first sheet down. I.E. once I link cell A1 from second sheet to cell
A1 in the first sheet, I want to copy cell A1 in first sheet to cell A11 in
first sheet, but want it to reference cell A2 in the second sheet.

Copy link to offset sheet
 

good to hear that worked - thanks for posting back

"SNACK D" wrote:

Awesome...works perfectly. Thanks!

"JMB" wrote:

Since the sheet name was not included, I had to make one up. Try:
=INDEX(Data!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

This seems to get close, but can't quite make it work. The second sheet is
actaully called "DATA", so should I replace "Sheet2" with "SheetDATA" to make
it work, or Sheet2 should reference the second sheet no matter what the
second sheet tab is labeled as? Maybe after typing in formula, I can
highlisht "Sheet2" and click on the second sheet's tab to refer it into the
formula.

"JMB" wrote:

try:
=INDEX(Sheet2!A:A,INT((ROWS(A$1:A1)-1)/10)+1)


"SNACK D" wrote:

I have two sheets, and am trying to link cells (copy the same vallue) from
the second sheet into the first. The problem is, the first sheet has 10
blank rows between each link I'm trying to paste in from the seccond sheet.
The second sheet does not have any blank rows between the data.

I'd like a formula that would take into account this offset as I copy the
link on first sheet down. I.E. once I link cell A1 from second sheet to cell
A1 in the first sheet, I want to copy cell A1 in first sheet to cell A11 in
first sheet, but want it to reference cell A2 in the second sheet.