Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Compare two sheets for a specific lookup value and a parameter associated with the lookup value and confirm whether the associated value matches

0
User action Action taken/Value returned/comparison result Description Select first sheet to compare Master sheet User is prompted to select the first sheet to compare. A drop down or search option prefered. There could be hundreds of sheets. In this case I have specified only 3 sheets. Select second sheet to compare Coverage User is prompted to select the second sheet to compare. A dropdown opti or search option prefered Lookup  Sam Here the name "Sam" is being looked up in Master sheet and Coverage sheet Comparison parameter  Team size The parameter to be compared for "Sam" is being defined here Value returned from first sheet 7  The value  of Team size for Sam in first sheet selected should display here Value returned from second sheet 7  The value of Team size for Sam in second sheet selected should display here Result Match or Does not Match Based on whether it is an exact match or not the output should display in E9

There could be 100s of sheets in a given workbook. At any given point of time only two sheets will be compared. user to be prompted to select the two sheets that need to be compared. Then the user should be prompted to specify the lookup value or text from sheet 1 that needs to be looked up in sheet 2 for the parameter specified by user. For eg: "Team size"

Value returned from first sheet

Value returned from second sheet

The values will be returned from the two sheets specified by the user.

Result will be compared and it will be confirmed whether the values are match/Do not match

Challenge:

We only know that the lookup value and the parameter exist in both the sheets. However, the exact location may not be know.

Sample file is attached. I tried using DGET() formula but the approach is too manual. Looking for a much more automated approach where user input is gathered automatically for Sheetnames to be checked, Lookup value and parameter. 

Answer
Discuss

Answers

0

Sorry to pour cold water on your effort but you are going about this in the wrong way entirely.

  1. Project structure
    The first step in any project is the design of the output sheet in great detail, right down to the cell format and certainly(!) the exact location of each cell, hopefully not to be changed thereafter.
    Then do the same for the user interface, if - and that is highly likely for your project - the UI is different from the output sheet.
    Finally, design the template of the look-up sheets. Since you want 100's they should all have the same format. If they have different formats design one of each type. Make sure that they can be duplicated.
    None of the above can be done without having a clear picture of how you want to operate the system (work flow, click by click). In fact, you have developed some thoughts about the operation but they are coarse by nature. They get refined while you design all the sheets and the UI (and later, again, when you move to automation.
    Automation is the last and final step. Don't start thinking of DGET or VLOOKUP until all the above work has been done. Of course, that isn't quite true either because you need to design your sheets so that functions can work in and on them. Take courage from the fact that there are so many functions in Excel, with such great capabilities, that solutions can be found for any reasonable sheet and UI design. So, focus on the design quality.
  2. Project leadership
    Unless you wish to employ a programmer there can be no question but that you should lead the project. Therefore you must not only design the size and shape of each little component yourself but also enable and supervise its integration into a bigger picture. You must be both architect, main contractor and sub-contractor, design the outside and inside of your building, go to the DIY market, buy a socket and install it in the corner of the proposed bedroom. You can't afford not to know anything. The hard part of that is that you still don't know what there is to know about. That will only come with hard work.
  3. Getting help
    Avoid the project manager's trap of "I don't know how to do it but I can tell you what to do". Bring your project to the point that you can ask a question like, "How can I fetch the number in column Coverage!D:D where Coverage!C:C matches the selection in [Check Sheet]!F7 and display it in [Check Sheet]!G7?"
    For this kind of question you will find answers here and elsewhere. You will get one formula which you can deploy and that brings you one inch closer to your goal, except that it doesn't because the [Check Sheet] doesn't have a final design and neither does Coverage. Avoid questions where, in effect, you seek to construct a centre before the ends are firmly anchored.
    Long before you get to the question of which formula to use a decision will be required over whether Coverage should contain a table or not, and how to enter data in that sheet or its table. The advantage of TeachExcel to you is that you will be able to ask questions about how to design the lookup sheets/tables. The disadvantage of your approach is that you never find these questions until your project has advanced well beyond them and you need to restart of scratch or live with a cripple.

To finish pouring the water: which is the question I could answer in order to help you get your project ever so little nearer to its fruition?

Good luck!

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login