Automatically convert the formula as value & keep remains the formula in blank cells

Dear Forum Members,

I would like to get your support to solve my problem here. I have a master register, which contains more than 100 column and 30000 rows. Among the 100 column nearly 5 column has been applied ARRAY formula for automation returns. Hence, my excel workbook getting very slow to open, do data entry and to save the file.

My requirement is :

1. Is there any possibilities to automatically convert the formula as value till the data entry applied. Form example, i have 30000 rows in a column with formula. The last entry was in 2200 th row. Now, the formula should automatically convert the returns as value till 2200 row and remaining rows should have the formula.

Or else is there any other suggestions also highly appreciated.

Thanks in advance

Dear Mr. Glenn,

Yes, the data set has maximum of A: CI (column) and A1: A5005 (Rows). Kindly help me on this.

Thanks in advance

Dear Mr. Glenn,

As you suggested, I have attached dataset template for your kind reference and support.

If you enter Date of Reach (Column B) and Name of TCM (Column C) automatically result will come in Column H and L (Which has been highlighted in yellow color. I have used helper for that in Column CX.

Since file has gone upto 1.97 KB, i have setup the database upto 100 rows only.

Kindly do the needful.

First of all you don't need any array formula on column L. You can use this simple formula for that :

Please Login or Register  to view this content.

Also you can avoid helper column by using this formula on Column H :

Please Login or Register  to view this content.

I have tested this change in your attached file with 6000 rows. It's not making slow.

But if you are interested to remove those formulas automatically then you need to use a Change Event. Right click on the tab name and select View code. Now paste this code there :

Please Login or Register  to view this content.

Now save your file with a macro enabled format (ex. .xlsm or .xls). Now you will get the calculated answer every time if you enter any value in Column B or Column C. But if any of them is blank then the answer will be blank also.

Dear Mr. Sanram,

First of all, my expression was wow... excellent ... I have applied it in my master data set. But i have two clarification. I have attached the model data entry data set for your kind reference.

1. Row 9 - I had entered as "Ram", since Ram has been entered in the Row 3, immediately it show "Repeat" and Unique ID number. But after that, i found This Row 9 "RAM" is different, Hence i went back and add the initial for him. But no changes was happen in H and L column.

2. I have applied a formula in Column M to generate Family ID. based on the name match with C and V. Could you please see on Row 10, Since Rajan name is not entered in C column, M column shows #NA error.

Kindly guide and help me on this.

Which solution you have used? Formula or Macro?