Saving Final Date With Logic Statement
Apr 28, 2014
I have a workbook that is titled "Results". It has 2 columns that I need, a 'status' column (B) and a 'finalized date' column (C)
Column B is the 'status' column: Formula: ('[May Database]Summary'!$B$1)
The May Database B1 column is a dropdown with options "In Progress" and "Final" that will be switched manually.
The results workbook pulls it's status from the May Database workbook.
So I need my results workbook to update it's 'finalized date' column (C) when the May database says "Final" but populate and save the day that the May database was manually switched to 'Final'
My question is would this formula work?:
Results workbook status column: C1= IF(B1="Final", Now(), "")
Would this save the date when the May Database was manually switched to 'Final' or will this just update my current day everytime I open this workbook since Now() returns current day? Is there a way to save a date when it was finalized and then keep that date?
View 1 Replies
ADVERTISEMENT
Feb 8, 2007
I'm having a problem with coming up with a suitable excel formula. I have a table that calculates 2 values. Once these values are derived I need them to be compared to a separate table. This is how it works.
Table 1:
Cell B8 gives me total weight: 3187pounds
Cell C9 gives me the center of gravity in inches: 142.02
on sheet2
Table 2: Cell A2-A26 have weight increments every 50 pounds starting at 2200.
Cell B2-B26 have the minimum C.G. limit
Cell C2-C26 have the maximum C.G. limit
My question is how can I write a formula that takes the weight from B8 and compares it to the cells on sheet two (rowA) and finds the closest weight. Then from there finds out if my C.G. from C9 is within limits of my min and max values?
Sounds complex but it's not that bad. I'll upload the file to my website and let people download if they'd like to see it.
View 8 Replies
View Related
Mar 31, 2014
Column A: T/F
Column B: True/False
Here's what I want in a sentence: If one of the cells in grouped row is "T", column B = True, if not then column B = False.
(row groups are separated by a blank row)
Example)
Col A,Col B
TTRUE
FTRUE
[Code] ....
I came up with following formula:
=IF(MATCH(TRUE,INDEX(ISBLANK(A1:A17),0),0)>MATCH("T",A1:A17,0),TRUE,"")
It checks to see if blank cell is found before "T" but it doesn't look rows above.
inincubus.grouprows-1.xlsx
View 3 Replies
View Related
Jan 28, 2010
my IF statement is breaking logic in picking the right strcode value.
View 2 Replies
View Related
Jul 17, 2013
I have a list of data for each year dating back 10 years and some of lists had entries that weren't available so the cell showed up #N/A. I searched and replaced them with 0 but when I graph the data it skews my graph. I was wondering if it was possible to take a logic (IF) statement that says if the cell = 0 then take the average of the next and previous data points so the graph looks smooth. i.e. =IF(G444=0,(G443+G445)/2,G444) This gives me the correct number i want, but i want to apply it to all my lists like a format if possible.
View 1 Replies
View Related
Dec 8, 2008
I am trying to calculate the number of forecasted hours worked per week for each contractor based on their allocation to the project and also based on a 37.5 hour standard week.
I am using a formula which was previously provided to me for another problem but theoretically should share the same logic. The formula works only when a contractor is forecasted to work for a full week. If there is a contract which finishes mid week the formula returns a weird value. Also another problem is that if a contract is starting on the 12th of January, it still forecasts a full week for the week commencing the 7th of January. I have attached a spreadsheet for everyone's reference.
View 4 Replies
View Related
Apr 16, 2009
tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:
View 2 Replies
View Related
Oct 22, 2009
I have the below code to save a file once cretaed from master data, but it keeps failing on me.
View 3 Replies
View Related
Apr 16, 2012
I have a problem with value's from a userform saving in the sheets as date's. In the userform i fill out e.g. 14-4-12
With the code beneath i save them in mij sheet.
Code:
Sheets("Containerlabels Afdrukken").Range("D6") = TextBox2.Value
When i save this in my sheet, it is saved as text. While i want to calculate with this value as a date, i would like to know how i can save this value as a date.
View 3 Replies
View Related
Oct 30, 2007
I want to add the text (a date) in cell A6 to the title of my spreadsheet when I file => save as. I have the file => save as written into my code at the end and it is the last operation of the code, but I want the date that appears in A6 to automatically appear in the title. This date is not todays date. This way the title will always be EP120-the text in cell A6.
i.e. EP120-Aug-18-2007
Here is the code I have so far...
View 9 Replies
View Related
Jul 3, 2006
See the attachment. When the macro runs. The workbook is saved in a folder with a new serial number. That is fine but I need to debugging the code. Since I need to save the WB name with the time and date to be also picked and named when saving. Refer to cell B1.
View 9 Replies
View Related
Aug 29, 2009
Is it possible to use a macro like the one below, and have the second location save with the date / time stamp in the name? This is what I need to accomplish: I have a few folks that need to make changes to a master spreadsheet daily. The spreadsheet is in a Network share that when modified and saved, will save to that network share, but also locally. Can the second location have the date/time stamp added to the name of the file so that I know when the changes were made and have an audit trail ? This will also keep a copy of the last modified file in case the spreadsheet is corrupted. Here is what I have for saving to 2 locations:
View 3 Replies
View Related
Jan 15, 2010
Is there a way to automate the date and time in one cell upon saving? I have seen some posts elsewhere upon this subject, but here is the twist:
this is a shared workbook with each tab allocated for a different person. Each person needs to update their worksheet and have the date and time update upon saving. The date and time would appear in the same cell for each sheet. I am fairly certain that this cannot be done with an Excel formula, so I would appreciate if anybody had any VBA suggestions. (I am just starting to learn VBA
View 9 Replies
View Related
Aug 26, 2006
I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.
View 9 Replies
View Related
Apr 21, 2007
I am trying to code a macro call where in once the file is saved with the current week start date all the other macro's should be disabled. Since am having lot of data and report sheets which needs to be added and deleted in the run. It causes me problem when I try to open it again to view the results. For example I have a sheet named apple and another one applereport. I have made my code to delete apple since it is a data sheet. I want apple report to have all the other macros disabled once its been renamed to week start date for now it is 16 Mon 2007.
View 3 Replies
View Related
Jan 29, 2014
I am currently creating a simple macro to save out files to a specific folder on one of our network drives. Since we will be doing this often I would like to amend the date the the file name saved for sorting/organizational purposes.
Unfortunately I have been running into a few issues, this is what I have tried so far but keep getting a SaveAs error:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation TeamDC Tools" &
"Copy DC Conversion WB_2014 " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=xlNormal
I have also tried:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation Team
DC ToolsCopy DC Conversion WB.2014_" & _
Format(Now(), "YYYYMMDDhhmmss") & ".xlsx", FileFormat:=56
But still no avail. I believe it may have something to do with the file format but I am not sure. I am running Excel 2010.
View 1 Replies
View Related
Jan 4, 2007
How can I have an if statement or other type of formula look at a date and add 26 months and determine if it's 26 months old yet.
example
assuming that November 4, 2005 is one day from being 26 months
as of today That cell next to it would say "Available"
Then the next it would say "Retired" because it hit the 26 month old period.
View 9 Replies
View Related
Jun 14, 2012
I need to cut and move data in a worksheet where the bottom always changes. The header row should stay in worksheet 1 and row 2 down to the bottom needs to be cut and moved to worksheet 2. One time it could be 100 rows and the next time a thousand or more.
View 5 Replies
View Related
Feb 12, 2009
I need to do a final ranking for the employees those who have been already ranked on three different segments. And there will be also employees who are not qualified for the ranking in any one of the three different segments. So in the final ranking need to find the final qualified employees who are ranked in all the segments and we need to rank them, and the employees who are not qualified also has to be mentioned.
View 9 Replies
View Related
Jan 31, 2010
I think the title pretty much says it all... Now I have a file I can't send to anyone to do anything with... I've googled a few things but I've found nothing to work with... It was only a few hours worth of work but this seems ridiculous...
View 3 Replies
View Related
Feb 13, 2014
DATE
PRODUCT
BALANCE
1/2
KEY CHAIN
4
1/2
KEY CHAIN
8
1/2
KEY CHAIN
27
[code]....
How to get final figure each product from sample data above:
the result:
Key chain = 27
Gift = 4
Hamper = 1
View 7 Replies
View Related
Mar 26, 2009
Finalrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Finalrow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
I need my "Do until" loop to run until the activecell in sheet1 =finalrow
I tried "Do until activecell.row=Finalrow", however that didn't work because it ended my macro too soon.
I tried "Do until sheets("Sheet1").Activecell.row=Finalrow
But it won't accept that.
View 9 Replies
View Related
Apr 21, 2011
Column A : Date & time ( 23 march 2011 14:35 )
In column B: I want the formula to look at column A and return three possible asnswers.
If the time is between 06:00 to 13:59 = AM shift
If the time is between 14:00 to 21:59 = PM Shift
If the time is between 22:00 to 06:00 = Night Shift.
Remember there is a dat attached to the time. I do not want to split the date & time.
View 9 Replies
View Related
Jul 9, 2007
I am trying to use a IF statement to extract date data by months.
e.g. : =IF(I13134="A",H13134(Date field) - (date by 3 months)
I hope that makes sense:
So I guess if the field I13134 has the letter "A" in it - Look at field H13134 and subtract that date by 3 months.
View 9 Replies
View Related
Jul 26, 2007
I have several columns as so:
B - Patient Surname
C - First Surname
D - Postcode
E - Date
F - Results of If Statement (Days Difference)
I am looking to comapre b, c, d with teh previous cells to make sure I am looking at the same person hcen the AND statement. If they all match then if subtracts the latest date (column E) from an ealier date for the same person. If the person is different it will return a 0. If the date is the same ti will return a 0.
I have done this so far - =IF(AND(B2=B1,C2=C1, D2=D1),(E2-E1),0)
What I am struggling to do is if the number of days (E2-E1) is 0-3 then for the result to show the text "Less then 3". If the value is 4 - 7 then to have it read "Less then 7", anything over 7 to read "OVer 7".
View 8 Replies
View Related
May 19, 2009
I'm trying to create an IF formula that returns the month as text between a certain date range in a different column.
For example
Between the 25-APR-09 and 23-MAY-09 I want to display "May" 24-MAY-09 and 27-JUNE-09 "June"
And so on so forth, the column with the dates in it is in date format.
View 9 Replies
View Related
Jun 15, 2009
If I have a columns of numbers that vary in length, and can have empty cells between the final value and the formula to pull the number (of varing lengths), what formula can I use to repeat the final value?
For example - here are two columns:
4,800,000
0
46,594
space
space
<formula to pull 46,594>
or
56,000
48,150
36,528..............
View 2 Replies
View Related
Jan 21, 2012
I've modified some code to perform a loop which creates a new worksheet for each item in a pivot table Page By field (EmpName), copying and pasting values for the displayed pivot table with each name selected. Everything is working fine until it gets done with the last item in the list, at which point I get a Run Time Error 1004, "Unable to Get Properties of the Worksheet Class".
I want the MsgBox to pop up at the end, stating how many sheets have been added.
Sub Loop_PivotItems()
'Turn off screen updating
Application.ScreenUpdating = False
'Store the sheet with the Pivot Table
Piv_Sht = ActiveSheet.Name
'Loop through every PivotItem in the PageField (Filter) of the Pivot Table
[Code] ...........
View 2 Replies
View Related
Aug 15, 2012
I am attempting to sum the final two values in a list. Each week, I add a new value to the bottom of the list. I would like the formula to calculate only the final two values, and update each week. For example, in Week 1, I would have the values 4,10,12,6 in Cells A1, B1, C1 and D1. The formula would calculate the sum of C1 and D1 = 18.
In Week 2, I would input the value of 15 into Cell E1. I would want the formula to now calculate the sum of D1 and E1 = 18.
View 9 Replies
View Related
Mar 24, 2007
If coded correctly, there are various Excel functions & tricks for creating a formula to send someone on a wild goose chase through many worksheets and cells before finding the final cell that contains the final value. I was able to use =Cell("address",...) function to cut through the many paths and retrieve the address of the final cell. Is there a VBA trick for doing this to the active cell? I would like to be able to run a small VBA routine that would trace back the fomula of the active cell and return the final cell's worksheet and cell address.
View 9 Replies
View Related
