Hi folks, I'm trying to link cells between spreadsheets in the same workbook and am having problems, I'm wondering if there are any excel gurus out there who could quickly tell me what I'm doing wrong.

The information to be referenced always comes from the same column, but from each successive row in the first worksheet. But I need to copy it ACROSS columns on the 2nd spreadsheet. So I want to copy cell B4, B5, B6 into C1, D1, E1, etc. When I try referencing $B4 it treats it as though it's absolute and I get B4, B4, B4 across all columns. Argh!

I suspect the problem is directly related to my stress level. Any suggestions?

The offset command would work:

=OFFSET(Sheet1!$B$1,COLUMN()-1,ROW()-1). You need to edit to get it perfect

thanks, that may be a little over my head but I'll give it a try. thanks again!

You can try the easy way too.

Put =B4 into C1. Then put =B5 into D1. Then highlight both C1 and D1 and drag the corner of the selection across E1 F1 etc. Excel is smart enough to figure out what the progression should be and will automatically put =B6, =B7, etc into the next columns.

Just confirmed that as well in Excel.

Another, less elegant way is to put =B5 into some cell away from where you want to work. Then drag it down the column and let it put =B6, etc. automatically in the next few cells (however many you need). Then highlight the whole selection, select CUT (not copy) then right click paste it into D1 and select TRANSPOSE.

Thanks 92_Ag, but I'm still having problems. When I type in b3, b4, b5 etc then drag across, I'm getting b3, b4, b5, e3, e4, e5, h3, h4, h5. The copy and paste method throws up a bunch of #REF! errors. One thing - I'm doing this across spreadsheets in the same workbook so that must have an impact somehow. OR - there's some setting in Excel that's off.

I'll be back in the office tomorrow so will ask one of the gurus there to take a look. I really appreciate your help!

What version of Excel are you using?

=INDEX($B:$B,ROW()+COLUMN(),1)

Put that formula into cell C1 and drag it out to the right across row 1 as far out as you need. That will put B4 into C1, B5 into D1, B6 into E1, etc.

92_Ag, I couldn't get your method to work in Excel 2013.

I would select C1:E1, type the formula =TRANSPOSE(B4:B6), and use Ctrl+Shift+Enter to enter the array formula.

For the simple copy/paste version, this works on Excel 2010:

Enter =$B5 into a cell.

Drag the cell down for as many places as you need. (there should be =$B5, =$B6, etc.)

highlight the cells

Select COPY (I told you wrong with CUT).

Right click on the destination - choose 'Paste Special'

On the popup menu - select 'Paste Special' at the bottom. It will open a selection window.

Check 'Transpose' and hit OK.

If you want to do it via formula, this way works as well:

=INDIRECT(ADDRESS(COLUMN(B1)-COLUMN($B$1)+ROW($B$1),ROW(B1)-ROW($B$1)+COLUMN($B$1)))

substitute starting address wherever you see B1 - be sure to keep the $'s. Enter it in your starting column and drag it across.

This formula works to transpose in either direction (source data in rows to convert to columns or columns to convert to rows).

Thanks folks. After dragging two other co-workers into this, we tried the transpose function two different ways and there are issues with each. The problem is that each linked cell must have an absolute reference to the first cell on the other sheet.

I tried the transpose function two different ways - the first just copied the contents of the original cell (but not a cell reference); the second copied an array so no individual cells there either. So when I go to a cell on the linked sheet, the formula needs to show Projects!$B$4 rather than 402987, or transposeXXXXXXXX.

It's starting to look like the thing to do is to individually link each cell from one sheet to the other - surely there's an easier way. Someone else has suggested VLookup??

Are you trying to have:

Sheet1 B4 = Sheet2 C1
Sheet1 B5 = Sheet2 D1
Sheet2 B6 = Sheet2 E1
etc?

quote:

---

Thanks folks. After dragging two other co-workers into this, we tried the transpose function two different ways and there are issues with each. The problem is that each linked cell must have an absolute reference to the first cell on the other sheet.

I tried the transpose function two different ways - the first just copied the contents of the original cell (but not a cell reference); the second copied an array so no individual cells there either. So when I go to a cell on the linked sheet, the formula needs to show Projects!$B$4 rather than 402987, or transposeXXXXXXXX.

It's starting to look like the thing to do is to individually link each cell from one sheet to the other - surely there's an easier way. Someone else has suggested VLookup??

---

Did you try the INDIRECT formula I posted?