Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Wed Feb 04, 2009 6:19 am Post subject:
I'm not quite clear what you are trying to achieve here. I assume that you want to put a numeric value in one cell, and have that to cause a name to be shown in another cell. How you do this will depend on a number of factors. If you only have a few values to deal with then you can do it with a nested IF formula:
Code:
=IF(A1=1;"Adam";IF(A1=2;"Bill";IF(A1=3;"Charlie";IF(A1=4;"Dave";"Value out of range"))))
Inserts one of the names listed in the cell in which this formula is place according to what value is put into cell A1 and gives "Value out of range" if your number doesn't match a name.
A simpler way of achieving basically the same result would be a CHOOSE formula:
Code:
=CHOOSE(A1;"Adam";"Bill";"Charlie";"Dave")
but if you choose a number not in the list you just get "Err:502" as result in the cell.
If you have a lot of values, then your formula gets very cumbersome and hard to understand. In that case it may be better to store them at a location in the spreadsheet itself and use LOOKUP/HLOOKUP/VLOOKUP to return the correct value according to whatever number is inserted. I am very inexpert at using these functions, but here is a simple example. If I insert a number in cell A1, I want it to prodcue a name in cell B1. I am going to store a list of numbers (1,2,3,4) in cells E1 to E4, and the corresponding names (Adam,Bill,Charlie,Dave) in cells F1 to F4. I insert the following formula into cell B1:
Code:
=VLOOKUP(A1;E1:F4;2)
this gives an instruction to look for the value I have inserted in cell A1 in the first column of cells in the range E1 to F4 and to return as a result the value in the corresponding cell in the second column. You can see that this can be quite powerful because I could store more data against that number so if I had a third column of data, say, phone numbers then:
Code:
=VLOOKUP(A1;E1:G4;3)
would give me a phone number as a result instead of a name. Note that my range is now E1:G4 because I now have three columns of data. If the number inserted doesn't match any data in your array then it gives a "#NAME?" message in your result cell.
For CHOOSE or any of the LOOKUP functions you can put them within an IF formula so that a more helpful error message is generated than the formula produces of its own accord.
LOOKUP/HLOOKUP/VLOOKUP are standard spreadsheet functions, and you ought to be able to find information about how they can be used on the web somewhere, although to be honest I haven't found anything that I find very easily intelligible.
I hope this is some help.
Last edited by James3359 on Wed Feb 04, 2009 2:12 pm; edited 1 time in total
LOOKUP/HLOOKUP/VLOOKUP are standard spreadsheet functions, and you ought to be able to find information about how they can be used on the web somewhere, although to be honest I haven't found anything that I find very easily intelligible.
I thought Jacqueline had written something for the wiki about them, but I can't find it now (if my mind is not playing tricks on me)
Smokey _________________ "[...] whether the duck drinks hot chocolate or coffee is irrelevant." -- ovvldc and sardisson in the NeoWiki
James, you seem to know your stuff !!
However, I must be doing something wrong, as I get name/522error mesg.
To clarify further;
I to be able to enter range of numbers(1-10) in a cell ,then have corresponding names(Bob(1),Peter(2),Bill(3),Steve(4)) to those numbers displayed "in that same particular cell".??
Sorry if were going over same stuff >>>
Cheers Peter.
If you want Bob, Peter, Bill or Steve in that cell without referring anywhere else, you need to enter the names there. The formulae work on what is in other cells.
best wishes,
Oscar _________________ "What do you think of Western Civilization?"
"I think it would be a good idea!"
- Mohandas Karamchand Gandhi
Not that it's a perfect solution, but you could always have the numbers in e.g. column D and the lookup results in column E, and then hide column D.
What you want to do (if I understand correctly) is to display a 'virtual' value in a cell whilst keeping the real value hidden underneath it. It may be possible to do that via some very sophisticated conditional formatting. Or do you just want to replace the number value with the name value? That should be possible via a macro. But it's a lot easier to go with the column hiding thing .
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Thu Feb 05, 2009 2:11 am Post subject:
Ozpwc wrote:
I to be able to enter range of numbers(1-10) in a cell ,then have corresponding names(Bob(1),Peter(2),Bill(3),Steve(4)) to those numbers displayed "in that same particular cell".??
Normal spreadsheet behaviour would mean that anything you entered into the cell would overwrite any formula or data that is already there.
NeoOffice may have a feature which has the functionality you are looking for, but I am not aware of it. Alternatively you could work on yoxi's suggestions.
Joined: Jul 05, 2005 Posts: 685 Location: North West England
Posted: Thu Feb 05, 2009 3:32 am Post subject:
Another way which may get near to what you want is to use the data validation feature. Select the cell into which you wish to insert the data and choose Data>Validity. In the Validity diaolog select the Criteria tab and choose List in the drop-down menu. In the Entries field enter the list of names that can be entered each separated by a return. Uncheck Allow blank cells if you don't want to allow them and leave Show Selection list checked.
Your cell will now have a little button to the right of it. Clicking that button brings down the list of names you entered and the user can choose which name they want using keyboard or mouse.
Ok, too hard to understand all your previous mesg's or the NeoOffice Help, so here is spreadsheet presentation of what I am trying to do-I think??
Here's the DRAW for our tennis teams,displayed as Team Numbers;
Rnd DATE Team Team Team Team
1 05/Feb 3 v 8 5 v 6
2 12/Feb 2 v 7 4 v 5
3 19/Feb 1 v 6 3 v 4
I want to re-type DRAW in a spreadsheet so that as I type each 'TEAM Nº' the team NAME is presented instead of the Team Nº .
I have tried Lookup & Vlookup but cant get the desired result or formulae???
Thnks in advance
Ozpwc.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You cannot download files in this forum