Formula help needed

Sounds like you need a 'lookup' function such as VLOOKUP(). The Excel Zone compendium includes a section on lookups: http://www.accountingweb.co.uk/item/175137#lookup, in particular: http://www.accountingweb.co.uk/item/174160. Do be careful with the optional 4th argument in VLOOKUP() which determines whether to perform an approximate or exact match. If you use the default, approximate match, form then the first column must be sorted in ascending order.

You mention trying to write a program, although VLOOKUP() will probably do the job in a simple case, it's often worth considering the use of a database rather than a spreadsheet for more complex requirements, particularly with regard to controlling data input.

Hi Paul,
I had a similar set of requirements: upon entering a two letter code in a cell, replace this code with a particular name, populate the adjacent cell with the corresponding pager number, check to see if the named person was listed as the swing shift coverage and indicate this in the next cell over. This was "archived" in a trailing queue of 10 sheets, with documentationbeing pulled from the preceding sheet when appropriate. To do all these things I used VBA code in a WorkbookChange procedure. I used the INTERSECT function to limit the triggering of the SheetChange to the column where the code would be entered, and used the VLOOKUP function to find and bring over the applicable documentation from the previous sheet.
HTH
Bill

you are using validation to force the entries in your column X to be an item on the list.

If your Supplier list changes in size then if it is on a sheet of its own you can make the range name(s) dynamic to expand or contract as you add/remove supplier details to the list as follows - assume the supplier list is on Sheet2 with headers on row 1 and starts from column A. When you create the name Suppliers using Insert-Name-Define in the RefersTo box type =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1)) So long as there is always an entry in column A, and if you add a new column to your supplier list you add a header for it in Row 1 then when you add suppliers to the list the range name will adjust.

You can add single column dynamic ranges by using the formula =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1) - so only counting the entries in column A to extend the range from Sheet2!$A$2 or Sheet2!$B$2 etc. In that way you can have a dynamic range for just the Name column (say) that can be the List item for Data-Validation in the input sheet

If you make entries on Sheet3 and your suppliers are on Sheet2 then the following VBA on Sheet3 would put the phone number into column 2 when you enter a supplier code in column 1 where phone number is the second column of the supliers list

'Code starts

Private Sub Worksheet_Change(ByVal Target As Range)
 Select Case Target.Column
  Case 1 'Assumes entry in column A
   Target.Offset(0, 1).Value = Application.WorksheetFunction.VLookup(Target.Value, Sheet2.Range("Suppliers"), 2, False)
  Case Else
 End Select
End Sub
'Code ends

It's worth investigating Lists in Excel 2003 and Tables in Excel 2007/2010 as an alternative to the need to create dynamic ranges and the use of the WorksheetChange event. I contributed a couple of articles on lists/tables to Excel Zone a little while ago

http://www.accountingweb.co.uk/topic/technology/how-use-excel-2007-table...

http://www.accountingweb.co.uk/topic/excel/how-use-excel-2007-tables-pt2

There's also some more examples on my own site:

http://www.tkb.co.uk/excellist.htm

Please list up all the suppliers name in column X and the details in column Y. This is your database. Now type the following formula in the cell where you want to bring the Details (Column Y). Your data is in sheet1 at X1:Y4. I have 3 data only. You are typing BT in cell A2 and want to bring the details (Telecom) in B2.

If you want to bring the details:

in the same sheet formula (In sheet1):

=vlookup(a2,$x$1:$y$4,2,false)

in another sheet (ie sheet2)

=vlookup(a2,sheet1!$x$1:$Y$4,2,false)

I hope your problem is resolved.

Thanks,

Firoz

Surely this is easier:

Assuming that X7 says "BT", T7 (list B) is details, then
In Y7 type:
=IF(X7="BT",+T7," ")

Copy this formula down the Y column

Possibly, but unless I've misunderstood completely, only if you just have one supplier...