I want to make a spreadsheet which will be solving numerical for different chemical species. Now what i really want is the Excel recognize the text (i.e. specie name) as entered by user and place its data in the calculation and while data for species remain hidden

Hi, Your request for help really needs some examples of what you are trying to achieve and what source data you have.

Is your source data just two columns, one column of chemical species names and the next column a value for each chemical species, or are there several columns of data for each chemical species.

Please include the formula you are using - the formula that will include the 'replaced' data.

From what you have said it sounds as though the VLOOKUP() function will do what you want.

If the user enters a name in Cell A1, and you have a table in cells D1 to E116 with names in D1 to D116

and values to use in the formula in cells E1 to E116,

then use VLOOKUP in your formula

=X*VLOOKUP(A1,D1:E116,2,FALSE)

The value X will be multiplied by a value from Column E, based on the name entered in cell A1.Here is VLOOKUP in the Excel help file.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

(range_lookup is TRUE or FALSE. FALSE ensures that data is only returned if there is an exact match between the lookup_value (cell A1 in this example) and a value in the first column of the table_array, in this example column D).col_index_num tells the formula which column in the table to return data from. In the example I used 2, which is column E. If you needed to return two different values for the same chemical species, the table would have to be bigger, columns D to F and then you could use =X*VLOOKUP(A1,D1:F116,2,FALSE) - Y*VLOOKUP(A1,D1:F116,3,FALSE)

Regards

Thnx 4 ur reply

wat i really want isI have a formula (eg) ln p=A+B/(T-C)

Here ln is for log base to e

p for partial pressure

A,B,C r constants different for different Species which are available in text books or literature.

and T is tempreature

I enter values of A,B,C against each specie

the flow sheet is finished by entering related formulas.

when i enter a specie name in specific box, it matches data with text and calculate partial pressure at any tempreature for that specie

Hope u will solve this problem

Thanking you

Going back to Humar's VLOOKUP suggestion, I believe this is what you are looking for... A1 will be the cell where you will enter the Species name

A2 will be the cell where you will enter the temperatureA3:D4 will be your Table of Species and their associated A, B and C values.

A B C D 1 Species1 2 −273.15° 3 Species1 1 2 3 4 Species2 3 2 1Put this in a cell and it will

lookupthe A, B and C values in the table.=LN((VLOOKUP(A1,$A$3:$D$4,2,0)+VLOOKUP(A1,$A$3:$D$4,3,0))/(A2-VLOOKUP(A1,$A$3:$D$4,4,0)))

Each VLOOKUP will

lookupthe value in A1 and pull data from a different column of thelookup_arrayA3:D4.

Hi,

I created a sample table of data against names in cells A1 to D4

In Cell A7 I used data validation to create a drop-down list.

Select cell A7. From the Menu select Data - Validation...

In the dialog box select the Settings tab and List from the Allow: box

In the source box select the range for the names used in your table (=$A$2:$A$4)

A7 will now only allow users to select a valid name from your table.

I used cell B7 for the temperature.Here is what it looks like:

A B C D 1 Name A B C 2 Oxygen 10 0.001 1 3 Argon 20 0.002 2 4 Helium 30 0.003 3 5 6 Select Enter gas temperature 7 Oxygen 20 8 10.00005263

The formula in cell A8 is:

=VLOOKUP(A7,$A$2:$D$4,2,FALSE)+VLOOKUP(A7,$A$2:$D$4,3,FALSE)/(B7-VLOOKUP(A7,$A$2:$D$4,4,FALSE))

This formula uses the VLOOKUP function to return data from the table array in cells A2 to D2, based on the value in cell A7.

The number 2, 3 or 4 is the column following the name in the table to return the data from, (column 1 is the name itself). False at the end of the VLOOKUP function ensures an exact match for the name selected. The formula also gets the temperature from cell B7.You could use data validation for cell B7, this time selecting whole number instead of list, and selecting between and a lower and upper limit (there are several other options for data validation). Data validation reduces the risk of completely wrong data being entered.

Hope this gives you a basis for your solution.

Regards

thnx for ur suggestion

u guys hav suggested right solution

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History