How can I create a horizontal link to a vertical cells in another work sheet?

How can I create a horizontal link to a vertical cells in another work sheet?

I have sheet A and sheet B.

Sheet B has a column.
I want this to create a link to Sheet B in Sheet A - I can do this for individual cells, by doing:

=SheetB!A1

for example.

But: how do I copy one column and and apply it a row in the other spreadsheet?

I've read up, and can do this for values by using 'Past Special' and choosing 'Transpose'.

I don't cant to copy just the values: I want to create a link, so that if cells change in SheetB, then they change in SheetA.

Apart from doing this manually: I can't figure out how else to do it.

Thanks.


Omar

paste special /formulas/ transpose
formulas must be in format =Sheet3!$B$9 to keep cell referencing

hmmm been there done that. not working.

the cells i want to copy have formulas in the them.
when i do: special past -> formulas -> transpose...
all that happens is that the formula in the cells get copied.

i wasnt too sure what u mean when u said:

"formulas must be in format =Sheet3!$B$9 to keep cell referencing"

in the second sheet, the formulas in the cells i want to have reference to have simple formulas like =a5*b5.

let me know if u can help further.

thanks.

there are probably lots of clever ways but
open this sheet and from it
to save time create a custom list
tools /options /custom list
click in import list from cells
click on column a
click import
now if you put sheetx!$a$1 in a1 and then drag down
it will increment
sheetx!$a$1
sheetx!$b$1
sheetx!$c$1
sheetx!$d$1
sheetx!$e$1

you can then use find and replace to create your formula
eg find sheetx
replace
='my sheet name'
or
=sheet2
and so on
ugly but it works