How to become abstract of salary

[paramveer]

Hi Sir,
Please solve my problem
In my Job, i make salary bills of about 110 employee in excel
& also do the function of calculation of income Tax.
I use a bill file named "bill 1" in folder name "3" which relates to March Month
similarly In next month i use folder "4" i.e. April salary with similar named
Here is my problem Now i create a file (in example) in which i want to put brief detail of whole year salary of particular employee by using "VLOOKUP" formula
but this file become very heavy and hang the excel as well as my computer.

What can i do
If you give solution with example it become more helpful for me

 

[NARAYANK991]

Hi Paramveer ,

Can you confirm whether you are using Excel 2003 ?

Narayan

 

[paramveer]

Hi Narayan,

No sir, I am using Microsoft office 2013

 

[NARAYANK991]

Hi Paramveer ,

In that case , as a first step , replace all the formulae which at present are of the form :

=IF(ISERROR( formula ) , 0 , formula)

with this :

=IFERROR(formula , 0)

Straightaway , you should see some improvement , since Excel does not have to calculate the formula twice.

The IFERROR function was introduced only in Excel 2007. Thus it is available in that and all later versions.

Narayan

 

[paramveer]

Hi Narayan,

Sir it doesnot work it show false

 

[NARAYANK991]

Hi Paramveer ,

Here is an example.

Your formula in cell B6 is :

=IF(ISERROR(VLOOKUP(J1,'D:\Paramveer Singh\SALARY\3\[Bill No. 1.xlsm]Bill'!$D$4:$AB$135,2,FALSE)),0,(VLOOKUP(J1,'D:\Paramveer Singh\SALARY\3\[Bill No. 1.xlsm]Bill'!$D$4:$AB$135,2,FALSE)))

This can be rewritten as :

=IFERROR(VLOOKUP(J1,'D:\Paramveer Singh\SALARY\3\[Bill No. 1.xlsm]Bill'!$D$4:$AB$135,2,FALSE),0)

Narayan

 

[NARAYANK991]

Hi Paramveer ,

In fact , since you are looking up the same value and returning the corresponding value from various other columns , the correct functions to use would be the INDEX & MATCH combination ; use the MATCH function to return the matching location in a helper column , and then use this value to return the corresponding value from the respective columns using the INDEX function.

Narayan