Self editing formula system, is it possible and how?

GingerBeardo

New Member
Joined
Feb 20, 2019
Messages
25
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
Joined
Aug 23, 2010
Messages
17,017
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
25
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
17,017
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
25

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
17,017
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top