# 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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### 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
7
Views
102
Replies
6
Views
468
Replies
4
Views
73
Replies
4
Views
397
Replies
0
Views
458