Row column match to ENTER data in matched cell, then enter, cells clear, data stays

[born2dive00]

Hello everyone I have a challenge for you all,

I have a huge data base that I need to fill out, 4500 rows of oils, and 5000 columns of chemicals.

Now the data will be copied and pasted into several individual cells from another data base. what I need is a formula that looks at the first cell in green (the name of the plant) and then goes to the chemical compound names, and looks up the corrisponding cell/s on the another sheet (oil list and chemical compound sheet)

Then once the corrisponding cells are found, I need the numbers in the percentage helper column to be entered in the assigned cells. on the oil list and chemical compound sheet.

BUT here is the catch, I need it so once you hit enter, the cells on data entry page clear, but the data entered stays in the table on the oil list and compound sheet.

i am trying to keep the formula as fast as possible to prevent lagging of the computer and program.

i have attached a copy of a sample file i am talking about.

Please let me know.
Thanks Brad

[FDibbins]

This cannot be done with formulas You will need VBA

Formulas cannot "see" colors, and they cannot affect other cells, only the cell they reside in. (I need it so once you hit enter, the cells on data entry page clear, but the data entered stays in the table on the oil list and compound sheet.)