Software·43,776 discussions

Excel Formulas

Locked

I have a large excel spreadsheet which I want to apply the round formula to multiple cells.

For instance I have a table with linked figures divided by 1000.
i.e: in Sheet 1 cell B2 I have: “=(‘Sheet2’!B2)/1000)”

I want to have “=ROUND((‘Sheet2’!B2)/1000,0)”

Is there a way to apply this to multiple cells without manually typing the formula in to each of the hundreds of cells?

Topic

Clarifications

In reply to Excel Formulas

Clarifications

Copy and paste

In reply to Excel Formulas

works fine for formulae. It will update the cell reference

Neil 🙂

Sort of works

In reply to Copy and paste

I am able to copy and paste the formula only if the cells in the linked sheet are adjacent.

For instance:
If the cell values in column B are:
=’Sheet2′!J75
=’Sheet2′!J76
=’Sheet2′!J77
An the cell values in column C are:
=’Sheet2′!K75
=’Sheet2′!K76
=’Sheet2′!K77

Copy&Paste works.

However if the values are as below, copy and paste results in the same outcome as with the numbers above:
in column B:
=’Sheet2′!J75
=’Sheet2′!J76
=’Sheet2′!J78
in column C:
=’Sheet2′!L75
=’Sheet2′!L76
=’Sheet2′!L78

Any suggestions?

Thanks!

[Author]

Replies