I would like for the result of a formula to stay in the cell even after the first cell changes.
in cell G1 I use this formula, if(C1=D10,A1,""), I would like result of formaula to remain in G1 even after C1 is changed.
I need to look up the name in E2 in the list in column A and if it matches then lookup name in F2 in list column b, if it matches then the corresponding number in column C is displayed in column G. If neither names are in the 2 columns the words"Not on lists" is displayed in cell in column G.
The other problem is one name is spelled two different ways I want it to look for both spelling before moving on to looking up the second name.
I started with this formula but I'm getting #N/A or "not on list" when they are on the list. I'm using ranges prod_sum is columns AthruC, Last_name is range BthruC. =IF(AND(VLOOKUP(F4,prod_sum,3,0),VLOOKUP(I4,last_name,2,0)),"not on list")
What I'm having a hard time is trying to get information from column C to pull into a cell, for a specific student (column A), but only when the entry happens between two set of dates (column B).
Column A = Student Name
Column B = Entry Date
Column C = Entry
So in a different worksheet, the names of the students will already be on the report and each column following will have a date range of a week. So for column B I want the entry that only happened between "09/07/09-"09/13/09". No student will have more than one entry a week so I just want it to locate the entry and fill in automatically. This will repeat for each week after as well.
I am trying to match info from one column with another column and if it matches, I need the formula to populate the relevant data from the adjacent column. I using Vlookup and I am not sure why it does not work. I have attached a simple example.
View 2 Replies View RelatedI am using the =TODAY() command in cell C21.
If i reload the saved file a few days later the date obviously defaults to the date opened not the previous one.
Is there any code or a macro that i could use to ensure this date stays the same when it's first saved?
Is it possible to use a date formula eg Today() to enter that days date when a blank cell say a1 gets data entered, then not change date the following day?
View 9 Replies View RelatedI want the range to stay as i put it without it +1 when i drag the formula down my spreadsheet, below is the formula im using (for a creche in case your wondering)
=IF(G5<A8,"Baby",IF(G5<A16,"Wobbler", IF(G5<A24,"Toddler",IF(G5<A30,"Playschool","Please use DD/MM/YYYY FORMAT"))))
G5, A16, is taken from a list of dates down the side so i need these to remain constant otherwise my formula goes out.
I need a formula that will take the value of one cell and put it in another cell other than the one in which I am typing the formula. I can't think of how to do this.
I have two columns, A & B (no headers), of information. The columns are not identical, and column B has empty cells scattered throughout the column. For each empty cell in column B, I want to copy the same row of information into it from column A, as if I'm just scooting the information in column A that's on the same row as the empty cells in column B.
This is simple with a cut/paste, but when there's thirty empty cells, cutting and pasting takes time. (What's really frustrating is that I've figured out a few formulas that will copy the information from column A but all the information gets pasted in the column in which I'm creating the formula (say, column F, for example). THAT doesn't help me because the empty cells in column b are still empty!)
In column A I have Product Codes, They start at A3 and go down to A169.
I need to have a formula which i can drag across from CQ3 to DH3 which corresponds in the following manner.
CQ3 = A4
CR3 = A5
CS3 = A6
etc etc.
I then want to be able to copy the formula into another place, eg CQ20 = A21, CR20 = A22 etc
I have attached a screen shot.
Untitled-1000.jpg
I need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.
If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)
Capture.JPG
I have used index/match before to match and extract data, but I can't seem to wrap my head around this one. It's probably best explained by looking at the attached workbook. Basically, I want formulas in 'John Doe!B' and 'Jane Doe!B' to look up the Name and Date values in column A on the 'Data' sheet, and then return the time value that is on the same row but in the 'Data!B' column.
I then want 'John Doe!C' and 'Jane Doe!C' to return the time that is two rows below the time shown in 'John Doe!B' - I've gone and manually entered and bolded the values in the workbook to show what I eventually want the formula to do. The data on the 'Data' tab will be extracted monthly into excel from tracking software my company uses, but there will always be a minimum of 3 rows of time for each date. As you can see in the example workbook, some dates will be omitted for some people, and while there will always be 3 minimum rows there will often be many more time entry rows for different dates as well. However, I'm only concerned with the 1st row and 3rd row for each date.
I was originally thinking a vlookup for 'John Doe!B' and then a hlookup for 'John Doe!C' that references the value in B might work, but I'm getting stuck on how to ensure the formula realizes the differences between the data and dates for John Doe and Jane Doe.
I am trying to use some vba match function code to return the column number of the matching date. The date will be stored in a date variable.
Every time I run this code I normally get a match error even though the date is in the worksheet and the variable matches that date.
See below:
[Code] .....
I have a column of cells (Column I2:I1063) with zip codes in it and I want to keep the row if the zip code matches one on the list in a column B2:B100 on a separate sheet (Sheet1).
View 4 Replies View RelatedA B C D E F
Latest Status
Latest Date
Current Status 1
Current Date 1
Current Status 2
Current Date 2
#VALUE!
Sun,16-Feb-14,11:0 PM
Take1
Sun,16-Feb- 14,10:41PM
Take2
Sun,16-Feb- 14,11:02 PM
My formula =IF(B3="","",OFFSET(A3,MATCH(B3,B3:F3,0),0,2)) in A2 is giving me #value error.
The correct solution should be Take2, while matching the condition from B2 latest date.
Sheet Attached. File Name-Latest.xls Zippyshare.com - Latest.xls
is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.
View 7 Replies View RelatedI have one worksheet which comes out of an online application (for the purposes of this example I will call it "Online Sheet"). The column headings in this sheets are always named the same but could theoretically appear in any column address. For example, the column header "Completed" could appear in column "X", or "AT", or "ZZ".
On a second sheet (called "Code Sheet") I have to unscramble all of this data into a standardize layout. Each row value has a unique ID which appears on both the "Code Sheet" and the "Online Sheet". However, in the "online sheet" the unique ID could also appear in any column. Like the "Completed" column this column also has a unique column heading ("Unique ID").
Using Match I can calculate the column number in which the "Completed" column appears in this instance of the online data. For example, "Completed" = Column "25". However, I now need to use some lookup function on this column based on the row in which my "Unique ID" appears of the "Online Sheet" (which could of course be in any column in the "Online Sheet").
For example, if "Completed" is in column "25" and the "Unique ID" which I am referencing on the "Code Sheet" appears on row 14 in the "Online Sheet", then the lookup formula must return the value of column 25 row 14 on my "Code Sheet".
I copied this code from the Microsoft site:
Sub Send_Range()
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:B5").Select
' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True
It works...sometimes. It opens the mail address/subject bar at the top of the page, and it gives me the typical warning that Outlook is being asked to send an email from an external source. I click "Allow" and it sends. Well, it sends sometimes. Other times, when I don't receive the email after a few minutes, I open Outlook and there it is sitting in the Outbox. I have to hit send in order to make it actually send out.
Is there something that I am not noticing that I am doing incorrectly? I would think that if it doesn't work, its the code, but without changing any of the code, it'll end up working another time that I run it, so I would think the code is correct.
Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?
Below is an exctract from a much larger sheet of the columns in question.
The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.
Date Decision agreed
Disposal Order
Latest Decision date for D.O.
06/05/2014
D.O.001
[Code] ........
I am trying to move a column of numbers based on the information in another column. I've been looking for about a week and find macros that are close but not quite.
In one column it reads Mobile, Home, or is blank. If the number is a Mobile (column R), the area code (column P) needs to move to column S and the phone (column Q) needs to move to column T and the primary phone (column R) needs to move to column U. Home and blank cells remain as they are.
area (P)
phone (Q)
primary phone (R)
col S
col T
col U
[code]....
I have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A.
Here is what I have but it is counting the dates in col A not the order numbers in B?
I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.
View 3 Replies View RelatedI'm using an old CP code to display a list of all worksheets when a button (from control toolbox, not forms) is clicked:
Sub SheetList_CP()
'Chip Pearson, 2002-10-29, misc., %23ByZYZ3fCHA.1308%40tkmsftngp11
'Dave Peterson, same date/thread, 3DBF0BA8.4DAE9DA0%40msn.com
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
If Err.Number > 0 Then
Err.Clear
Application.CommandBars("Workbook Tabs").ShowPopup
End If
On Error GoTo 0
End Sub
If I click the button and decide I don't want to go to another worksheet and click a cell to get out, the button stays depressed. Any ideas as to why?
table1.jpg
I am looking for a formula that will satisfy the following:
1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16
I am attempting to Organize some data; however, I have run into a problem. I will paste a sample of the data that I have and explain the situation....
View 9 Replies View RelatedI have been setting up some pivot tables. Down the left side of the pivot table is the values from a column labelled TRACKS. There are about eight different TRACKS. Say Track1, Track2, Track3, etc. I have set the data range up as a dynamic named range so that if more rows or columns are added to the data then the pivot table will automatically incorporate these.
Today I wanted to test that the pivot was working as expected. One of the things I did was stick a few extra values into the TRACKS column on the data source. These were TEST1 and TEST2. These appeared in the TRACKS down the left side of the pivot table as expected.
When I deleted these two test values, however, they would not disappear from the pivot table. There seemed to be nothing I could do to make them go away, except to rebuild the pivot table from scratch.
I need for coloring "Expected Delivery Date" column corresponding with "Control Date" and "Control Result. But I want to correspond with the cells' last content of "Control Date" and "Control Result" But first of all I want "Control Date" should be flashed 10 days ago of it's date... (it's already done on my excel file)
Then
I want "Expected Delivery Date" as green when
- "Expected Delivery Date" =< "Control Date" and "Control Result" =Y (Means Control is Ok on due time)
I want "Expected Delivery Date" as red when
- "Expected Delivery Date" > "Control Date" and "Control Result" =Y (Means control is ok not on due time)
- "Expected Delivery Date" > "Control Date" and "Control Result" =N (Means control ok is not given, delayed...)
I'm copying and pasting data from a PDF and need to eliminate some unnecessary data. The original .pdf has 4 columns: Account, Dollar Amount, Name, Notes
When I copy this to an excel document, it copies the row from all 4 columns into column A. The two columns I need are Account and Dollar Amount. All account numbers are 8 digits so I was able to create a formula to weed that out with this: =left(A1,8)
The problem I'm running into is obtaining the dollar amount within the cell. For example:
A1 contains: 11112222 $1234.56 Sample, Name Sample Note
I pull the 11112222 with =left(A1,8) in column B but not sure how to pull dollar amount to column C.
Further, the dollar amount varies from $1.01 to $10,000+
I need to sort information in a column containing both numbers and words. In the "asending" & "desending" it only gives two options to choose from. (none) & PartNum.
View 14 Replies View RelatedI have a sheet that users fill out and there is a macro on that sheet to copy the info to another workbook, save and close.
for some reason, the date, which originates from a cell with the value "=Today()", and when pasted in to the new book, it is pasted values.
the issue is, every so often, the date will say 09/26/10 and continue to do so for quite a few entries.
i have tried almost everything. I checked the dates on the computers, and all are fine. i even went through it step by step, and when the data pasted it, it was 09/26/10.
I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View Related