+ Reply to Thread
Results 1 to 20 of 20

Enter number dependant on value in another cell

  1. #1
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Unhappy Enter number dependant on value in another cell

    I'm new to using the functions in Excel so i had a go at this from online resouces and cant get it to work

    I am wanting the value that is displayed in cell C14 to be dependant on the value that is entered in cell I4.

    If the value in I4 is 10 or less, i want the value in C14 to be blank, if I4 is greater or equal to 11, i want the value in C4 to be 8

    this is what i have at the moment but it says invalid function??

    =IF($I$4>=11, $C14, "8")-IF($I$4<=10, $C14, "")

    hope you can help!

  2. #2
    Forum Expert 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: Enter number dependant on value in another cell

    In C14, try

    =IF($I$4>=11,8,IF($I$4<=10, ""))
    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
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    thank you so much

    and how would you enter a range, ie

    again if I am wanting the value that is displayed in cell C14 to be dependant on the value that is entered in cell I4.

    If the value in I4 is between 10 and 16, i want the value in C14 to be 4, if the value in I4 is outside of that range (less than 9 or greater than 17) I want it to be blank?

    Is that possible?

    thanks

    Zoe

  4. #4
    Forum Expert 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: Enter number dependant on value in another cell

    =if(and(i4>=10,i4<=16),4,if(or(i4<=9,i4>=17),""))

  5. #5
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    fabulous! thank you works just as wanted!

    and the last one, which means i can do my spreadsheet,

    again if I am wanting the value that is displayed in cell C14 to be dependant on the value that is entered in cell I4, but with two ranges

    eg If the value in I4 is between 10 and 16 or 20 and 23, i want the value in C14 to be 4, if the value in I4 is outside of that range (upto 9, 17-19 or greater than 24) I want it to be blank?

    Is that variation possible?

    thanks for your help :D

    Zoe

  6. #6
    Forum Expert 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: Enter number dependant on value in another cell

    =if(and(i4>=10,i4<=16),4,if(or(i4<=9,i4>=17),""))

  7. #7
    Forum Expert 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: Enter number dependant on value in another cell

    Apologize. Wrong copy....

    =IF(AND(OR(I4>=10,I4<=16),AND(I4>=20,I4<=23)),4,"")

  8. #8
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    Hiya

    is the 2nd one right?

    If i enter a value between 16 and 19 it works fine and shows a 4 in the cell J4, but it doesnt seem to show anything if i enter a value between 6 and 10 ?

    this is what i entered, wanting a 4 entered if value is between 6 - 10 and 16 - 19 in cell J4

    =IF(AND(OR(J4>=6,J4<=10),AND(J4>=16,J4<=19)),"4","")

    thanks

  9. #9
    Forum Expert 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: Enter number dependant on value in another cell

    ....this is what i entered, wanting a 4 entered if value is between 6 - 10 and 16 - 19 in cell j4

    =if(and(or(j4>=6,j4<=10),and(j4>=16,j4<=19)),"4","")
    =if(and(j4>=6,j4<=10),4,if(and(j4>=16,j4<=19),4,""))

  10. #10
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    works a treat !

    thank you

    if there's any more i'll ask

    Merry Xmas

  11. #11
    Forum Expert 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: Enter number dependant on value in another cell

    You are welcome!

    Thanks for the reb*

    Merry Christmas to you too!

  12. #12
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    aaahh sorry just one more question......i've entered this in one of the cells:

    =IF(AND($K$4=1),"1R",IF(AND($K$4=2),"2R",IF(AND($K$4=3),"3R",IF(AND($K$4=4),"4R",IF(AND($K$4=5),"5R",IF(AND($K$4=6),"1R",""))))))

    and it works fine, again it enters a value dependant on what is entered in cell K4, but if i add another IF(AND($K$4=6),"1R", to it it wont accept it, is 6 the maximum number of these you can enter into one equation?

    I wanted a value, lets say A, B , C, D or E to be entered in the cell dependant on the value entered in K4, so lets say, 1,5,9,15 = A, and 2,6,10,16 = B, 3,7,11,17 = C, etc?

    can this be done or does it contain too many variables for one equation?

    thanks again

    Zoe

  13. #13
    Forum Expert 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: Enter number dependant on value in another cell

    You don't need AND for this . Just IF! Something like this.

    =IF($K$4=1,"1R",IF($K$4=2,"2R",IF($K$4=3,"3R",IF($K$4=4,"4R",IF($K$4=5,"5R",IF($K$4=6,"1R",""))))))

    But in this case my suggestion is to create a table and then use VLOOKUP or INDEX & MATCH for your result.

    As you use Excel 2000, you have a limit of 7 if!

  14. #14
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    I'll update my Excel i think! lol

    I'll have a read up on Index and Match as well, thanks for that, i'm sure i'll have some questions about it

    Happy Holidays

  15. #15
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    Hiya....another query for you if you dont mind:

    I have this in cell B51 at the moment;

    =IF($C$48="","",IF($C$48>=1,"YES"))

    so if the cell C48 is blank, B51 remains blank, is there is a value is C48 then the message YES appears in B51.

    Now how do i do this to include two cells, ie

    as well as C48, include B48? if either of those two cells has a value in then show the message YES in B51, if both these cells are blank then B51 stays blank?
    Only one of the cells, B48 and C48, will have a value in it at one time

    many thanks :D

    PS i've just purchased Excel 2010 so i should be more flexible now!

  16. #16
    Forum Expert 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: Enter number dependant on value in another cell

    Maybe...

    =IF(AND(B48="",C48=""),"",IF(OR(B48<>"",C48<>""),"YES"))

  17. #17
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    Hiya

    I'm setting out some cells using the Match and Index function and have got it working lovely, just one issue:

    I have this in cell C17:

    =INDEX($L$19:$X$19,MATCH(I5,$L$18:$X$18,0))

    which puts the value in that cell according to what has been entered in I5.

    The resulting value is a number, so lets say 12 appears there, and in the cell below it, C18, i have this:

    =IF(C17<="","",SUM(C17*$I$8))

    which basically multiplies the value shown in C17 by the number entered in cell I8.........

    now this worked fine using the IF command in cell C17, but with the INDEX and MATCH C18 stays blank regardless of what is shown in C17?

    Is this because its not a number that being displayed, just a 'item'? i know with the IF command i had to put it into inverted commas, ie "" to make it count as a numerical value

    Is that what i need to do with this INDEX and MATCH command, tell it that the figure displayed is classed as a numerical value?

    If so, do you know how i do that?

    thanks

  18. #18
    Forum Expert 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: Enter number dependant on value in another cell

    As i "hear" it, try

    =IF(C17="","",SUM(C17*$I$8))

    Are you sure that C17 value is number? For testing this, use in an empty cell, this: =isnumber(c17)

  19. #19
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    Hiya

    i got round it by adding the function as this:

    =INDEX($L$19:$X$19*$I$8,MATCH($I$5,$L$18:$X$18,0))

    so it looks up the same value in the relavant table, but multiplies it by the entry in I8

  20. #20
    Registered User
    Join Date
    12-24-2012
    Location
    Chesterfild
    MS-Off Ver
    Excel 2000
    Posts
    71

    Re: Enter number dependant on value in another cell

    Happy New Year

    I have another question about the Index and Search, i'm using this in cell J22

    =INDEX(T24:AF24*$N$7,MATCH($O$4,T4:AF4,0))

    which works fine, but one of the cells in the indexed range, lets say U24 is blank, so when it indexes to that cell its puts a 0 in the cell J22

    Is there any way to make it so that if any cell in the index range is blank, to make the target cell J22 stay blank as well and not put a 0 in there?

    thanks

+ 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