To Return A Value- Whether It Is PLANNED, STARTED Or FINISHED
Nov 27, 2006
I have 4 columns: X, Y, Z, AA.
X - Start month e.g. 1, 2, 3, etc
Y - Start year e.g. 2006
Z - Finish month e.g. 1, 2, 3, etc
AA - Finish year e.g. 2006
In column AK, I need to return a value, whether it is PLANNED, STARTED or FINISHED.
PLANNED is if start date and completed date are both > than now()
STARTED is if started >now() but completed date is > now()
FINISHED is if completed date is < now()
Issues:
1. Start date is computed using Y as year, and X as month and the day is assumed to be 1. Same princilple for the finished date.
2. Column X, Y, Z, AA can be blank. If month (X and/or Z) is blank but year is not, then date is beginning of the year. If either start year is blank but finish year is not, then status should be based on the finish year and vice versa.
3. If both year (Y and Z) are blank, then status should be "NOT SPECIFIED".
View 9 Replies
ADVERTISEMENT
Mar 30, 2009
i am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.
Start 08:00
End 16:00
Total 8 hrs.
How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?
View 9 Replies
View Related
May 22, 2009
I am not sure that I can do this, but here is what I would like to do. I have a worksheet that I initial when I start a job in on cell and then when I finish in another cell. What I's like is to have a macro running in the back ground that will tell me the total elapsed time from when I started to when I finished.
View 5 Replies
View Related
Apr 14, 2009
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If IsNumeric(Target.Value) Then
'Clear the Invalid entry
Range("L4").ClearContents
Range("L5").ClearContents
If Target.Address = "$K$4" Then
Range("K5").ClearContents
ElseIf Target.Address = "$K$5" Then
Range("K4").ClearContents
End If
Else
Range(Target.Address).ClearContents
Target.Next = "Invalid Input"
End If..........
However for L28, L29, L48, and L49 the invalid input entry is not showing up properly.
View 2 Replies
View Related
Jan 12, 2010
Is there a way I can format a spreadsheet so that as soon as someone highlights a row, that row automatically gets a strikethrough?
View 9 Replies
View Related
Jan 1, 2009
I am trying to copy some data from on sheet(sales) to another sheet(totals) and later will try to copy to all the worksheets in my workbook
I am using the following Sub macro2()
Sheets("sales").Select
Range("c16:c17").Select
Application: CutCopyMode = False
Selection.Copy
Sheets("totals").Select
Range("b2:b3").Select
Active Sheet.Paste
End Sub
the data in cells C16 and c17 is 2000 and 1850. it shows an error: sub or function not defined
View 9 Replies
View Related
Feb 13, 2014
I receive a list of finished part #'s everyday at work. With this list I need to have excel figure how many components are in each finished part. In the table below, COM. 1 goes into Parts A,B and E one time each. COM. 2 goes into Parts B, C and D twice each. COM. 3 goes into each part once. I need a formula that will find the QTY needed for each component multiplied by the QTY to build.
Finished Part #
QTY to build
Component #
QTY needed
Part A
4
COM. 1
PART B
5
COM. 2
Part C
8
COM. 3
Part D
2
Part E
6
View 1 Replies
View Related
Dec 5, 2008
I'm trying to get a loop started to perform tasks and then go to the next line if it has info in it and do the same thing, until it gets to a blank line, and can't seem to get it right. It's been a while since i've written macros, so i'm sure i'm doing something completely wrong.
View 4 Replies
View Related
May 25, 2006
I am trying to find an automatic way to create a finished report format that draws information from a different sheet within the same workbook. The trouble I'm running into is:
The raw data consists of some lines without any data, and some lines with data. The purpose of the final report is to display only those rows with data in them.
I have a sample spreadsheet attached, with the first sheet containing raw data, and the second containing a rough Idea of what I'd like the final infomation to look like. Somehow this seems like it should be really simple, maybe doable with an if statement, but I can't figure out how to get it to just ignore the empty rows without skipping a row in the final doc.
View 2 Replies
View Related
May 8, 2007
How can I loop through all worksheets in a workbook and, not only select cell A1 in each worksheet at the end of a macro, but have the visibility of cell A1, no matter where you last clicked on the worksheets, such as a remote cell like BK244.
I would like the user to see the first row and further most left cell (cell A1) when they click any sheet in the workbook, because my macro causes several rows of data to be written, leaving the viewer to see something like row 244, instead of the top row which is where the titles are.
View 5 Replies
View Related
Jan 8, 2009
Every time I open my excel spreadsheets the "Getting Started" window opens automatically. How do I turn it off so it does not open automatically
View 3 Replies
View Related
Jul 31, 2014
I've a set of tasks in Column A which I would like to measure. At times, Users flip from task A to task B then back to A and so on. I would like to be able to pause a timer on task A if when timer on task B is started and same as well with the other tasks.. When Task A is started again, it should continue the timer if not add the on-going time to the presently running time under that task.
View 2 Replies
View Related
Oct 20, 2008
I start my excel program from a "Shortcut" in "Folder 1".
That shortcut points an Excel program in "Folder 2".
When Excel is started,
Parent.Path returns "Folder 2",
but I really need to know what "Folder 1" was.
It seems that should be available as
when I do "Save File As"
it is pointing to "Folder 1".
Is there a way to find what "Folder 1" really was from the VBA enviornment.
View 9 Replies
View Related
Nov 6, 2008
I have a workbook with about 52 worksheets, I have a script that will unlock the protection from all the worksheets. However the problem I'm having is the script runs and starts and ends at sheet1 I want the script to start and end at the sheet I run the script from lets say sheet 40?
View 4 Replies
View Related
Mar 8, 2014
I experience "out of memory" error after previous vba code is successfully finished. So quick "solution" is to restart Excel.
My current PC configuration:
HP Compaq dx7300 Microtower
Intel(R) Core(TM)2 CPU
6320 @ 1.86GHz
1.58 GHz, 1,99 GB of RAM
View 3 Replies
View Related
Jul 29, 2013
I have a very large inventory spreadsheet (30,000+ lines) where finished goods are listed on one line and the raw materials (parts) that make up that finished good are listed below. After the last raw material for a finished good is listed, the next finished good is on the next line (and so on). First, I need to sort the lines to eliminate certain common raw materials. Next I need to take the lowest raw material inventory level and have that as the default inventory level for the finished good.
View 2 Replies
View Related
Jul 17, 2013
I am using the formula: LOOKUP(A1,{"S","M","L","XL"},{"S","M","L","XL"}) on a cell containing: EF-2147.(S) so that it will return a S [or M, L etc. if it were .(M),.(L)] but it just keeps returning #N/A. I tried putting in "(S)" etc. in the lookup formula but it then started returning the wrong letters.
View 2 Replies
View Related
Feb 20, 2014
I am, and have been using several sum- and count-ifs formulas for a time, but when the new year rolled around, decided to improve some of the sheet.
That all being said, the issue started after this. A specific set of my count and sum if functions started returning error values.
I analysed them, they seemed fine, and then selected them and hit enter. Problem over in that cell, as suddenly the correct value is returned.
But that's just it, I know the formulas are correct and working, as, if I select and hit enter on them individually, they return the right value. However, every time I close the sheet and re-open, all the values are showing error again.
I am perplexed. Did I inadvertently change a setting somewhere when I rebuilt the worksheet that is preventing the formulas from resolving correctly? As, like I said before, the formulas are fine, the data they are resolving, is fine. Just does not seem to calculate when I open the sheet.
View 2 Replies
View Related
Jan 20, 2009
I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.
View 3 Replies
View Related
Jan 23, 2014
I have data that daily needs to be refreshed and printed to pdf.
I figure the simplest way to do this would be to task schedule the workbook to open daily. Then on open it will refresh the data, print it after all data has been refreshed and close the workbook.
I set it up originally without the need to print so I have all the queries set to refresh when opening the file, however when I now try and put the code to print to pdf on the workbook open event it runs before the queries are finished running. (Query notes: queries were created through Microsoft query, and are accessing a MySQL database queries set to refresh when opening the file queries set to enable background refresh).
View 4 Replies
View Related
Jul 26, 2012
I have set up a workbook with sheets named for each month.
I have 70+ separate excel files (one for each month) that each have a single sheet I want to copy into the new workbook's corresponding month sheet.
Each file is named different but the name of the sheet in those files that I need will all be the same.
Is there a way I could sit in the sheet I want it pasted to, run a macro that I can point to the correct file/workbook -- preferably without opening -- and then have it copy the contents of the sheet I want (again, will always have the same sheet name).
I want the sheet copied with formatting and all, an exact copy.
This link seemed useful - if i could control which sheet it copies more easily since for me the source data always has a different file name. [URL] ........
I thought I lost that original message so I rewrote everything - differently - then I saw the restore button so I'm leaving both phrasings.
I would like to know if there is a Macro that could ask me which workbook to copy from, select the sheet in that workbook called CAP (sheet will always have the same name but I need to copy the sheet from 70 different files/workbooks) copy the whole sheet with formatting and all, and then paste it in my active sheet on the new workbook I am in.
I want to take data about a particular partner we have (CAP) out of the monthly summaries for the last ~72 months (which each have their own file/workbook) and put them all in one workbook just for this partner, on each sheet by month still.
I am only interested in automating everything I have to do for 1 month, but in a way that would easily let me do it again for the next month, and the next and the next, 70 times.
But a Macro would still be easier than opening each file, going to the sheet I want, copying everything, going back to my new workbook, pasting everything, closing the extra workbook, then doing that ALL again 70 times.
View 1 Replies
View Related
Jan 30, 2014
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm
View 14 Replies
View Related
Aug 18, 2014
How to return address of the column or cell I selected from Application.Inputbox, not just return the value?
[Code] ....
View 8 Replies
View Related
Feb 17, 2010
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies
View Related
Oct 4, 2009
I am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
View 8 Replies
View Related
Jun 25, 2009
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies
View Related
Jul 3, 2012
I am using the averageIf function and I need the cell to show 0 when then results are not positive. How to resolve my calculation
=AverageIf(a1:a10,">0",a1:a10) displays #DIV/0!
I want it to show 0 only
View 6 Replies
View Related
Jun 8, 2014
I'm working with an ancient greek lexicon, and I'm trying to get all the parsing to line up.
I have a table with
A
B
C
D
E
1
ἀκούω
I hear
present
first person
singular
[Code] ........
I want column F to display "present" if the row contains the word "present", "future" if it contains the word "future" etc
My problem is rather similar to [URL] .........., but I'm trying to search the whole row rather than a single cell.
View 5 Replies
View Related
Apr 16, 2009
{=(--if(isnumber(search("tfr",c:c)),"tfr","")+(--if(isnumber(search("payment",c:c)),"eft","")","")+(--if(isnumber(search("american",c:c)),"amex","")))))}
I am trying to look up column C and if it contains tfr then return tfr or if it contains payment then return EFT etc etc.
I have also tried using this one as I can use more than 7 criterias. Only problem is that return EFT for all ect where does not contain TFR or payment brings back EFT
=IF( SUM(--ISNUMBER(SEARCH({"Tfr","PAYMENT","AnotherTerm","And Another"},C:C ))),"TRF","EFT")
View 12 Replies
View Related
Jan 17, 2010
I need to have a cell value not return - ( of any number) as it effects additional cells calculations the current formula used is =G4/50-SUM(I4:V4) I basically only need it to count down to 0 reguardless of the entries in l4:v4
View 2 Replies
View Related