Excel formula help required please

SexyGreyFox · 7 Sep 2010 at 23:07

Lets say I type a number between A00000 and A99999 into A1 I would then like cell B2 to read 1.

However if I type a number between B00000 and B99999 into A1 I would then like cell B2 to read 2.

and so on .........

Any help appreciated

Makhaira · 7 Sep 2010 at 23:12

Easy sir! There are two functions you need, I suggest you look up the exact syntax as I'm about to head for bed and can't remember it offhand.

First, use LEFT to break out just that leading letter from the cell.
Second, use a VLOOKUP table, containing letters A through Z, and numbers 1 through 26, to convert the letter to a corresponding number value. Hey presto, your B2 cell value!

izzop · 7 Sep 2010 at 23:35

If I've understood you, try this.....

Code:

=IF(LEFT(A1)="A",1,0)

It'll give you '0' if it's not, but I wasn't sure what you wanted.

If you want it to be blank/empty then use...

Code:

=IF(LEFT(A1)="A",1,"")

Regards

Tomsk · 8 Sep 2010 at 11:27

B2=CODE(UPPER(A1))-65

SexyGreyFox · 8 Sep 2010 at 11:48

Let me explain exactly what I want to make it more clear.
Scattered around the hospital in different rooms are Medical Records and they are numbered A00000 to Z99999 (of course all these numbers haven't been used yet).
Now it could be that A00001 to A09999 are in room 7 but A10000 to A45000 could be in room 2 and A45001 to A99999 are in room 11.
B00001 to B79999 could be in room 4 and B80000 to B99999 could be in room 15 and so on.
I know that the cell is going to contain a lot of information.

I can do this very easily with Conditional Formatting and using colours but I can't see an option where I can use a number instead of a colour.

Thanks

Tomsk · 8 Sep 2010 at 12:51

Code:

[b]    A           B        C            D[/B]
[B]1[/B]  A00000	7	A10000	=INDEX(B$1:B$5,MATCH(C1,A$1:A$6,1))
[B]2[/B]  A10001	2	A10001	=INDEX(B$1:B$5,MATCH(C2,A$1:A$6,1))
[B]3[/B]  A45001	11	A45000	=INDEX(B$1:B$5,MATCH(C3,A$1:A$6,1))
[B]4[/B]  B00001	4	A45001	=INDEX(B$1:B$5,MATCH(C4,A$1:A$6,1))
[B]5[/B]  B80001	15	A99999	=INDEX(B$1:B$5,MATCH(C5,A$1:A$6,1))
[B]6[/B]  C00000		B44999	=INDEX(B$1:B$5,MATCH(C6,A$1:A$6,1))
[B]7[/B]  	        	B45000	=INDEX(B$1:B$5,MATCH(C7,A$1:A$6,1))

Column A is a list of Medical Records ranges
Column B is the room assignments
Column C is a list of example medical record enquiries
Column D is the formula returning the room assignment

SexyGreyFox · 8 Sep 2010 at 15:00

Tomsk said:
Code:

[b] A B C D[/B] [B]1[/B] A00000 7 A10000 =INDEX(B$1:B$5,MATCH(C1,A$1:A$6,1)) [B]2[/B] A10001 2 A10001 =INDEX(B$1:B$5,MATCH(C2,A$1:A$6,1)) [B]3[/B] A45001 11 A45000 =INDEX(B$1:B$5,MATCH(C3,A$1:A$6,1)) [B]4[/B] B00001 4 A45001 =INDEX(B$1:B$5,MATCH(C4,A$1:A$6,1)) [B]5[/B] B80001 15 A99999 =INDEX(B$1:B$5,MATCH(C5,A$1:A$6,1)) [B]6[/B] C00000 B44999 =INDEX(B$1:B$5,MATCH(C6,A$1:A$6,1)) [B]7[/B] B45000 =INDEX(B$1:B$5,MATCH(C7,A$1:A$6,1))

Column A is a list of Medical Records ranges
Column B is the room assignments
Column C is a list of example medical record enquiries
Column D is the formula returning the room assignment

That works but now to see how to integrate it into the spreadsheet.
Thanks.