Self editing formula system, is it possible and how?

GingerBeardo

New Member
Joined
Feb 20, 2019
Messages
14
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.
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Feb 20, 2019
Messages
14
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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Feb 20, 2019
Messages
14

ADVERTISEMENT

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
Joined
Aug 23, 2010
Messages
16,393
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,501
Messages
5,523,290
Members
409,508
Latest member
Afc

This Week's Hot Topics

Top