default values in a cell

Hello,  can you help me please

Cell B1 contains a complex mathematical formula which requires (in several 
places) a number from cell A1.

Cell A1 can contain any integer number, but it is usually the same (30).

I would like to be able to leave cell A1 empty, and only enter a number when 
it is not 30 ,

ie the default value of A1 is 30, unless specified.

How do I go about this ?

Should I look at conditional formatting, or put lots of IF functions into an 
already complex formula?

Thank as always

KK




0
trebor (88)
10/31/2009 1:59:59 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
805 Views

Similar Articles

[PageSpeed] 13

Use 2 cells

Modify the complex formula to use B1 rather than A1 ( or any other un-used 
cell)

In B1 enter:

=IF(A1="",30,A1)
-- 
Gary''s Student - gsnu200908


"KRK" wrote:

> Hello,  can you help me please
> 
> Cell B1 contains a complex mathematical formula which requires (in several 
> places) a number from cell A1.
> 
> Cell A1 can contain any integer number, but it is usually the same (30).
> 
> I would like to be able to leave cell A1 empty, and only enter a number when 
> it is not 30 ,
> 
> ie the default value of A1 is 30, unless specified.
> 
> How do I go about this ?
> 
> Should I look at conditional formatting, or put lots of IF functions into an 
> already complex formula?
> 
> Thank as always
> 
> KK
> 
> 
> 
> 
> .
> 
0
GarysStudent (1572)
10/31/2009 5:28:08 PM
Reply:

Similar Artilces:

Random Locked cells
I am having a problem with a spreadsheet that has random cells locked. The only way to unlock them is to copy and paste a blank cell that isn't locked into the cell that is locked, but this doesn't always work either Has anyone had this type of problem? Any ideas Thanks Leonar lmack@usccs.co Hi Leonard Maybe I don't understand you correct but if you select a cell or cells and right click on it and choose Format Cells you can change the locked on the Protection Tab -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Leonard Mack" <lmac...

selecting cells #4
When holding down ctrl to select a number of cells to format them or see the sum in the bottom of the screen - and you accidentally select one you dont want - is there a way to unselect that one without starting over ? Thanks, Yosef Take a look at this: http://www.cpearson.com/excel/unselect.htm there's no builtin way to do it, other than starting over... -- Regards, Juan Pablo Gonz�lez Excel MVP "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:9EA0DDD8-4668-430B-BB5E-C15549FC9346@microsoft.com... > When holding down ctrl to select a number ...

format cell type
Hi everyone, in a spreadsheet, I have a complex data type that I want to sort correctly. The format contains a prefix that contains both letters(A) and numbers(0) followed by a suffix after a hyphen and another number. Example: 000A0000-1 and 000A0000-10 The thing Excel is messing up in, is if the complete prefix (000A0000) is the same, but a different number after the hyphen, it sorts it 1, 10, 11, 12, 13, ..., 2, 20, 21, 22, ... 3, etc. I was wondering if anyone knew how to set up a custom format type that will allow a suffix stored as text and the suffix after the hyphen stored as a n...

VLookUp and DropDown List in the same cell
Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for s...

Error on default installation
Hi there. First of all let me explain that working with MS CRM is new for me and my first time. I've just installed the default MS CRM database and created the 6 users, etc. I am now trying to create a new email/lead/topic/etc. When doing that I get an error ======================================================= Server Error in '/' Application. -------------------------------------------------------------------------------- A potentially dangerous Request.Form value was detected from the client (crmFormSubmitXml="<emailactivity><acti..."). Description: Req...

Sorting cells #2
When you try to sort certain data by selecting the cells, but leaving adjacent cells unselected, you get a "sort warning" asking if you would like to expand the selection. How do you stop this from happening? Joey If you are selecting cells in one column or row only you will get this message. I have never found a way to prevent it. Hopefully we both can learn something here. Gord Dibben Excel MVP On Mon, 24 Jan 2005 13:39:05 -0800, "JoeyJoeJoe" <JoeyJoeJoe@discussions.microsoft.com> wrote: >When you try to sort certain data by selecting the cells, but lea...

Pivot Table Data Source, point to cell?
Is there any way to point the data source block to a cell. I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table and changing from the wizard. Is there a way to do this? Example: Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536 Data source in pivot table points to cell A1 to get the range. please tell me there's a way to do this.... thanx.! hi, Jeremy ! > Is there any way to point the data source block to a cell. > I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table >...

Default Site ID Lookup
When accessing Transactions -> Inventory -> Transaction Entry and you click on the Default Site ID lookup window, you are not able to sort by Description. With a company that has multiple locations (100s) setup, it is very painful to scroll through the list. This sort is unavailable on both the SmartList Alternate GP Window sites lookup and the basic GP window sites lookup. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane....

