The worksheet (Payments) contains a list that allows the user to input accounting transactions. Almost every input cell contains a formula or data validation enabling selection from a dropdown.
The worksheet feeds the data to 3 separate spreadsheets (Debits / Credits / VAT) and the formulas for that are contained within dynamic ranges in the other 3 sheets. A command button macro then feeds that data from the 3 sheets to the Master sheet. This all works perfectly.
After the macro is run and the data is copied to the Master sheet, I would like all of the data in the 4 other sheets to be cleared without losing the formulas and data validation, ie so that the sheets are empty and clear for the next batch of inputs.
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
Probably a simple method that i have not tried. I have a table where some cells are populated with #N/A. I am trying to work out a formula in another cell saying if the cell that contains #N/A then leave this cell blank.
Its probably very simple question, but i can not work it out.
I am trying to design a simple worksheet so I can keep track of how much money my room mate has paid me and what she owes. She pays $142 a week rent plus $23 a month for internet.
Please open the attached workbook. You can see in E7 and 8 there is a red negative number. This is because my tenant hasn't paid me yet for those weeks (D7 & 8).
How can i get these cells to read $0 or leave the cell empty?
The reason why I need this to read $0 or nothing is because it is messing up my next calculation in D3 where it works out how much she has paid for our internet bill.
Thanking you in advance. And sorry if this is a really daft question. Its probably something really simple i have missed.
I am creating a worksheet for my buisness and I have input date of birth and have done a formula to work out ages but I dont know how to hide the formula and leave the cell blank so that when I create new information it automatically puts the age in.
I would like to use the IF function so when cell B2 is empty, cell C2 will stay empty. the formula =MONTH(B2&C2) need to be used when cell B2 is not empty. this to convert text to number in column C
A1 has a date of 03/10/07 B2 has formula =A1 + 7 I want to copy that formula down the column B2 but if B3 is empty I get 01/07/1900 How Do I make so that if there's no date entered in A column I get blank cells but still retain formulas, I've been running into this issue with other formulas but I couldn't find any solution searching this forum,
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
I'm trying to create a worksheet to automatically make a football league table update once results are entered. I have entered a formula using the "IF" function which compares scores and then awards points. However before a game is played and the cells are left blank, the formula detects this as a "draw" and awards a point. how to prevent the formula operating until a result is entered?
I am trying to write a macro to find all cells that are returning cell errors (ie: #REF!) within a named range, to select only those cells, and then to clear the contents of only these cells. It is my understanding that replacing or altering the value is not an option because doing so will return a "" string value, instead of a clear cell. Replacing the cell's contents with a =na() is also unacceptable...these cells to be completely clear. I was unable to piece together a macro from searching around this site and thought it would be best to just ask.
On the attached spread sheet here is what I am trying to do. In B2 I will either enter D (digital label) or F (Flexo label). With that than if a D is entered nothing else will happen with in spread sheet but if F is entered D23:Q40 will need to be cleared out and blank.
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
I am using Excel 2010. I am extracting text and data info from a single cell and inserting the info into different cells. Let's say the following formula is entered into cell M28:
This tests cell J28 to see if the text string "IM" is present in that cell.
If it is, then the text "IM" plus the following eight characters become the result in cell M28.
If the text string "IM" is not present in cell J28, I want the contents of cell M28, that is the formula itself, to be deleted from cell M28. EG below:
=IF(MID(J28, FIND("IM", J28),2)="IM", MID(J28, FIND("IM", J28),10), function here to delete this formula from this cell) ( logical test ) ( value if true ) ( value if false )
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
I am making a workbook where the G column is drop-down menus that are populate a drop down on the H column. Whenever someone changes the selection of the G column I was to erase what is in the corresponding H column. I have this code that works, but I need to do this for 100 lines. Is there any way to put this into a loop instead of writing 100 if statements?
I have text in column F (cells 20 to 199) that changes automatically based on pre-determined time constraints (this is done via an IF formula). I want the 4 cells adjacent to F to clear contents when the text in F changes. For example when cell F126 changes text automatically, G126, H126, I126 and J126 all clear. If cell F99 changes, cells G99, H99, I99 and J99 clear. And so on.
I am quite a novice with excel and I am trying to get a cell to show blank when I select a drop down list value in another cell.
Cell D2 contains a validated list containing two items (Air, Vacuum).
If I select Vacuum I wish to change another cells (D4) contents to blank, D4 is also a validated list containing three items (Yes, No, BLANK) the blank is actually a blank space and not the text blank.
I can get the cell to operate correctly but if I select Air in Cell D2 and then Yes in cell D4, but then change my mind and reset to Vacuum in cell D2 i need the cell D4 to clear its contents automatically.
I'm having a problem with a macro clearing a formula in a cell. I have the same type of cell that doesn't have the problem but I can't find the difference between the 2 cells or difference in vb that's making it happen. I have to intentionally cause this to happen but don't see why it's happening. Do I need to attach workbook and describe what's happening? I have been copying and pasting from different sources as well as paying to have it created/started but it was expensive(for me) and I make nothing off of it, just use it at work. I am not proficient in Excel or vb but I'm desperately trying to learn as I go so as not to fork out a few hundred dollars again.
I have created a worksheet with many calculations based on user input into several unlocked cells. I would like to create a cell that when selected, would clear the contents of this group of unlocked cells.
I have two rows of numbers, say D1 to F1 and D2 to F2. Row D2 to F2 will always have lower values.
The range below it is D3 to F100. I want the range to clear the contents of the cells in the range where:
a) the cell is > the x1 cell above it
OR b) the cell is < the x2 cell above it
If it is = or between the two cells, the value stays.
Example:
If D1 thru F1 is 4, 5, 7 and D2 thru F2 is 2, 4, 3, then: D3 is 5, it is cleared E3 is 4, it stays F3 is 5, it stays D4 is 2, it stays E4 is 1, it is cleared F4 is 9, it is cleared Etc
Basically, the cell in the range looks up its own column, compares itself to x1 and x2 in that column. If it's greater than or less than, then it clears the contents, if not, the value in the cell stays.
I'm trying to create macro that will clear the contents of the cells in the 4 columns to the left of a cell that = 0 %, as well as the cell that = 0%. For example, if cell F13 = 0 % then the contents of B13:F13 would need to be cleared. The range in which data is being pasted into is B13:F27.
A project I am working on calls for me to implement a macro that will clear the contents of a cell (but not affect the validation list it contains) based on the selection of another cell or cells. So on the simplified attached example, I need to achieve the following;
On the fist row of options, if cell "C3" is selected, then the contents in cell "D3" are cleared. If cell "B3" is selected then the contents in cells "C3" AND "D3" are both cleared. Similar is needed for the second row of options;
If Cell "C6" is selected, then the contents in cell "D6" are cleared. If cell "B6" is selected then the contents in cells "C6" AND "D6" are both cleared. Finally, if cell "A3" is selected, then ALL of the above cells' (B3-D3 & B6-D6) contents must be cleared.
I am making a spreadsheet for use by my customers. Is there a way to leave cells that have formulas' in blank until the cells that make up the formula have entries in?
