Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts
    My team is involved in Senior level management reporting about the status of different projects running within the group. We compile data for 40 Projects in an excel file. The data represented is like Project Planned Finish Date, Actual Finish, Planned Project Effort, Actual Project Effort and so on.

    The data is picked up from different excel reports that we prepare. We copy all the reports in a common folder and using a VBA macro, all the required data is picked up from different excel files in the folder and copied in a separate excel file in which each row contains the data for one Project. This excel file is then copied to another template file in which we have pre-defined excel formulaes like vlookup that update the data in relevant columns of the report.

    This has been working fine, however of late we have been receiving updates to the reports released by us with changes (updates) done by the Management to values and these are different from the data in the reports sent by us. Now the problem is that when we repeat the above mentioned process again in the next run of reports preparation (weekly) and using the vlookup function the values get overwritten from the prior week's updates done by the Management.While some values should be overwritten, some values need to be retained from the previous weekly run of reports. The way we are managing is by not updating the vlookup formula for such hard coded values by manually checking the updates using excel compare (=) function.

    Can you suggest some more efficient way. Like Vlookup If .....

    Thanks for your help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You write "While some values should be overwritten, some values need to be retained from the previous weekly run of reports". If the data are tabular, perhaps you could add a column and enter a character in it, for example an "x" if the value in a row should not be overwritten (or perhaps if it should be overwritten, whatever is more convenient). You can then use IF to test whether that column contains an "x".

  3. #3
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, I think this will work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •