+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-28-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2013 (PC)
    Posts
    24

    Question 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. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    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.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    01-28-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2013 (PC)
    Posts
    24

    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. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    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. #5
    Registered User
    Join Date
    01-28-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2013 (PC)
    Posts
    24

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

    Thanks again, appreciate your time.

    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.
    Last edited by suxrule; 06-20-2012 at 08:34 AM.

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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