+ Reply to Thread
Results 1 to 7 of 7

Multi-Sheet, Multi-Table Vlookup possible?

  1. #1
    Registered User
    Join Date
    09-10-2010
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Wink Multi-Sheet, Multi-Table Vlookup possible?

    Howdy,

    I been scaling the internet trying to figure out a way that doesn't involve basic. Hopefully it's possible. I was goofing around when an excel ideal came into my mind, yeah pretty boring life. Wasn't sure if the following was possible?

    Ideal based on a game where you build items depending on the resources available.

    I have a list of requirements for each item. The goal of the table would be to enter in the current resource available, in this case wood, stone, iron and crops. Using this information, I would do either a vlookup or some other function to determine what the most of that resources i was able to make. Kinda of like a greatest common denominator type problem.

    I've attached the file for you to see what I did. If it needs a complete re-engineering let me know. There are no macros, so if it ask you to allow macros, disasble, plus it's not saved as a macro enabled xls file.


    Thanks for your help.
    Attached Files Attached Files
    Last edited by cte; 09-11-2010 at 11:19 AM. Reason: solved

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi-Sheet, Multi-Table Vlookup possible?

    Not entirely sure I follow (expected results often help) but perhaps the below?

    Please Login or Register  to view this content.
    The above would return the most of any given type that could be produced given resources.

    Note this uses your table for 1 unit - and your tables imply no economies of scale (ie no marginal costing to worry about)

    If you want the best combination of all items that can be produced with the resources (ie the best mix of the four items) then I think you would need something much more sophisticated than the above
    (and perhaps assigned a preference weighting to each item)

  3. #3
    Registered User
    Join Date
    09-10-2010
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Multi-Sheet, Multi-Table Vlookup possible?

    Good point about example of expected result.

    Let's say I have the following resources: Wood - 3820; Stone - 3200; Iron - 5880; and Crop - 1540
    I want to find out what is the most I can get out of these resources. In this case, the formula (it may not be a vlookup) would return the following (Master!F4:Master!I4): Infantry - 31, Horses - 5, Archer - 12, Catapults - 2;

    I am getting these numbers by using the sheets. The basic resource requirements, which is for one unit, is based on the table located Master!D9:Master!H14.

    Hope that clarifies things. Maybe it just confuses things more.

    I would like to take this concept into use this model in other scenarios.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi-Sheet, Multi-Table Vlookup possible?

    Quote Originally Posted by cte
    Let's say I have the following resources: Wood - 3820; Stone - 3200; Iron - 5880; and Crop - 1540
    I want to find out what is the most I can get out of these resources. In this case, the formula (it may not be a vlookup) would return the following (Master!F4:Master!I4): Infantry - 31, Horses - 5, Archer - 12, Catapults - 2;
    That's what the prior suggestion gives you....

  5. #5
    Registered User
    Join Date
    09-10-2010
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Multi-Sheet, Multi-Table Vlookup possible?

    Thanks, DonkeyOte, would it be possible to explain the concept behind the formula?

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi-Sheet, Multi-Table Vlookup possible?

    For each "item" the formula conducts 4 calculations, 1 per resource.

    The calculation divides the resource value by the appropriate per unit cost of that resource for that item

    The resulting values are rounded down to the nearest whole number (via INT)

    It follows that the most you can have of a given item is determined by the lowest whole number returned by the division calculations (ie the least whole units that can be generated given resourcing level and per unit cost)
    As such we use MIN to pull the lowest of the 4 results.

    If we use your sample file and look at Infantry - you have resource values of:

    Please Login or Register  to view this content.
    and the per unit cost for Infantry is

    Please Login or Register  to view this content.
    so if we divide the values as described we get:

    Please Login or Register  to view this content.
    which we round down (via INT)

    Please Login or Register  to view this content.
    we then apply MIN to the above array of values

    Please Login or Register  to view this content.
    and that number represents the most Infantry we can create given our resource values.

  7. #7
    Registered User
    Join Date
    09-10-2010
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Multi-Sheet, Multi-Table Vlookup possible?

    Thanks

    Wow, I was looking at this from hold different angle. This makes more sense then my crazy ideal. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1