+ Reply to Thread
Results 1 to 15 of 15

Linking VLOOKUP to further tables

  1. #1
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Linking VLOOKUP to further tables

    Hi Guys,

    Ok the file is attached.

    What I'm trying to achieve is the following;

    1) As you can see there is a drop down list in A4 which references a price by VLOOKUP for B4.
    2) When this happens the boiler is either a Windhager,Baxi etc etc .. and each Boiler has its own set of Accessories as you can see in the Sheet 'LOOKUPS'.
    3) I would like to then be able to bring up a list of the Accessories in B6 but ONLY the ones relevant to the type of Boiler.

    Is this possible ?

    I am wondering if I need to separate out the boiler type from the description and then base it on that ?

    Also presuming I need to use the IF command ?

    Thanks in Advance !!

    Amit
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Linking VLOOKUP to further tables

    Hi
    See attached.
    Hope this is what you need.
    Good luck.
    Tony
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    Hi Tony,

    Thanks a lot for that, but it's not quite what I'm looking for. Basically i probably confused things by the way I laid out the LOOKUPS sheet.

    What I'm trying to do is when a boiler is selected in A4 then the Accessories will automatically access the right list as per boiler. So if you look at the LOOKUPS sheet, Column C is basically a divider between the type of boilers, and the boiler accessories by make.

    So if someone chooses a Baxi boiler for example, then the accessories will only give them access to the Baxi Accessories ....

    Does that make more sense ?

    It's a tricky one I think .....

  4. #4
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Linking VLOOKUP to further tables

    Hope this is what you need.
    JP
    Attached Files Attached Files
    Last edited by Jean.P28; 07-30-2013 at 07:28 AM.

  5. #5
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    JP that is exactly what I needed !!! Thank you very much ...

  6. #6
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    Hi JP

    I tried to PM you earlier but am not sure that I can with my account status, and the message doesn't show up on my PM sent ... did you get it ? Am having a problem with the macro .... Be great if you could help me out !!

    Amit

  7. #7
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Linking VLOOKUP to further tables

    Test this update,The error arose because there were no accessories with MCZ and TRIANCO, I have included these two
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    Hi Jean-Paul,

    Thanks for that, that's it sorted. I'm trying to learn here, but am failing miserably. My quoting process with clients is taking too long, so am trying to put everything in there, and have now got unstuck again with Fluing (which is a similar set up to the Boiler stuff but less info). Trying to decipher the Visual Basic is not doing me any good, so if it's ok with you, once I've collated all the info on the sheet you've just sent me, would it be ok if I upload it here for you to look at ?

    Thanks so much for sorting that last one that's really working great now !

  9. #9
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Linking VLOOKUP to further tables

    You can post your problem and I will have a look at

  10. #10
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    Hi Jean Paul

    Sorry it's taken so long, I've been busy on the job. I have attached the final master file - and the one thing I'm struggling with is Fluing (which is half way down the master page). I've set up a seperate worksheet for it - which you can see at the end. Basically the diameter will be the same depending on the job - once chosen - but I don't know how to reflect that in the options for the other categories. I've laid out the spaces for as many as would be needed per category .... am i missing something obvious or would it be 'if' or macro based again ?

    Help !!


    Query File 3.xls

  11. #11
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Linking VLOOKUP to further tables

    This way you can still attach accessories without having to change something
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    Great thanks so much JP. Hopefully this will make pricing a lot quicker and easier !!

    Blessings

    Amit

  13. #13
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    Hi JP !

    I'm struggling with one hopefully final thing. There seems to have to be something as a value in each of the pull down boxes, in order to not have either '#N/A' or '#VALUE!' in the total boxes at the end - which then means that the totals won't total up and neither will the grand total .... is there a way round this ? Sheet attached ...
    Attached Files Attached Files
    Last edited by amitzala; 08-15-2013 at 07:41 AM.

  14. #14
    Registered User
    Join Date
    07-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2011
    Posts
    15

    Re: Linking VLOOKUP to further tables

    JP I think I've resolved it !!! Using the IF x = "","" - although I don't quite understand what it means it seems to work .... Is that what I need to do ?

  15. #15
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Linking VLOOKUP to further tables

    Answer in the sheet
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Linking Pivot Tables
    By mwedemeier11 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-19-2013, 05:32 PM
  2. Linking pivot tables to one another
    By cmb80 in forum Excel General
    Replies: 8
    Last Post: 10-10-2012, 11:41 AM
  3. linking three tables
    By Edward Masoya in forum Access Tables & Databases
    Replies: 1
    Last Post: 07-23-2011, 08:18 AM
  4. Excel 2007 : Linking Pivot Tables
    By derpotheman in forum Excel General
    Replies: 0
    Last Post: 01-28-2011, 02:31 PM
  5. Help with linking tables
    By shubanacadie in forum Access Tables & Databases
    Replies: 2
    Last Post: 03-01-2010, 11:29 AM

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