Excel 2007 : Logical formula ERROR UPDATED WITH SHEET 2

**Skip98** · 03-04-2010, 09:19 AM

Hi there

I am using 2007 version and i have problems with formulas to do the following.

I have 4 columns of data. Column one is a city name. Column two is an expected delivery time number for that city. Column 3 is the city hub which will do the delivery for the city and column 4 is number associated with the city.

I have a drop down menu in column 1 for the city.

If i select the city in column 1 the rest of the information associated with the city must be shown in the rest of the columns. With out entering them.

Example. if i select Pretoria in the drop down menu then in column 2 must show the delivery time column 3 must show the city hub which is going to do the delivery and in column 4 the number.

I thought that it must be =if(a1="pretoria" - that is were i got stuck.

Please help

**NBVC** · 03-04-2010, 10:12 AM

With both books open...

=VLOOKUP($A2,'[SAMPLE OF ERROR.xlsx]Sheet1'!$B:$E,COLUMNS($A$1:B$1),0)

copied down and across...

You can then close the source workbook and the path should be updated...

**Skip98** · 03-04-2010, 10:19 AM

Hi There

Thank you for the reply - please explain in more simple way - what do i enter were?

**NBVC** · 03-04-2010, 10:25 AM

Assuming you want the formula in cell beside drop down menu in SAMPLE OF ERROR2.xlsx,
then put it there, in cell B2. It will look at your SAMPLE OF ERROR.xlsx workbook.

**Skip98** · 03-05-2010, 04:34 AM

Hi there

I only have one spreadsheet that i am going to work of or must i use two sheets to do this.

Can you please help me with the formula pertaining to one sheet.

Regards

**Skip98** · 03-05-2010, 07:15 AM

Hi There

I have changed the sheet with the information needed. Please read and help.

**NBVC** · 03-05-2010, 08:46 AM

The information to load into B2, C2 and D2 has to come from somewhere doesn't it?

How does excel know what info to put there? You need a table of all relevant data. Then the Vlookup function, like I showed you, would be responsible to go to that table and pull your corresponding data after you select a city.

The table can be in the same workbook or in a separate sheet in the workbook.

Have a look at VLOOKUP help in Excel to see how the function works.