problem with formulas

[Sathisc]

hi,

i have problem in dragging the formulas

if i give cell reference in a1

for ex =Sheet2!A1 in cell a1 in sheet 1

i couldnt drag the formula to cell b1. If i drag the formula it is
showing as =Sheet2!B1. instead i need it like =Sheet2!A2

But when i drag horizontally the formula is working but i couldnt copy
and paste as it is not working.

Please help

 

[Mike H]

Hi,

Try this

=INDIRECT("Sheet2!A"&COLUMN(A1),1)

Mike

 

[Mike H]

OOPS,

Try this instead.

=INDIRECT("Sheet2!A"&COLUMN(A1))

Mike

 

[Domenic]

hi,

i have problem in dragging the formulas

if i give cell reference in a1

for ex =Sheet2!A1 in cell a1 in sheet 1

i couldnt drag the formula to cell b1. If i drag the formula it is
showing as =Sheet2!B1. instead i need it like =Sheet2!A2

But when i drag horizontally the formula is working but i couldnt copy
and paste as it is not working.

Please help

Try...

A1, copied across:

=INDEX(Sheet2!$A:$A,COLUMNS($A1:A1))

 

[Sean Timmons]

Drag across the columns (the way that works)


then, in A2, enter: =TRANSPOSE(A1:Z1)

copy A2 then, highlight from A2 - A27.

Hit Shift+Ctrl+Enter to enter as an array formula.

 

[Sathisc]

thanks everyone it works perfect