+ Reply to Thread
Results 1 to 9 of 9

Automatic cell fill, based on answer from VLOOKUP function

  1. #1
    Registered User
    Join Date
    01-31-2008
    Posts
    19

    Automatic cell fill, based on answer from VLOOKUP function

    Here's the current formula for reference for column C:

    =IF(B12="","",IF(OR(VLOOKUP(B12,M3:R67,6,0)="Profile",VLOOKUP(B12,M69:R302,3,0)="Metre"),"Length needed","-"))

    Column A = Quantity
    Column B = Product Code
    Column C = Length

    We have a number of products:
    Some are classed as a "profile" (all of which are given a length)
    Some are classed as an "accessory" (only a few of which requre a length)
    • If 'A' is blank, 'B' and 'C' are blank
    • If 'A' has a quantity, column 'B' shows "Ref Needed" text which you need to manually type in (it's just a prompt)
    • Once you have specified the reference in column 'B', Cell 'C' (in the same row) references the tables above in the VLOOKUP (the first VLOOKUP is a table for the profiles, the second VLOOKUP is a table for the accessories)

    I want Cell 'C' to recognise from the value in column 'B' if a length needs specifying or not. If yes, then show a prompt type of "Length needed", if it is not needed, fill the cell with '-'

    As far as I can tell the equation above should work, but I'm getting an #N/A error, any ideas?

    Edit: Profile shows incorrectly in the equation above (space in the middle of the word), but it's a forum thing, not in my excel formula

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    Does this work for you?

    Please Login or Register  to view this content.
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    01-31-2008
    Posts
    19
    Well it's not giving an error anymore! However when sleecting something that needs a length, instead of saying "State length" or something similar it's just putting "-" (which should be present what a length is not required.

    Let me work through the equation and see I can can fully understand it, you're a legend chippy, many thanks!

  4. #4
    Registered User
    Join Date
    01-31-2008
    Posts
    19
    I'm not having any luck Chippy. As far as I can tell you don't require the last part of your equation as all parameters are specified in the first half, if you can briefly describe what's meant to be happening in your equation it my help me

    Upon looking up the ISERROR function (which I hadn't used before) it seems to be an error finding aid, but I can't tell if it's ncessary to make the cell actually behave properly or if you're just using it to try and find the problem.

    The cell doesn't seem to be acting properly as the conditions specified in in the OR command, as far as I can tell, should mean that if either are TRUE, then "Length required" is displayed.

    This is not happening, '-' is displayed regardless of whether it's an item that needs a length or not.

    Appreciate any further explanation or help you can give.

  5. #5
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    Is it possible to post a "dummy" sample of your spreadsheet and I'll take a look, only its difficult to see exactly what you are looking at?

    To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

  6. #6
    Registered User
    Join Date
    01-31-2008
    Posts
    19
    Hi Chippy, file attached.

    i'm working on row 12, column c.

    Don't even look at the Each column!!!

    There are many variables for working out the price of each individual line, these are eventually considered in the each column but that needs further work
    Last edited by cptwhite; 02-04-2008 at 12:38 PM.

  7. #7
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    The cell B12 is a number, the cell M3 is text, so if you enter '01 in B12, you get D12 to work.

    I'm not sure what the second vlookup is doing?

    Can you explain a little more.

  8. #8
    Registered User
    Join Date
    01-31-2008
    Posts
    19
    Sorted it chippy, just so you're aware:

    First problem was as you mentioned
    The cell reference range for the second VLOOKUP was incorrect. The reason being you were looking for the value in B12, this value wasn't always present in the range specified in the second VLOOKUP thus it returned an error. To correct this you need to ensure the range in both VLOOKUP is from M3 to R392 so that it can always find the value you enter in B12

  9. #9
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Glad to hear you got it sorted - thanks for the feedback

+ 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