Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell to return a specific value if left blank
Just a novice, so please bear with me. I'm trying to set up an inventory
system where 1 is the most common amount, yet I would also like the "quantity" column to reflect the input value if different than 1. Thus I would like to be able to leave a cell blank and return the value of 1, otherwise it returns the value I input. Hop that makes sense. I tried the following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is left blank, then return a value of 1, otherwise, return the value input into A1. Thanks for your help........... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell to return a specific value if left blank
Hello,
If I have undertood what are you looking for it will run. first of all you can not have a value and a formula in the same cell, a1 for instance, in this case we have to use an auxiliar column to help us lets assume column B as auxiliar, so in b1 use =if(a1="",1,a1) Hope this helps. -- regards from Brazil Thanks in advance for your feedback. Marcelo "Sdtex" escreveu: Just a novice, so please bear with me. I'm trying to set up an inventory system where 1 is the most common amount, yet I would also like the "quantity" column to reflect the input value if different than 1. Thus I would like to be able to leave a cell blank and return the value of 1, otherwise it returns the value I input. Hop that makes sense. I tried the following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is left blank, then return a value of 1, otherwise, return the value input into A1. Thanks for your help........... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell to return a specific value if left blank
=IF(A1="",1,A1)
-- If this post helps click Yes --------------- Jacob Skaria "Sdtex" wrote: Just a novice, so please bear with me. I'm trying to set up an inventory system where 1 is the most common amount, yet I would also like the "quantity" column to reflect the input value if different than 1. Thus I would like to be able to leave a cell blank and return the value of 1, otherwise it returns the value I input. Hop that makes sense. I tried the following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is left blank, then return a value of 1, otherwise, return the value input into A1. Thanks for your help........... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell to return a specific value if left blank
You were very close, try this
=IF(A1="",1,A1) Mike "Sdtex" wrote: Just a novice, so please bear with me. I'm trying to set up an inventory system where 1 is the most common amount, yet I would also like the "quantity" column to reflect the input value if different than 1. Thus I would like to be able to leave a cell blank and return the value of 1, otherwise it returns the value I input. Hop that makes sense. I tried the following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is left blank, then return a value of 1, otherwise, return the value input into A1. Thanks for your help........... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell to return a specific value if left blank
Your formula =IF(A1="",1,A1)
And so should these =A1+ISBLANK(A1) =A1+(A1<1) ... this is the shortest best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Sdtex" wrote in message ... Just a novice, so please bear with me. I'm trying to set up an inventory system where 1 is the most common amount, yet I would also like the "quantity" column to reflect the input value if different than 1. Thus I would like to be able to leave a cell blank and return the value of 1, otherwise it returns the value I input. Hop that makes sense. I tried the following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is left blank, then return a value of 1, otherwise, return the value input into A1. Thanks for your help........... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell to return a specific value if left blank
Thanks to all who have helped. I'm assuming that all those who have
responded with alternative formula's, also agree with Marcelo. By using this auxiliary column, I now will have an additional column with a quantity showing any quantity if other than 1, right. i.e. if I use Col. B as suggested, I will show the number 5(for example) in both columns A & B, right? One that I input and one that is read via the formula in column A. Will this be the case if i use the "ISBLANK" formula, or will I still have a circular reference error? Thanks again to all!! "Marcelo" wrote: Hello, If I have undertood what are you looking for it will run. first of all you can not have a value and a formula in the same cell, a1 for instance, in this case we have to use an auxiliar column to help us lets assume column B as auxiliar, so in b1 use =if(a1="",1,a1) Hope this helps. -- regards from Brazil Thanks in advance for your feedback. Marcelo "Sdtex" escreveu: Just a novice, so please bear with me. I'm trying to set up an inventory system where 1 is the most common amount, yet I would also like the "quantity" column to reflect the input value if different than 1. Thus I would like to be able to leave a cell blank and return the value of 1, otherwise it returns the value I input. Hop that makes sense. I tried the following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is left blank, then return a value of 1, otherwise, return the value input into A1. Thanks for your help........... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell to return a specific value if left blank
OR
As you mentioned "One that I input and one that is read via the formula in column A." In the formula you are using you can check for blank and consider the default as 1. using same IF condition//// -- If this post helps click Yes --------------- Jacob Skaria "Sdtex" wrote: Thanks to all who have helped. I'm assuming that all those who have responded with alternative formula's, also agree with Marcelo. By using this auxiliary column, I now will have an additional column with a quantity showing any quantity if other than 1, right. i.e. if I use Col. B as suggested, I will show the number 5(for example) in both columns A & B, right? One that I input and one that is read via the formula in column A. Will this be the case if i use the "ISBLANK" formula, or will I still have a circular reference error? Thanks again to all!! "Marcelo" wrote: Hello, If I have undertood what are you looking for it will run. first of all you can not have a value and a formula in the same cell, a1 for instance, in this case we have to use an auxiliar column to help us lets assume column B as auxiliar, so in b1 use =if(a1="",1,a1) Hope this helps. -- regards from Brazil Thanks in advance for your feedback. Marcelo "Sdtex" escreveu: Just a novice, so please bear with me. I'm trying to set up an inventory system where 1 is the most common amount, yet I would also like the "quantity" column to reflect the input value if different than 1. Thus I would like to be able to leave a cell blank and return the value of 1, otherwise it returns the value I input. Hop that makes sense. I tried the following =IF(A1,"",1,A1), thinking the double quotes would equate to a blank cell, but I may be waaay off base here. So, I'm thinking this reads: If A1 is left blank, then return a value of 1, otherwise, return the value input into A1. Thanks for your help........... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return a blank formula cell if the reference is blank? | Excel Worksheet Functions | |||
Display error message if a cell is left blank | Excel Discussion (Misc queries) | |||
stop a clicked on cell being left blank | Excel Worksheet Functions | |||
formula to lookup value and return value from cell at left of target | Excel Worksheet Functions | |||
return left most part of cell | Excel Worksheet Functions |