Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing the difference between 2 worksheets
I am trying to find a way to compare the values of accounts in 2 worksheets.
One worksheet is the budget while the other worksheet is the actuals. There may be instances where a budgeted amount may not be used. How can we combine the 2 worksheets to find the difference between the 2 worksheets and create a new worksheets showing the difference ? Would appreciate some help from the experts. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing the difference between 2 worksheets
Do we asume you have a list of all accounts?
If so, let's assume worksheet 1 has account in column a and budget in column B Worksheet 2 has account in A, actuals in B. On worksheet 3, =SUMIF(sheet1!A:A,A2,B:B)-SUMIF(sheet2!A:A,A2,B:B) will give the amount under budget you are. If over budget, will be a negative number. "teckkee" wrote: I am trying to find a way to compare the values of accounts in 2 worksheets. One worksheet is the budget while the other worksheet is the actuals. There may be instances where a budgeted amount may not be used. How can we combine the 2 worksheets to find the difference between the 2 worksheets and create a new worksheets showing the difference ? Would appreciate some help from the experts. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing the difference between 2 worksheets
Hi,
If you have a max of one line for each account in both sheets then =Budget!A1-VLOOKUP(Budget!A1,Actuals!A$1:B$100,2,) This will display positive or negative diferences or 0 if identical and N/A if a value is not found in the Actuals sheet. If you don't want to display N/A error messages you could use =Budget!A1-IF(ISNA(VLOOKUP(Budget!A1,Actuals!A$1:B$100,2,)),0 ,VLOOKUP(Budget!A1,Actuals!A$1:B$100,2,)) In this example if the buget number is not found in the actuals the budget number is displayed, you could change that by -- If this helps, please click the Yes button Cheers, Shane Devenshire "teckkee" wrote: I am trying to find a way to compare the values of accounts in 2 worksheets. One worksheet is the budget while the other worksheet is the actuals. There may be instances where a budgeted amount may not be used. How can we combine the 2 worksheets to find the difference between the 2 worksheets and create a new worksheets showing the difference ? Would appreciate some help from the experts. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find a % difference between two numbers (comparing 2005 . | Excel Worksheet Functions | |||
comparing columns to find a difference | Excel Worksheet Functions | |||
comparing worksheet and Marking the difference | Excel Worksheet Functions | |||
Comparing two different Worksheets | Excel Worksheet Functions | |||
Comparing Two Worksheets for changes | Excel Discussion (Misc queries) |