this will probably be easy for someone here, but i haven't really had time to try and figure it out.
i've got a list of 55 pieces of medical equipment i need to log repeatedly, sorted by room in a hospital. so a portion of it would look like:

Room Type Equipment # Equipment Name
101A Patient 11 Crash Cart
101A Patient 22 IV Rack
102 Isolation

And so on. Is there any way I can make excel automatically enter the equipment name when I enter the equipment number? some sort of macro or something? i think it could be done by a really long if/then, but that doesn't seem like the best way to go about it. thanks

[Edited on June 16, 2007 at 9:43 AM. Reason : .]

6/16/2007 9:43:11 AM

vlookup would be your best bet for this. unless you want to have excel automatically replace the number you entered with the name then its macro time.

[Edited on June 16, 2007 at 9:58 AM. Reason : by the way if then won't work, you're limited to the number of statements (i think 6)]

6/16/2007 9:57:38 AM

I concur with vlookup.

Or, do it in Access. Make a table with equipment number and name and make another table with rooms and equipment numbers. Use a query to make a third table with all of the data that you want in it.

[Edited on June 16, 2007 at 10:06 AM. Reason : You could also use a third table in that listed all of the rooms and room types]

6/16/2007 10:02:17 AM

vlookup is good for filling in the type, as long as you know the number.

but you could also use data validation to automatically present you with a list of items in a dropdown.
To do this, make a list of Equipment somewhere (on another worksheet) in one column. Then in the main table, highlight all the cells in the Equipment column, and go to Data > Validation. In the Data Validation window, select "List" from the Allow dropdown. Then click inside the "Source" field, and go over to the list of equipment and highlight them all, so the source field will be something like "=$A$1:$A$7", if the list of equipment is stored in A1:A7. Then click OK, and when you go back to the Equipment column in the main table, when you select a cell, a little arrow will appear that will show a dropdown of all the equipment to choose from.

6/16/2007 10:17:59 AM

VLOOKUP would work, but I feel like this is much better suited to a relational database. Using that, you'd be eliminating redundant entries in your storage which is more space efficient.

If the volume of data is relatively small then space isn't really an issue. Otherwise, I think Access could do this nicely. That's just my $.02.

6/16/2007 10:55:22 AM

just wanted to clarify/make sure we're on the same page. i want to set up some sort of reference in the cells so that if i enter "01" in cell E5, "Blanket Warmer" automatically shows in F5. Is that what ya'll are talking about w/Access?

6/18/2007 8:05:51 AM

Well, Access would to this for you -

you set up two tables similar to this...

|Room |Type |Equipment #|
----------------------------
|101A |Pat | 11 |
----------------------------
|101A |Pat | 22 |
----------------------------
|102A |Iso | 01 |
----------------------------

AND

|Equipment # | Name |
-----------------------------
| 11 | Crash Cart |
-----------------------------
| 22 | IV Rack |
-----------------------------
| 01 | Blanket W. |
-----------------------------

and Access, or any other DB can automatically relate back and forth between the two tables, using the Equipment # as the index. It works nicely for larger data sets, but if you're not dealing with a whole lot of data, it might be a bit overkill.

With Excel's VLOOKUP, you can just create a lookup table and it will essentially do the same thing, andmore easily since you just have a one-to-one relationship between data.

[Edited on June 18, 2007 at 11:34 AM. Reason : .]

6/18/2007 11:33:34 AM

vlookup worked great, thanks

6/18/2007 12:04:47 PM