Select reciptients default folder.
In Outlook express 6 if I click "Create Mail" I get the New message box if I then click the "To:" tab it opens the Select Recipients box. I have a number of folder in my address book but the select recipients box always defaults to my contacts. Can I set it so that it always defaults to my folder called Work as this is the one I use the most. Thanks. Steve <swise@autogas.co.uk> wrote: > In Outlook express 6 if I click "Create Mail" I get the New message > box if I then click the "To:" tab it opens the Select Recipients box. > >...

CMFCPropertyGridCtrl
Hi All, I have an MFC application with a TreeView and a CDockablePane Properties Window. When I select a tree item - I display its properties in the properties window. That works. But I don't know how to change the tree item properties when I change the values in the properties window. My problem is in getting the values from the properties grid. I tried to override EndEditItem method like this: BOOL CMyMFCPropertyGridCtrl::EndEditItem(BOOL bUpdateData) { CMFCPropertyGridProperty* currProperty = GetCurSel(); if(currProperty) { //I don't like this implementa...

How do I display a text message when a cell has a certain value??
Hi, I have a cell where if the cell is empty (A1=0), then I would like the cell to display "Enter your value here." Otherwise, I would like the cell to display the value a person enters. I have tried using an if/then/else statement and, understandably, I get a circular reference error: =IF(A1=0,"Enter your value here",A1) Does anyone have a suggestion? Thanks! Why not just enter the text itself? The value entered by the user will displace (replace) it. This should work if the form is a template. -- HTH, RD ===================================================...

Counting Text Cells #2
Hi Guys, How can I determine the most common occurence of a word in a lis formatted as text in Column A. I would like a function that simpl returns the word that occurs the most to appear in cell D1. Also, i there are two words that occur the same number of times in this list, would like both words to be shown (one in D1, one in D2). Any suggestions? Thanks, Be -- Message posted from http://www.ExcelForum.com Hi to get the most frequent text entry try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX($B$1:$B$20,MODE(MATCH(IF($B$1:$B$20<>"",$B$1:$B$...

