Formula To Keep Adjacent Cell Blank When No Date Is Added
Jan 23, 2014
I have a formula in my spreadsheet that shows dates for servicing of products. Column F show's Date Last Serviced and the adjacent column (G) then shows Date Next Service. When there is nothing on the F column's cell then the date that appears on my spreadsheet is 31/12/00. It looks messy and I would rather have a formula to make it so that if the F column is blank, then the adjacent cell in G column should stay blank also.
To automatically calculate the next service due date, I have variants of this formula that creates the dates in G column:
=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))
I tried adding as an extra part, for example:
=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))&If(F4 = 0, "", F4)
It always brings back the answer of 366, and does not make the cell blank...
View 2 Replies
ADVERTISEMENT
Aug 12, 2014
I have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
View 5 Replies
View Related
Apr 4, 2008
how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.
I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.
CREATE TABLES LIKE BELOW?Column A Column B Column C
March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................
View 4 Replies
View Related
Jan 7, 2013
If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.
View 9 Replies
View Related
Nov 14, 2006
I'd like a macro that cycles down 50000 rows of column B. If it comes across a blank cell it will import the value from its adjacent cell in column A. then the macro will move onto the next cell.
View 9 Replies
View Related
Aug 8, 2008
I am looking for a piece of code that I can use.
Basically if cell A2 is non blank then I want cell C2 to display My Text. I want to do this for every cell down to about A250.
View 4 Replies
View Related
Mar 20, 2008
In one cell the user has to fill in a date, example 20.3.2008 and then in another cell he/she has to choose a month from a list (january-december). I need a macro that checks that the month stated in the date cell is the same as the month selected in the other list cell.
View 3 Replies
View Related
Mar 6, 2014
I want AB1 to highlight red if date in S1 is not blank and N1 doesnt equal AA1. Is this possible?
View 1 Replies
View Related
Feb 19, 2014
I'm trying every conceivable angle I can think of in vba and coming up empty. What I have is a spreadsheet similar to this:
Column A Column B
1. 1234567
2. 2345678
3. 3456789
4. 4567890 A0001
5.
6. 9876543
7. 9876543
8. 9876543
9. 1234578
10. 3456789 A0002
What I would like to do is to fill in B1:B3 with A0001 and B6:B9 with A0002. The number of rows in each set of data will vary, but the identifier (A000X) will always be located in the row in column B adjacent to the last entry for that set of data in column A and there will always be empty cells between data sets. The number of data sets will also vary.
View 5 Replies
View Related
Feb 7, 2014
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
View 4 Replies
View Related
Feb 24, 2009
Ok, I've come close to the solution using the Match function, but somehow just can't quite nail it.
What I need is a formula that will search down Column B until it finds the first blank cell. Then report what is directly to the left of that cell.
So If the first blank cell is in $B$5, it will report what is in $A$5.
View 6 Replies
View Related
Mar 4, 2008
I'm having trouble getting my random number generator functions to use my variables that are defined by text box inputs. I can mouse over the variables in the code and they have the correct values, with the excpetion of where they are listed in the functions. I'm sure I'm missing something simple, thanks in advance for any help!
Private Sub cmdPopulate_Click()
StdDev = txtStdDev.Value
RandomNum = txtRandomNum.Value
Mean = txtMean.Value
lambda = txtLambda.Value
OriginCell = txtOriginCell.Value
If cmbDistributions.Value = "Normal" Then .................
View 9 Replies
View Related
Nov 19, 2013
I have made a table which contains 2 date columns, Both formatted as date (dd-mmm-yy), the second column is calculated as the first column, + 30, The dates work out fine when adding to a date in the first columns, but from a blank cell it displays 30-Jan-00.
e.g
Started
Finishes
12-Nov-13
12-Dec-13
30-Jan-00
09-Nov-13
09-Dec-13
11-Nov-13
11-Dec-13
Rather than delete the rows with the blanks in the Started column, is there a formula that I can use so that the cells in the "Finishes" column is left blank when the "Started" cells are blank?
The current formula for cells in the "Finished" column is:
=[@Started]+30
View 2 Replies
View Related
Mar 16, 2014
I am trying to get VBA code in Excel to copy formula in specific cells to copy the cell below when an entry is added to a database.
View 3 Replies
View Related
Jan 4, 2008
If a cell in collumn B is blank, I want to cut text from column C at the same row and paste to column B.
View 4 Replies
View Related
May 2, 2014
column 1 is vouchers
column 2 is date
vouchers column is populated with a voucher code for every date date column is populated with dates beginning with today, ending with the last day of the year.
I would like to create a function that evaluates the date file, If date = today's date, return value in the adjacent vouchers column.
View 1 Replies
View Related
Oct 31, 2006
Is there a really easy code which will insert the current users ID into a cell adacent to the one previously completed automatically? For example if a date was entered in A2, then B2 would automatically be completed with the user identity?
View 7 Replies
View Related
Feb 28, 2008
formula to auto input the date into a specific cell once one cell has been changed
View 6 Replies
View Related
Apr 26, 2007
I created a macro to save my spreadsheet into another book, but I first want to check if cells Z2 & Z3 are blank, and if Z4 = "$--k". If any of those conditions are true, I want a messagebox sent to the user to please fill in data in those cells, and exiting the sub( ) preventing the save from happening. The messagebox would say "Blank Info", vbOKonly button, Title would be "Fill in data".
View 2 Replies
View Related
Jul 25, 2012
I'd like to sum a range of cells if the date in the adjacent cell is last month, so... A2:A1000 holds the dates and B2:B1000 holds the values, i'm trying to figure out how to sum all of the values for the previous month (i.e. June).
View 7 Replies
View Related
Jun 21, 2009
I have this userform:
DATE : [TextBox1] WITHDRAWAL: [TextBox2]
[Commandbutton1]
In my sheet I have a list of all the days in a year in the cell range of AJ12:AJ376.
Basically I want to find the date in Textbox1 within the range of AJ12:AJ376 and enter the value of Textbox2 in a adjacent cell. The adjacent cell would be in column AT. This would be achieved hopefully via a code using the commandbutton.
View 9 Replies
View Related
Mar 22, 2008
I have a spreadsheet with a column A of dates April 08, May 08, June 08 etc. Adjacent to each of these dates is a value in column B. I want to select the appropriate value relevent to the current date and use it elsewhere. Therefore if it happens to be Oct 08 when I open the spreadsheet I want the value in column B, adjacent to Oct 08 to be represented.
View 3 Replies
View Related
Apr 27, 2008
Two Sheets:
1) Top Sheet
2) IRA
- Cells A4:A10 of sheet "Top Sheet" contain dates. i want B4:B10 to show the IRA value as of the date in column A:
____A__________|_B______________________
4 | 09/22/2005 | $ Total as of date in A4
5 | 10/06/2005 | $Total as of date in A5
6 | 10/20/2005 | $Total as of date in A6
7 | 11/03/2005 | $Total as of date in A7
8 | 11/17/2005 | $Total as of date in A8
9 | 12/01/2005 | $Total as of date in A9
10| 12/15/2005 | $Total as of date in A10
- Cells A6:A12 of sheet 'IRA' also contain dates but not in the same sequence (some date ranges had several transactions), B6:B12 of sheet 'IRA' contains the transaction values during the date range:
____A__________|_B______________________
6 | 09/22/2005 | $100
7 | _ _ _ _ _ _| $200.................................
View 4 Replies
View Related
Jun 5, 2008
I have a list of dates in column AF. I would like to retrieve a value from the 2nd occurrence of a date, so I used the function for the Nth Occurrence on this website as follows:
Function Nth_Occurrence(range_look As Range, find_it As String, _
occurrence As Long, offset_row As Long, offset_col As Long)
Dim lCount As Long
Dim rFound As Range
Set rFound = range_look.Cells(1, 1)
For lCount = 1 To occurrence
Set rFound = range_look. Find(find_it, rFound, xlValues, xlWhole)
Next lCount
Nth_Occurrence = rFound.Offset(offset_row, offset_col)
End Function
The excel formula I am using is as follows: =Nth_Occurrence(AF2:AF622,AE2,2,0,1)
Where AE2 is the date I am looking up. My question is this: When I put in a date ("1/12/2007") instead of AE2, i get the correct value. I tried giving AE2 and the AF column the same date formatting (3/14/2001), but this doesn't work. The date I am searching for also changes, so I cannot just type the date into the excel formula.
View 2 Replies
View Related
Apr 22, 2014
I am wanting to add the value of an adjacent cell and keep the SUM in another cell... depending on that cell value.
For example: H10 value is "JW" .. so that number adjacent (G10) would be added to the cell G4 (next to JW), and same with "NK". If it says "NK/JW" then I need it to be divided equally.
N528MW Revised.xlsx
View 3 Replies
View Related
Aug 2, 2014
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies
View Related
Mar 19, 2009
I want to display the formula in the adjacent box to where it is actually used. Would anyone happen to know how to get that done? Example:
If cell A1 contains the following formula: A1+ A2
I want cell A2 to show : '= A1+A2'
View 4 Replies
View Related
Jul 22, 2008
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
View 14 Replies
View Related
May 22, 2012
I have columns of figures with times like 5:52:54 and next to them cells with values that i wish to format based on the time.
that is if the time on the adjacent cell is before 8AM then make the value cell have a red border it does not have to be conditional formatting - Excel 2002 in win7/64
View 9 Replies
View Related
Oct 2, 2009
I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple.
Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col).
I know that you can just write a formula in each cell like...
b1 will be =sum(a1+5)
b2 will be =sum(a2+5)
... but is there a shortcut so that you don't have to write out the actual cell address for each one?
Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like...
b1 will be =sum(left+5)
b2 will be =sum(left+5)
... where "left" represents whatever value is in the cell to the left of that particular cell?
View 9 Replies
View Related
