Hi;

I'm using the "indirect" function to duplicate my contents from 1 sheet to
another worksheet. The formula that I'm uisng is :

=LEFT(INDIRECT("Sheet1!A30"),50)
=LEFT(INDIRECT("Sheet1!B30"),50) and so on.

Can anyone guide me on how to copy the formula to change the ref based on
the row number to the whole worksheet?
When I try to drag the formula, the system will copy the same formula for me
throughout the page. What I want is that:
For cell A30, it appears as =LEFT(INDIRECT("Sheet1!A30"),50)
While for cell A31, it should apperas as =LEFT(INDIRECT("Sheet1!A31"),50)

Please advice

This should work:

=LEFT(INDIRECT("Sheet1!A"&ROW()),50)

?B?YWg=?= wrote in
:

Hi;

I'm using the "indirect" function to duplicate my contents from 1
sheet to another worksheet. The formula that I'm uisng is :

=LEFT(INDIRECT("Sheet1!A30"),50)
=LEFT(INDIRECT("Sheet1!B30"),50) and so on.

Can anyone guide me on how to copy the formula to change the ref based
on the row number to the whole worksheet?
When I try to drag the formula, the system will copy the same formula
for me throughout the page. What I want is that:
For cell A30, it appears as =LEFT(INDIRECT("Sheet1!A30"),50)
While for cell A31, it should apperas as
=LEFT(INDIRECT("Sheet1!A31"),50)

Please advice