Calculation of multiple sheet's value in Excel and use of Paste Link

 

Paste Link: This option establishes link between source and target. If we modify value of source range then target range will be automatically updated.

Multiple Sheets Calculation: When calculate another sheet's value on current sheet then we must use Sheet  Name along with exclamation symbol(!) before cell reference.

Procedure:

1: Open Excel

2: Type following fields on Sheet1

    EmpName, BP, and NP

3: Now insert Sheet2 and type following fields

    EmpName, BP, DA, HRA, IT

4. Calculate DA, HRA, and IT using following expression

   DA=50%, HRA=40%, and IT=5% of BP if BP>=12000 otherwise DA=30%, HRA=20% and no any tax.

5: Now Click on Sheet1, select A2 to B12, and press Ctrl+C (Copy)

6: After then select Sheet2, acquire A2:B12

7: Now click on Paste Option and select Paste Link option.

8: Now select C2 to E2, place mouse pointer on bottom right corner of selected area.

9: When + symbol will appear then press left mouse button and drag it up to E12.

10: Now select Sheet1 and calculate NP. 

11: Now hide Zero value from both sheets using following steps.

Fileà Optionà Advanceà Uncheck Show a zero in cells that have zero value.

When write value in Sheet1 then Sheet2 will be automatically updated.