locked
Validation RRS feed

  • Question

  • Hi All Excel Experts,

        This is an interesting task, but unable to figure out where and how to start.

    Task: Every two weeks we run a ROI report which is based on some other excel spreadsheet as source. Sometimes when we run next ROI report after two weeks it is not consistent with previous version. So my task is to validate the data from old ROI Report and has to highlight the spreadsheet when values of the current ROI Report have 40% more or less with respect to old ROI report which will help us to verify the data.

    Any help is appreciated!


    - please mark correct answers

    Monday, May 12, 2014 2:28 PM

Answers

  • Hi Murali, 

    This is easy to do, if the report cells do not change from week to week.  What I mean is, if is a list of products or projects, do they shift around from week to week, or do they remain a consistent list?  Do you always see Product A, B & C, or do you see Product A, C & D the following week?  If they shift it's more work.  I'll tell how to handle it if the cells don't shift:

    If you make sure the old and new report maintain consistent naming convention and location, you could set it up once and reuse the same Excel workbook each time to capture the differences.  For example every two weeks you could rename the 2 week old report to "ROI Old"  and put a copy of the current report in the same folder and call it "ROI Current".   You will have two files:

    C:\Compare\ROI Old.xlsx

    C:\Compare\ROI Current.xlsx

    Open up a new Excel workbook and save it in the same folder as Compare.xlsx.  Add calculated fields which reference a cell on ROI Old and the corresponding cell on the ROI Current and compare them. 

    Here is a sample calculation referencing the old workbook and the new workbook and checking for more then 40% difference either way.  This will show the difference if it is more than 40%, and will show a blank if it is less.

    =IF(
     OR(
      ('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3 > 0.4,
      ('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3 < -0.4)

    ,('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3
    ,"")

    You can bring in the other cells of the report with just a straight reference, like this:

    ='[ROI Current.xlsx]Sheet1'!$B$2

    I hope this helps you.


    Martina White

    • Proposed as answer by Elvis Long Monday, May 19, 2014 10:02 AM
    • Unproposed as answer by Murali dhar Monday, May 19, 2014 12:31 PM
    • Marked as answer by Murali dhar Wednesday, May 21, 2014 4:05 PM
    Thursday, May 15, 2014 7:12 PM

All replies

  • Hi Murali, 

    This is easy to do, if the report cells do not change from week to week.  What I mean is, if is a list of products or projects, do they shift around from week to week, or do they remain a consistent list?  Do you always see Product A, B & C, or do you see Product A, C & D the following week?  If they shift it's more work.  I'll tell how to handle it if the cells don't shift:

    If you make sure the old and new report maintain consistent naming convention and location, you could set it up once and reuse the same Excel workbook each time to capture the differences.  For example every two weeks you could rename the 2 week old report to "ROI Old"  and put a copy of the current report in the same folder and call it "ROI Current".   You will have two files:

    C:\Compare\ROI Old.xlsx

    C:\Compare\ROI Current.xlsx

    Open up a new Excel workbook and save it in the same folder as Compare.xlsx.  Add calculated fields which reference a cell on ROI Old and the corresponding cell on the ROI Current and compare them. 

    Here is a sample calculation referencing the old workbook and the new workbook and checking for more then 40% difference either way.  This will show the difference if it is more than 40%, and will show a blank if it is less.

    =IF(
     OR(
      ('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3 > 0.4,
      ('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3 < -0.4)

    ,('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3
    ,"")

    You can bring in the other cells of the report with just a straight reference, like this:

    ='[ROI Current.xlsx]Sheet1'!$B$2

    I hope this helps you.


    Martina White

    • Proposed as answer by Elvis Long Monday, May 19, 2014 10:02 AM
    • Unproposed as answer by Murali dhar Monday, May 19, 2014 12:31 PM
    • Marked as answer by Murali dhar Wednesday, May 21, 2014 4:05 PM
    Thursday, May 15, 2014 7:12 PM
  • Hi Murali, 

    This is easy to do, if the report cells do not change from week to week.  What I mean is, if is a list of products or projects, do they shift around from week to week, or do they remain a consistent list?  Do you always see Product A, B & C, or do you see Product A, C & D the following week?  If they shift it's more work.  I'll tell how to handle it if the cells don't shift:

    If you make sure the old and new report maintain consistent naming convention and location, you could set it up once and reuse the same Excel workbook each time to capture the differences.  For example every two weeks you could rename the 2 week old report to "ROI Old"  and put a copy of the current report in the same folder and call it "ROI Current".   You will have two files:

    C:\Compare\ROI Old.xlsx

    C:\Compare\ROI Current.xlsx

    Open up a new Excel workbook and save it in the same folder as Compare.xlsx.  Add calculated fields which reference a cell on ROI Old and the corresponding cell on the ROI Current and compare them. 

    Here is a sample calculation referencing the old workbook and the new workbook and checking for more then 40% difference either way.  This will show the difference if it is more than 40%, and will show a blank if it is less.

    =IF(
     OR(
      ('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3 > 0.4,
      ('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3 < -0.4)

    ,('[ROI Old.xlsx]Sheet1'!$B$3-'[ROI Current.xlsx]Sheet1'!$B$3)/'[ROI Old.xlsx]Sheet1'!$B$3
    ,"")

    You can bring in the other cells of the report with just a straight reference, like this:

    ='[ROI Current.xlsx]Sheet1'!$B$2

    I hope this helps you.


    Martina White

    Thank you sir for taking time and sending me out the detailed explanation. Let me try this and let you know about it.

    Thanks.


    - please mark correct answers


    Thursday, May 15, 2014 8:33 PM
  • Hi Murali,

    Have you solved this issue? Please let us know how things go.

    Thanks,


    Elvis Long
    TechNet Community Support

    Wednesday, May 21, 2014 1:38 AM
  • Hi Murali,

    Have you solved this issue? Please let us know how things go.

    Thanks,


    Elvis Long
    TechNet Community Support

    Works perfect. 

    Thanks.


    - please mark correct answers

    Wednesday, May 21, 2014 4:05 PM