# Changing a formula in a cell by entering a number in another cell, is it possible?

1. ## Changing a formula in a cell by entering a number in another cell, is it possible?

OK I was on here the other day and I got some great advice on a spreadsheet I'm working on for work. So I'm now looking to make it a bit simpler so any advice would be greatly appreciated.

When the % in B22 is within 5% either side of 43% (ideal split, B20) then it turns Green
When the % in B22 is within 13% either side of 43% (ideal split, B20) then it turns Amber

Same for E22, H22 & K22, the ideal % in each respectively is 4%, 4% & 49%

This has been put in place using a conditional format for each of the cells B22, E22, H22, K22 but has to be edited for each.

Here is the formula used for to turn Amber in B22 "=OR(AND(B22>=0.3,B22<=0.379),AND(B22>=0.485,B22<=0.56))"
and the 2nd rule to turn Green "=AND(B22>=0.38,B22<=0.484)"

Obviously this is fine for the 1 example department I'm using but we have over 20+ department that I would like to use this spreadsheet for & each has a different ideal split % so this could be very time consuming

I'm wondering if there is any way that me changing the ideal split % in cell B20 I could get the rules or formula to change in B22 so I wouldn't have to change the rules for every department, also this would make it a lot user friendly for anyone else to use?

I hope I've explained my self well enough and hopefully there is a solution to my problem but I do understand this could be quiet complicated to implement.

Here is a copy of my spreadsheet so far RHRP demo.xlsx

Any help much appreciated,
Ewen

2. ## Re: Changing a formula in a cell by entering a number in another cell, is it possible?

Hi

If i understand well your problem, in Cf rules, you can use cell referencs instead of numbers.. Use a hidden column, so in every sheet you can use differents values..

Take a look to the example,pls.

3. ## Re: Changing a formula in a cell by entering a number in another cell, is it possible?

Thanks that certainly speeds up my process no end, much appreciated

Is there a formula that I could perhaps use in B20 to change the values in S1, 2, 3 & 4 when I enter the ideal split % in B20?

Using my rules of 13% either side of the ideal split for Amber and 5% either side for Green?

Thanks again,
Ewen

4. ## Re: Changing a formula in a cell by entering a number in another cell, is it possible?

for your first question, maybe something like This in S1,2,3,4...

[B]=IF(AND(B20>0,B20<=30),0.3,IF(AND(B20>30,B20<=50),0.4)).....[/B

Sorry but i don't understand your second question...

5. ## Re: Changing a formula in a cell by entering a number in another cell, is it possible?

The idea is if i enter 43 (for example) in to B20, then B22 would turn Green within 5 either side of 43 (range 38 to 48)
It would turn Amber within 13 either side of 43 (range 30 to 37 and 49 to 56)

What I'm hoping is to enter any value in to B20 and it would follow these sets of rules in B22

Sorry if I'm not being very clear.

6. ## Re: Changing a formula in a cell by entering a number in another cell, is it possible?

..Sorry if I'm not being very clear.
Don't be sure for this.....

Probably my Excel skills are not so good, but for sure, my poor English don't let me to follow you...Sorry..

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1