# Self editing formula system, is it possible and how?

#### GingerBeardo

##### New Member
Is it possible to create an excel formula to edit another formula to reflect the changes in a range? All without using VBA or at least working in the confines of google sheets? More specifically I want to check a range for new text and when it finds it add it to an existing formula or remove it when something is deleted.

Here is an example of the formula I want to edit using this proposed formula.

Code:
``=IF(AND(R8=AA5),AB5,IF(AND(R8=AA6),AB6,IF(AND(R8=AA7),AB7,IF(AND(R8=AA8),AB8,IF(AND(R8=AA9),AB9,"")))))``

So if there is a new entry made at AA10 a new "IF(AND(R8=AA10),AB10" will be added to this automatically or deleted should an entry be removed.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Marcelo Branco

##### MrExcel MVP
I think you should use a different formula. Try this formula
=IFERROR(INDEX(AB5:AB100,MATCH(R8,AA5:AA100,0)),"")

Hope this helps

M.

#### GingerBeardo

##### New Member
Two questions, first, how do I apply this cell by cell to an entire column, hopefully not one by one? Copy an paste up dates but everything shifts a number.
Second, out of curiosity, do you think what I proposed originally is possible?

#### Marcelo Branco

##### MrExcel MVP
First
The formula I've suggested above should do (i think) what you need - that is, when the value in R8 changes it returns the value in AB5:AB100 that corresponds the value in AA5:AA100 that is equal to R8
If the formula doesn't help, could you explain what exactly are you trying to do? A small data sample along with expected results would be helpful.

Second
It's not possible without VBA. Even using VBA i wouldn't recommend a solution that changes a formula according with data entry.

M.

#### GingerBeardo

##### New Member

You are correct, the formula does do what I want, the issue is it is only checking R8 against AA. I need to apply this to the entire R column by cell. So starting at Row 3 (R3) each cell would need to be checked against AA and so on, this way their independent.

#### Marcelo Branco

##### MrExcel MVP
You are correct, the formula does do what I want, the issue is it is only checking R8 against AA. I need to apply this to the entire R column by cell. So starting at Row 3 (R3) each cell would need to be checked against AA and so on, this way their independent.

Try this formula in a cell in row 3, say S3, and copy (drag) down
=IFERROR(INDEX(AB\$5:AB\$100,MATCH(R3,AA\$5:AA\$100,0)),"")

M.

#### GingerBeardo

##### New Member
Awesome, that cuts out half the job! but I still have to do this for over 1k cells

#### GingerBeardo

##### New Member
Disregard, I was doing it wrong! Thank you very much for your assistance.

Replies
1
Views
83
Replies
1
Views
150
Replies
18
Views
389
Replies
4
Views
530
Replies
7
Views
356

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,596
Messages
5,832,640
Members
430,150
Latest member
amitk1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back