x404.co.uk
http://www.x404.co.uk/forum/

Any Excel experts out there?
http://www.x404.co.uk/forum/viewtopic.php?f=3&t=12190		 Page 1 of 1
Author:  tombolt [ Fri Jan 21, 2011 3:29 pm ]
Post subject:  Any Excel experts out there?
I am sure I've got this in the wrong place, though it's not specifically PC or Mac software. A friend has asked me to post the following:

Quote:
Please help I have to do something in excel but I have got stuck on something…

I have 2 worksheets:
Cells in worksheet 1 will contain numeric values.
Cells in worksheet 2 will contain words.
The words in worksheet 2 represent the values in worksheet 1.
1=poor
2=average
3=good
4=verygood
5=excellent

So where cell A1 in worksheet 1 shows a value of 3, cell A1 in worksheet 2 will show a value of “good”

I have tried using a SpellNumber function but I don’t think that is the right method… can anyone help?


I'm not sure that makes a lot of sense to me, as I don't really understand what his problem is exactly, but then I know virtually zero about excel!
Author:  jonlumb [ Fri Jan 21, 2011 3:31 pm ]
Post subject:  Re: Any Excel experts out there?
I'd use a seperate little table and the vlookup function.
Author:  EddArmitage [ Fri Jan 21, 2011 3:38 pm ]
Post subject:  Re: Any Excel experts out there?
As Jon said I'd use a lookup table. If you want an example spreadsheet I can email it to you? Essentially you want a table somewhere (a separate worksheet?) with the number in one column, and the wording in the next (I'd call that region "Lookup" for clarity). If you have your first worksheet called "Numbers" and your second called "Words", then cell A1 in Words will contain the following:
Code:
=vlookup(Numbers!A1,Lookup,2)


HTH.

Edd
Author:  Fogmeister [ Fri Jan 21, 2011 3:52 pm ]
Post subject:  Re: Any Excel experts out there?
Yep, what these guys said...

Code:
Sheet 1...
Cols...
A
1
2
3
2
4
5
3
etc...


Code:
Sheet 3...
Cols...
A    B
1    poor
2    average
3    good
4    very good
5    excellent


Code:
Sheet 2...
Cols...
A
=VLOOKUP(Sheet1!A1,Sheet3!$A$1:B5,2,FALSE)
=VLOOKUP(Sheet1!A2,Sheet3!$A$1:B5,2,FALSE)
=VLOOKUP(Sheet1!A3,Sheet3!$A$1:B5,2,FALSE)
etc...


Obviously, this depends on the exact locations of the data etc... but you should be able to work it out.

The formula in sheet 2 can go into any field but has to relate to the correct columns/rows in sheet 1 and 3.
Author:  tombolt [ Fri Jan 21, 2011 4:03 pm ]
Post subject:  Re: Any Excel experts out there?
Thanks guys, I've passed it on, appreciated.
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/