Applying a function to all used cells in another sheet

[Amjad]

How can I display double the numbers in cells of Sheet1 in
corresponding cells Sheet2?

Normally I would type the formula in Sheet2 and manually
drag the cells down to include all the used cells in
Sheet1. (e.g. =Sheet1!A1*2 )

I want my formula to be automatically applied to all the
used cells in Sheet1, but I don't know how many of them
are there besides I don't want to display zeros for the
empty cells in Sheet1. Any help?

 

[Ken Wright]

In cell A1 on your second sheet, put:-

=IF(Sheet1!A1<>"",Sheet1!A1*2,"")

and copy across and down your sheet 2

 

[Amjad]

"copy across and down your sheet 2" is a manual operation.
I'm wondering if there is an automatic way of detecting
the number of used cells in Sheet 1 and applying the
formula to all corresponding cells in Sheet 2 everytime
the workbook is opened?

 

[Ken Wright]

It's only manual once, and you simply need to go past whatever is likely to be the max range. The
,"" bit of the formula will stop any cells appearing with anything inside them when there is no
corresponding data in the cell in the other sheet.

Assume you may get data anywhere in the range A1:H100, just throw the formula across A1:Z1000 and
you will be well covered. Obviously more formulas carry a price tag in terms of efficiency, but
depending on what you are doing it could well be negligible.

Where cells are blank in Sheet 1, they will also appear blank in Sheet 2