anyone have a FORMULA for find and replace entire cell value?

[born2dive00]

Ok I think that there is a way to do this but am not sure.

On sheet 1 I have a index and match formula for a numbered row and column table on sheet 2., this is returning currently 0

I need a formula that will take the referenced cell above, and will replace that cells value with the value on A1 on sheet 1

something like =find('Oil list and Chem compound'!$C$6:$FFJ$4158,'Data entry page'!K20,'Data entry page'!M20) replace with sheet1 A1


Please help

[63falcondude]

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[born2dive00]

Here is the file i am working on.

[63falcondude]

4222 columns... so much for small.

I still don't understand what you want.

Where in your workbook from post #3 are the manually entered desired results?

[born2dive00]

Ok i will explain this as simply as i can.

1. On the Data entry page, I need excel to look up the name in E9, and in E20 on the oil list and chemical compound page which will be cell DTF6, (DTD1 on the address)

2. I then need DTF6 to be filled automatically with the numbers in I20 on the data entry page.

3. Once the return is hit I need the values on the Data entry page E7, E8, E13. E20:I34, to clear.

4. BUT i need the value from I20 to stay in the cell DTF6 on the oil list and chem compound.

[born2dive00]

I was thinking of using the formula Find or replace... but am un sure how to keep the data from switching.

4222 columns... so much for small.

I still don't understand what you want.

Where in your workbook from post #3 are the manually entered desired results?

[63falcondude]

A formula can be built to do #'s 1 and 2.
That being said, you are looking at a huge range (over 17.5 million cells) to fill. Any formula filling 17.5 million cells will likely lock up your workbook

No formula can do what you are asking for in #'s 3 and 4.

If you insist on going about your project in this manner, you will be limited to using VBA, which I cannot assist with.