How to use other sheet in workbook for calculation?

[Stork]

Hi, I need some pointers of how to get on with this.

I want to take input from one (perhaps later two) cells in a row, use them to make a calculation in a different sheet and return the result to the next cell in the row of the first sheet; this for a large number of rows.

The calculation is of income tax (in Portugal), and for maintenance and overview I prefer to have it in a separate sheet. I did something like this in Excel over 20 years ago but don’t quite remember how.

Anyone can give me ideas of which tools to use?

[Zizi64]

You can reference the cells of another shets by the Sheet name
In the a cell of the Sheet1:
=$Sheet2.A1*$Sheet3.A1
Reference another sheets.ods (7.9 KB)

As I remember it, the Excel used ! sign after the Sheet name. AOO and LO uses a dot.

[Stork]

Thanks for your suggestion, I was probably not clear enough.

I attach a simplified worksheet to illustrate:
IRS_estimation_model.ods (21.2 KB)
The idea is to take the values from Income.column_E, pass them to IRS:F2, read the results in IRS:F33 and write them in Income.column_F.

Does that make sense?

[JohnSUN]

Ah, you must be talking about Data - Multiple Operations?

[Stork]

Getting closer!
But this does not seem to work between different sheets.
Also, I had to rewrite the formula to one cell instead of using the multiple steps I had already.

[JohnSUN]

@Stork

this does not seem to work between different sheets

Well @Stork, this is not a big problem - add two cells to the current sheet: one of them (the original number) will be referred to by F2 on the IRS sheet, and the second will refer to the result from F33. Copy twice and Paste Special as Link. And to the Master of multiple operations to specify these two new cells.

I’m more concerned that the results may differ from the correct ones - you should carefully check the resulting table. However, if you have removed a bunch of intermediate calculations and reduced everything to a complex, but only one formula, then perhaps you will get the result today.

[Stork]

Thanks for the suggestion.

I made the formula in the IRS sheet and could check against the result from the original calculation.

It is possible to refer to field in a different sheet in multiple operations, I just had to edit manually to insert the sheet reference as that was left out when using point and click.