formula changes can be made in 1 box and it effects the whole sheet

Hi, I have a worksheet with 100+ cells in it and it is a work in progress.

I am using an IF statement to calculate the result. so example IF A1=1, then multiply by A2, if A1=2 then multiply by A3 and so on.

My Formula:
=
IF(G15=1 SETTINGS!$H$5,B20*$F$3,IF(G15=2,B20*$F$5,IF(G15=3,B20*$F$6,IF(G15=4,B20*$F$7,IF(G15=5,B20*$F$8,IF(G15=6,B20*$F$9,
IF(G15=1N SETTINGS!$K$5,B20*$I$3,IF(G15=$H$5,B20*$I$5,IF(G15=$H$6,B20*$I$6,IF(G15=$H$7,B20*$I$7,IF(G15=$H$8,B20*$I$8,IF(G15=$H$9,B20*$I$9,
IF(G15=1R SETTINGS!$N$5,B20*$L$3,IF(G15=$K$5,B20*$L$5,IF(G15=$K$6,B20*$L$6,IF(G15=$K$7,B20*$L$7,IF(G15=$K$8,B20*$L$8,IF(G15=$K$9,B20*$L$9,
IF(G15=1RN SETTINGS!$Q$5,B20*$O$3,IF(G15=$N$5,B20*$O$5,IF(G15=$N$6,B20*$O$6,IF(G15=$N$7,B20*$O$7,IF(G15=$N$8,B20*$O$8,IF(G15=$N$9,B20*$O$9,
IF(G15=0,"0"
)))))))))))))))))))))))))

My Problem: As this is an ongoing process and continually i need to make modifications to the formula (ie edit by adding or deleting parts) and this formula is used in over 100+ cells, i have to made the modification then got through all 100+ cells and re-paste the updated formula in.

Solution: I am looking for a way that i can simply edit the formula in 1 MASTER cell that then takes effect in all the other 100+ cells (Keeping in mind each of the 100+ cells that has the formula has a different cell to calculate from, eg from the formula above, G15 is always changing).

Can anyone help?

Thankyou

Right off the bat, I see that you are missing "," in the first IF()
=IF(G15=1, SETTINGS!$H$5,B20*$F$3,

Also the 1 is numeric which is OK as is, however, 1N is text so you must enclose it within quotes, "1N" as well as the "1R" and "1RN"

Also the "," between the $H$5 and B20*$F$3 becomes the "value if true" and B20*$F$3 becomes the "value if false", there fore it would need a closing parenthesis, or it has to go into the first IF statement's value if false just before the last parenthesis. The same problem in numerous IF statements. Your last IF statement lacks value if false, was it by design or you forgot?

Since you did not include the worksheets used, I could not help you further.

RJ

Hi RJK

Thankyou for the great feedback, i just noticed i had copied the incorrect formula here, with your help i have a fully functioning formula. Would you know if there is a way that i can edit that formula in 1 cell and it changes in all the other cells?

FDibbins, thankyou, i have attached the file to this.
The red text is the formula.

I will explore the vlookup idea thankyou

frmula.xlsx

Thankyou very much, the Vlookup has worked perfectly

Thankyou

*i will mark this post as solved and begin a new one, want to know if there is a way to send the entries on days (each box of what you have seen in the xls) to particular sheets (where i can choose which sheet to send it to)