Link to cells plus one row down

boxsterman · Apr 18th, 2002, 10:07 AM

I have data on one sheet in row 1 through 5. I would like to link to this data in 5 separate rows on another sheet. The other sheet where I would like to set up the link from, the data is not in consecutive rows. i.e. row 1, 6, 12, 16 etc. When I copy and paste the reference link it automically assumes that I want to pull data 5 rows down (row 1 to row 6). Is there are way to reference a cell above plus one row down.

Al Chara · Apr 18th, 2002, 10:11 AM

I don't know what you mean. Can you explain it in a different way?

boxsterman · Apr 18th, 2002, 12:45 PM

Information is stored on one page in cells a1:a5.

I want to link to this information on page 2 in cells, a5, A8, A11 etc.

When you link page 2 cell a5 to page 1 cell a1, it doesn't copy down correctly. - copy down to cell A8 and A11 etc. I would like to link non successive cells to successive data. Is this possible.?

Thanks,

Jamie

Mark W. · Apr 18th, 2002, 01:07 PM

1. Enter the formula...

="=Sheet1!A"&ROUND(TREND({5,8},{1,2},ROW()),0)

...into cell A1 of your 2nd sheet and fill down as far as needed.
2. Perform a Copy/Paste Special... Values to replace these formulas with their resultant values.
3. Choose the Edit | Replace... menu command and find = and replace with =.

You can adjust the starting row number and increment by changing the 1st TREND function argument. For example, {8,17} would start at Shee1!A8 and increment by 9. The 2nd TREND function argument should be the first two rows where this formula will be entered on Sheet2. For example, if you want to begin in Sheet2!A5 use {5,6}.

[ This Message was edited by: Mark W. on 2002-04-18 12:34 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 18th, 2002, 10:07 AM	#1
boxsterman Board Regular Join Date: Apr 2002 Location: Toronto Posts: 269	I have data on one sheet in row 1 through 5. I would like to link to this data in 5 separate rows on another sheet. The other sheet where I would like to set up the link from, the data is not in consecutive rows. i.e. row 1, 6, 12, 16 etc. When I copy and paste the reference link it automically assumes that I want to pull data 5 rows down (row 1 to row 6). Is there are way to reference a cell above plus one row down.

Apr 18th, 2002, 10:11 AM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	I don't know what you mean. Can you explain it in a different way?

Apr 18th, 2002, 12:45 PM	#3
boxsterman Board Regular Join Date: Apr 2002 Location: Toronto Posts: 269	Information is stored on one page in cells a1:a5. I want to link to this information on page 2 in cells, a5, A8, A11 etc. When you link page 2 cell a5 to page 1 cell a1, it doesn't copy down correctly. - copy down to cell A8 and A11 etc. I would like to link non successive cells to successive data. Is this possible.? Thanks, Jamie

Apr 18th, 2002, 01:07 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	1. Enter the formula... ="=Sheet1!A"&ROUND(TREND({5,8},{1,2},ROW()),0) ...into cell A1 of your 2nd sheet and fill down as far as needed. 2. Perform a Copy/Paste Special... Values to replace these formulas with their resultant values. 3. Choose the Edit \| Replace... menu command and find = and replace with =. You can adjust the starting row number and increment by changing the 1st TREND function argument. For example, {8,17} would start at Shee1!A8 and increment by 9. The 2nd TREND function argument should be the first two rows where this formula will be entered on Sheet2. For example, if you want to begin in Sheet2!A5 use {5,6}. [ This Message was edited by: Mark W. on 2002-04-18 12:34 ]