link excel cells in two sheets together

0
votes

I have following problem:

1/ On sheet1 I have cells A1:D1, On sheet2 I have cells A1:D1, etc ... 2/ On sheet0 I want to link these cells from sheet1 A1:D1 to cells A1:A4 2/ On sheet0 I want to link these cells from sheet2 A1:D1 to cells B1:B4

basicaly a column A from sheets 1,2,3 ... LINK to sheet0 as rows 1,2,3

I can create link on sheet0 to differet sheet like this for cell A1 on sheet1:

=sheet1!A1

When I drag and draw columns direction I expect A1 change to B1, then to C1 When I drag and draw rows direction I expect sheet reverence change from sheet1 to sheet2 etc.

Is there any soltion how to modify the relative link so it change direction from columns to rows ?

1
excelvbaexcel-formula

1 Answers

1
votes

If your sheets are actually called:

Sheet1
Sheet2
Sheet3
Etc....

You could try this formula in Sheet0!A1 and drag down and to the right:

=INDIRECT("Sheet"&COLUMN()&"!"&ADDRESS(1,ROW(),4,1))