We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Excel help!!!!!!!

Options
hi all
i need some help with excel
im making a table that shows incidents of graffiti and the type of surface it is on, i also have a column that shows the Surafce ID :
metal=1
brick=2

etc

is there any way that the ID of the surface can be automatically inserted once i type the surface on the spreadsheet?


cheers

Comments

  • dccarm
    dccarm Posts: 1,263 Forumite
    Add a table showing the surfaces beside their ID's, then use a VLookup function to pick the correct ID. If the surfaces are from a standard table I'd recommend using data validation to give you a dropdown menu of th eoptions. I've knocked up a quick working model if you want to PM me your email address.
  • Excel001.jpg

    Hope the above image makes sense. Basically, you put an IF field in the third column
    =IF(B4="Metal",1,2)

    Which means if the contents of cell B4 are equal to Metal, put 1 in this cell. If they are not, put 2. You can copy this calculation cell down column C to make it all work automatically as you enter values in column B

    [Edit: As other posters have pointed out, VLOOKUP would be better on with more criteria - I misread the original poster's question]
  • I'd also use the VLOOKUP function, which is far more flexible than using IF's (if there are 5 or 6 surface types it would be very unwieldy and unreadable).

    First create a named range (e.g. called SurfaceIDs) containing the surface types in one column and IDs in the next. Then, assuming the first surface type is in A1, put the following formula in B1 to calculate the corresponding surface ID:

    =VLOOKUP(A1,SurfaceIDs,2,FALSE)

    Copy down this formula for the other rows.
  • halil
    halil Posts: 288 Forumite
    okai dont thiink ive got this to work
    i have attached the xls file, maybe someone can get it working?

    http://s39.yousendit.com/d.aspx?id=2DQZBUB1HJSN81BTN8XM161JR7
  • OK, first highlight your surface 'table' (7 rows and 2 columns). Then, in the 'name' box type SurfaceIDs. That's a quick way of creating a named range. (The 'name' box is the box directly above the A column and contains the current cell reference).

    Next, enter the following formula in cell E2:

    =VLOOKUP(D2,SurfaceIDs,2,FALSE)

    Then drag this formula down the E column for the rest of the rows.
  • Chippy's answer is great.
    :grouphug: Things can only get better.
  • halil
    halil Posts: 288 Forumite
    aha.....got it, thanks alot Chippy
  • spotter
    spotter Posts: 50 Forumite
    The problem with using VLOOKUP on its own is if you mis-spell the surface type you get a #N/A error. If you use Data Validation on the surface type, as dccarm has mentioned, this will be prevented and you get a list of possible surfaces to choose from.
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.9K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.9K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.1K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.