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

Table Which Shows Time Started, Time Finished And Then A Total For Hours That Day, Then That Week

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

Time Stamp Macro Tell Total Elapsed Time From When I Started To When I Finished

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

Code Not Working As Planned

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

Strike Through Finished Items

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

VBA Getting Started

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

Counting Components In Each Finished Part

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

Trying To Get A Loop Started..

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

Transferring(Transposing) Data To A Finished Sheet

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

Visibility Of Cell A1 On All Worksheets When Macro Finished

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

Getting Started (does Not Open Automatically)

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

Pause Other Timers When Another Is Started?

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

Where Was My Excel Program Started From?

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

End Script On Worksheet That It Was Started In?

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

Out Of Memory Error After Previous VBA Code Successfully Finished

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

Sorting In Spreadsheet Where Lines Are Sequenced By Finished Good And Component Parts

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

Lookup Formula Started Returning Wrong Letters

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

Specific Set Of Count And Sum If Functions Started Returning Error Values?

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

Macro Worked For 2 Years, Suddenly Started Giving Strange Results

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

Refresh All Queries On Workbook Open When Finished Print To PDF Then Close Workbook

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

Copy Everything From Sheet Pointed To And Paste It To Sheet Started From

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

Double Click Row To Return Data To Userform For Edit Then Return Back To Sheet

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

How To Return Address Of Column Or Cell Select Not Just Return Value

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

Formula: If Value Return Is = To Value Of Cell Above Then Find Return Next Value

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

Return Top N List & Return Corresponding Data For N

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

Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing

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

Return Zero When Then Return Is Not True

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

If Row Contains Value Return X

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

Return EFT Instead Of TFR

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

No Below 0 Return

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







Copyrights 2005-15 www.BigResource.com, All rights reserved