john banks

BNF Chapter
Group

1

Gut

2

Circulation

3

Breathing

4

Nervous system

5

Infection

6

Hormones

7

Obstetrics, Gynaecology or Urinary systems

8

Immune system

9

Nutrition or blood

10

Bones

11

Eye

12

Ear, nose, throat

13

Skin

14

Vaccines

15

Anaesthetics


I have the above table with the number in column A and the string in column B.

I want a function that does a lookup from another cell which contains a number that matches one of those in column A and will return the string in column B, but the Excel functions I can find appear to be numberical.

Hints welcomed!

Hanslow

John, this of any help?

bob269

Formula would be =VLOOKUP(H5,A1:B18,2)

where H5 is the cell where your cross reference number is,

A1:B18 would be your data range

and the ,2 indicates the resulting field should display column 2 from the datarange, which would be the reference/description.

Hope this helps.

john banks

Works great thanks!

john banks

Further two questions if I may!

1. When you copy of function down a column it automagically moves the references to other cells as it copies down, how do I stop this if I want the whole column to refer to one cell or array? Can I define an array with a name and reference to that?

2. Can I automatically link a text file to be imported into a cell range when the spreadsheet is opened? The text file is imported with fixed width columns each time.

bob269

Using my above example =VLOOKUP($H$5,$A$1:$B$18,2)

putting the $ infront of the Cells, locks it when you copy it.

Point 2. Is possible via the on open macro. Needs a little thinking about.


You may want to add the false command to the end, currently if you enter anything above 15 in your check cell it will always report back with Anaesthetics, adding false forces it to return an error if the number does not exist.

=VLOOKUP($H$5,$A$1:$B$18,2,False)

john banks

Thanks!

bob269

Easiest way would be to (while spreadsheet is open) go to data menu->import external data->import data,

then use the wizard to select your text file, widths etc.

On the last page when it asks you where you want to put the data, click properties and Tick Refresh Data On File Open, and remove the prompt for Filename On Refresh Tick

john banks

Thanks.