Self editing formula system, is it possible and how?

[GingerBeardo]

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.

=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.

 

[Marcelo Branco]

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]

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]

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]

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]

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]

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

 

[GingerBeardo]

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