Why doesn't what I enter in the function bar appear in the cell?
I have a workbook that contains several copies of a master template worksheet. On one of these sheets when I enter text, it appears correctly in the function bar, but appears as a string of &s in the cell. This has only just occurred, and as far as I know used to work OK, i.e. it used to appear as text in the cell. What could cause this and what do I need to do to put it right? You wrote that the text appears as apersands (&). Did you really mean octothorpes (#)? If yes, then try formatting that cell as General--or anything but Text. Chris Mitchell wrote: > > I...

referring to different variable's values in a loop
Hi all, I'm trying to refer to a different variable each time I loop through a procedure, as below: I'm trying to loop through the different Menu1ItemCount, Menu2ItemCount etc variables so as to determine how many times to loop through one part of the code. So basically I want the variable CurrMenu to be able to equal the variables Menu1ItemCount, Menu2ItemCount etc. So I've tried to do that by "CurrMenu = "Menu" & MenuNo & "ItemCount" where MenuNo being the 1, 2, 3 etc so then CurrMenu will reflect the value of the correct variable - not the tex...

assigning a null value of a date field to a date variable
Hi, At some point in the execution of my program I open a dialog box were the user is supposed to enter some info. Among other things he is asked to enter a date in a textbox, but this is optional. When I try to assign this date to a date variable I get the message “Invalid use of null” in the case the user has not filled in any date. If I use the Nz function, a value of 0 is assigned to the date and this later shows as December 30 1899 which I don’t want. How can I deal with this situation? Thanks in advance, George You cannot assign the Null value to a VBA variable of type Date. ...

Adding text to a vlookup cell
Can a cell be edited if the contents are the result of a vlookup function and still keep the rseults? I have 2003 on an XP Pro PC. My formula is =IF(A4<>"",VLOOKUP(A4,InvDetail!$A$2:$B$3000,2,FALSE),"") I trying to create an availability list for our customers but need to add comments to the end of some items. I know I can add another column but the customers are used to the comment being after the item description and space on the page is limited. Thanks, Lee Coleman If you're talking about entering comments manually, then you'd need to concatenate th...

Reverse the order of values
Office XP. I am trying to reverse the values in a column of cells from positive to negative & neg to pos. Excel HELP tells me to go to the FORMAT menu & click Selected Axis then click the Scale tab. These options are not listed on the FORMAT menu. I tried going to the TOOLS menu & selecting CUSTOMIZE but I can't find where to add Selected Axis & Scale. Am I in the right place? Put -1 in an empty cell, copy it, then select the entire set of data and do Edit / paste Special / Multiply. Negs will turn pos and pos will turn neg, zero unaffected. -- Regards ...

Replace Option Group Value from form with text in report header
Hi all, Question: How can I convert the numerical option group value from a form to a string value and place it in the control source of a textbox in a report header? I have a form used to 'build' reports: users choose a query, a report, and then the rest of the controls on the form are used to build a 'where' clause for the DoCmd.OpenReports statement attached to a command button which opens the report based on all the user's choices. I've tried creating a variable on the report in the declarations area and then using a select case statement in the 'open&...

Need help formatting a cell.
Can such a request be done using Excel? I need to enter a time in Cell B18 of 2325 or 11:25pm from that time i need it to calculate that cell B17 is 15 minutes prior to B18 (2310 or 11:10pm) B16 is 20 minutes prior to B18 (2305 or 11:05pm) B15 is 45 minutes prior to B18 (2240 or 10:40pm) B14 is 1 hour prior to B18 (2225 or 10:25pm) B13 is 1 hour 10 minutes prior to B18 (2215 or 10:15pm) and so one...... When i will need to change cell b18 to another time.... I need all my cells to still have the same minutes prior to be calculated and deducted. Sorry if questions is compliacted and conf...

How do i select different cells to be use in a formula.
Hi I need to know how to select different cells to be used in a formula, but i don't know what is the separator that i need to use to this, see the example bellow Cells that i need to include on my formula: A1 C5 BH32 Thanks. Hi you didn't mention what formula but here's a starting point: =AVERAGE(A1,C5,BH32) or depending on your regional settings =AVERAGE(A1;C5;BH32) hope this helps Cheers JulieD "Manuel" <Manuel@discussions.microsoft.com> wrote in message news:A855A562-725C-4B10-B111-A88F7A767122@microsoft.com... > Hi > > I need to know ho...

ugly grey default background on IE7?
On my work computer, we recently updated to IE7. Now, on some web pages the background is a dull light grey instead of white. (but not on all) I've narrowed it down to mainly webpages that have no bgcolor named in the body tag. What's weird is mine is the only one that it is defunked on, and today I noticed while changing backgrounds of the Outlook Today page in my Outlook 2003 that the "Standard" pages have the grey BG too. There must be a connection because it too was working fine until we installed IE7. I've tried everything in the Theme and Appearance tabs in...

Default folder
How do I get Excel to go to my Excel files folder and not to "My Documents?". Click Tools > Options > General tab Define the path to the folder as desired in the box for "Default file location" Click OK -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com ---- "Sam" wrote: >How do I get Excel to go to my Excel files folder and not >to "My Documents?". Change: Tools|Options|General tab|Default file location to what you want your default folder to be. Sam wrote: > > How do I get ...

Do not display Value label if value = zero
Hi I am putting together a pie chart, where values for some slices is 'zero'. The zero slice is not visible, but the label still appears '0%'. Is there any way of suppressing this label for the zero slice only (so the other slices still have value labels appearing) ? Many thanks for any help Format label, custom format: #;;; -- Best Regards, Luke M "LFNFan" <LFNFan@discussions.microsoft.com> wrote in message news:CC677569-1FB1-4108-8535-C5BDC6E1B825@microsoft.com... > Hi > > I am putting together a pie chart, where values ...

cell color #5
Dear All, My OS is WindowsXP pro and Office is 2003. In WindowsXP, there are many users, now there is one user in Excel, after apply the fill color, the color can not appear, only in Print Preview can see the color applied. help pls. thank you berry See this: http://support.microsoft.com/?id=320531 Biff "berrylei" <berrylei@discussions.microsoft.com> wrote in message news:7EE1354C-542D-4EDB-BDE6-52FDB6A3C78B@microsoft.com... > Dear All, > > My OS is WindowsXP pro and Office is 2003. > In WindowsXP, there are many users, now there is one user > in Excel...

Run macro based on cell value in worksheet
Does anyone know of a way to do run a macro based on a cell value. EG i cell A1 = 0, dont run macro - otherwise run macro -- Message posted from http://www.ExcelForum.com Does anyone know of a way to do run a macro based on a cell value. EG i cell A1 = 0, dont run macro - otherwise run macro. below macro will run your code is cell a1 is anything but 0. Do you want the macro to run automatically if a different value i inputted into cell a1, or are you happy for the user to run the macr manually? (if automatically, more code is required) sub Macro 1 () if cells(1,1) = 0 then exit sub &...