Link the same row but shift the contents down one row and across one column

[rodeam]

Hi,

I have a simple problem but can't work out a quick way to do this:

Lets say I have two sheets:

Sheet 1: Numbers 1, 2, 3, 4, 5, 6 etc going across the columns
Sheet 2: On this sheet I want to link so that in A1 is 1, A2 is 2, A3 is 3 etc; in B2 is 1, B3 is 2 etc....

In other words, I want to link the same row into excel each time but shift it down one row, and across one column. I know I could link the start of the row each time and drag right but wondered if there was a better method?

Thanks

[Glenn Kennedy]

Use this:

=INDEX(Sheet1!$1:$1,,ROWS($1:1))

copy down until 0s appear

[Glenn Kennedy]

Or, if you want to get rid of the 0s, then use:

=IFERROR(1/(1/Sheet1!INDEX($1:$1,,ROWS($1:1))),"")

[rodeam]

Hi Glenn,

I've attached an excel just to be clear. Will your formula work with this?

Thanks

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional )

[Glenn Kennedy]

Ah, no. But this does...

=IFERROR(1/(1/OFFSET(Sheet1!$E$2,,COLUMNS($A:A)-ROWS($1:1))),"")

[Glenn Kennedy]

I've just seen Post #5. Please DO attend to this request. A suitable title may be "transpose and offset a range of cells"

[rodeam]

Thank you, that's great!

[Glenn Kennedy]

You're welcome. Thanks for amending your title & thanks for the Reputation added.

[FDibbins]

Thanks for the title change

[sanram]

Another attempt without Offset:
On D5 :

Please Login or Register  to view this content.

Now copy down and right.