We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
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

halil
Posts: 288 Forumite
in Techie Stuff
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
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
0
Comments
-
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.0
-
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]0 -
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.0 -
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=2DQZBUB1HJSN81BTN8XM161JR70 -
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.0 -
Chippy's answer is great.:grouphug: Things can only get better.0
-
aha.....got it, thanks alot Chippy0
-
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.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

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