1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Further Excel Formula Help

Discussion in 'Business Applications' started by Cheekywink, Nov 8, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Cheekywink

    Cheekywink Thread Starter

    Joined:
    Jun 5, 2008
    Messages:
    69
    I need help setting up a new formula to auto populate a field with a specific drop down list based upon an initial selection from a drop down list.....if that makes any sense.

    E.G

    Column A has a drop down list of 10 items (lets call them 1-10 for this), I have it set so that when any of these options is selected a 'Cost Price' (column B) box auto populates from a master template sheet, and this formula working fine now.

    I now want to add a colour section 'column C' which is also a drop down list based on the selection made in column A but depending on the item depends on the colour available.

    E.g Column A, drop down options 1-5 have 5 different colour options from a drop down list, Options 6+7 have 3 colour options, 8+9 have one colour option and option 10 has 2 colour options.

    I have saved lists for individual items and presume it will be the VLOOKUP command but it's about there I grind to a halt.


    Think thats about as well as I can explain at the moment, if required I can post an example document to display exactly how it looks.

    Cheers guys
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,910
    First Name:
    Wayne
    an example of what you want in a document is usually useful
     
  3. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    Warning: Head about to explode. Please vacate the vicinity.

    I think this is just about possible, but the resulting code will not be pretty. I'm working out ways to hack around the limitations of the software. At some point quite soon this whole arrangement will become so complex that it would be better to aim for a database solution, but let's do it in Excel for now. An example sheet would be useful though.
     
  4. Cheekywink

    Cheekywink Thread Starter

    Joined:
    Jun 5, 2008
    Messages:
    69
    Haha gluttons for punishment, I like it, but seriously I'm learning this as I go along so if i'm trying to break the laws of physics or time or anything else that can't realistically be done let me know. At least I'll know the limitations of Excel for future....then you can all help me on database setups :).

    Anyway back to business, I've attached the 2 pages I am trying to link and notes on there to show what I want to do, and example of my poor attempts. If it can be done that would be awesome but don't worry if i'm pushing it too far.
     

    Attached Files:

  5. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    Running up to the speed of light, so physics or time will have to blow soon :). Actually I love being pushed into my limits or the limits of the software, which is one of the reasons I'm on this site in the first place. I am pretty sure that this can be done.
    Excel does offer plenty of spare capacity in the form of Macros, but I don't want to go there. Basically my philosophy is that if you have to write a macro to make a program do what you want, you're using the wrong program. More to the point, they cause problems.

    I'm thinking that the best bet open is the Offset and Index functions, but I have never actually had cause to use them myself either. More annoyingly I've temporarily lost access to the copy of excel I was using to test my code. I think I'll have to sign off for the night and tell you of any solutions tomorrow. Unless, that is, a real Excel Guru turns up in a TARDIS and solves it for you in the meantime.
     
  6. Cheekywink

    Cheekywink Thread Starter

    Joined:
    Jun 5, 2008
    Messages:
    69
    haha no worries, cheers for the devotion anyway, oh and if you really like challenges I may be looking at adding a description tab in too at some point, but the way my head hurts at the moment think i'll settle for what i've got for at least a few weeks.

    Suppose my =VLookup strategy wasn't even in the same ball park :p
     
  7. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    OK. Irony was I had the fancy code worked out perfectly and tested, then my Vlookup decided to go on strike. That's why it took so long. OK, the other reason is that I had to go to sleep and go to school between starting and finishing.

    Anyway the setup I've used is a third column, alongside the name of the object and its Price, titled Column.
    Then elsewhere on that data sheet I've arranged the item names along the top, and beneath each a list of the colours they can be (Note: I'm not a fashion designer, OK!).
    The drop down box checks the relevant column using the code
    Code:
    =OFFSET(Sheet2!$E$2:$E$7,0,VLOOKUP(A3,Sheet2!$A$2:$C$13,3,FALSE))
    This (via the VLookup) checks the Column number associated with the item and moves that number of columns to the right from E2:E7.
    As promised, the code is not pretty.

    I'll post my sheet as an example.
     

    Attached Files:

  8. Cheekywink

    Cheekywink Thread Starter

    Joined:
    Jun 5, 2008
    Messages:
    69
    Hi Ent,

    Cheers for the code......but, i can't get it to work. :(

    I've added in my 2 worksheets to your attachment to see if you can highlight where i've gone wrong (probably starting this :p)

    Code I produced was. =OFFSET('Master Template'!J3:J8,0,VLOOKUP(A3,'Master Template'!A1:D15,4,FALSE))


    I tried to write the code into the existing drop down box which is taking data from a list which also seemed to cause a problem and i can't see how to set up a blank drop down box.

    Anyway, have a look when you get time and see if i'm close to getting it right.

    Cheers mate.
     

    Attached Files:

  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    To me this just sounds like some form of dependent data validation. Can you upload an xls version?
     
  10. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    I've made a few edits to your sheets. I see that I wasn't very clear; the code that I had given goes into the validation for the cell, not into the cell formula. That way you get a drop down for the available colours, but the code chooses which colours are allowed.

    Another tip that you might find helpful, when you do not want a reference to move as the formula is dragged down (e.g. when the reference holds the source for your Vlookup) put dollar signs before the letter and the number to mark them as absolute. There were a few flaws developing based on not having done that.

    As for adding a description, I think that you should be able to do that yourself using a Vlookup function. Space could be a problem though: spreadsheets don't allow multiple lines.
     

    Attached Files:

  11. Cheekywink

    Cheekywink Thread Starter

    Joined:
    Jun 5, 2008
    Messages:
    69
    Hi Bomb,

    I've attached an xls copy for you.

    the sheets I've been working on are the master template and order form pages.

    If you need any clarification on what i'm trying to do let me know as don't think the above is too explanatory, only Ent seems to understand a he's helped with the coding for most of the sheet :D
     

    Attached Files:

  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Thanks for the xls version.

    See the attached, which is a dependent data validation example ... thing.

    There are 3 defined names:

    1. "Types" refers to the red cells, which is the source for the data validation for the yellow cells.

    2. "Choices" refers to the green cells.

    3. "ChoicesX".

    "ChoicesX" is ... bizarre. The formula that defines it when a cell in (example) row 1 is selected is:

    =OFFSET(Choices,Sheet1!$H1,0,1,COUNTA(OFFSET(Choices,Sheet1!$H1,0)))

    , but the "$H1" part somehow "reacts" to selection change -- i.e. when a cell in row 12 is selected it's:

    =OFFSET(Choices,Sheet1!$H12,0,1,COUNTA(OFFSET(Choices,Sheet1!$H12,0)))

    The (list) source for the data validation for the blue cells is "ChoicesX". When H1 = 1, the I1 choices are a-b-c-d. Change H1 to 2, the I1 choices become x-y-z. Same applies to H2:I2.

    There's a snag (of course). Changing H1 changes the I1 options, but it won't automatically change what (if anything) was previously selected for I1 (from a different "type list", if you follow).

    Remains to be seen whether you can spot a way that this might apply to what you're trying to achieve. :)
     

    Attached Files:

  13. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    I too can't think of any way to invalidate the entry if the value it's based on changes.
     
  14. Cheekywink

    Cheekywink Thread Starter

    Joined:
    Jun 5, 2008
    Messages:
    69
    cheers for the help guys, sorry for the delay in responding.

    Had to have a few days off from looking at excel screens. I'll give the new formula a try and let you know how I get on.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/961299

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice