Need a formula or function to link data between sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 17 Nov 2004 07:44 GMT

I input some value say in G5 on sheet1, then using this value and different
formulas to get four different results on say A5, B5, C5 and D5. (All on
same row)

Now I need this values to be transferred to Sheet2 say A10, A11, A12 and
A13.(In one Column)

All this is possible by linking on the respective cell.

Now for the next set of value on Sheet1 G6 .... G100 and its corresponding
values on Sheet1 A6...A100, B6....B100, C6...C100, and D6....D100 need to go
to Sheet2 A14, A5, A16,A17.....and so on

What is the formula to automatically fill Sheet2 along the columnA

I thought that I could use the paste special - transpose and copy down the
column but it doesn't seem to work

Please help

Joe

Reply to this Message

Bill Manville - 18 Nov 2004 00:44 GMT

> What is the formula to automatically fill Sheet2 along the columnA

If I have understood you want
Sheet2!A10 =Sheet1!A5
Sheet2!A11 =Sheet1!B5
Sheet2!A12 =Sheet1!C5
Sheet2!A13 =Sheet1!D5
Sheet2!A14 =Sheet1!A6
Sheet2!A15 =Sheet1!B6
Sheet2!A16 =Sheet1!C6
Sheet2!A17 =Sheet1!D6
etc

In Sheet2!A10 put
=OFFSET(Sheet1!$A$5,INT((ROW()-ROW($A$10))/4),MOD(ROW()-ROW($A$10),4))
and copy down as far as necessary

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message