Formula - Find First Entry, Second Entry
May 28, 2008
say sheet 1 has 2 collums A & B
collum A is Names Collum B is Dates
A B
Bob Fenton 05/04/08
Rob Smith 05/06/08
Al Feth 05/08/08
Al Feth 05/18/08
Al Thomas 04/23/08
Rob Smith 05/23/08
Bob Smith 04/22/08
Bob Fenton 05/15/08
Al Feth 05/10/08
sheet 2 has unlimited collums in collum A is the name of the person in collum B to Z (or more) i would like a fomula that will search sheet 1 and return the dates for each entry of that name.
so sheet 2 would be like ....
View 9 Replies
ADVERTISEMENT
Mar 14, 2014
Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.
View 3 Replies
View Related
Mar 20, 2014
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
View 4 Replies
View Related
Feb 26, 2012
I'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.
columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.
Eg.
A = section
B = description
C = size
D = region
E = cases ordered
Row 2 = fruit, apples, medium, north, 25
Row 45 = fruit, apples, medium, north,
View 3 Replies
View Related
Sep 11, 2013
I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.
I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.
View 3 Replies
View Related
May 1, 2009
I used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?
View 4 Replies
View Related
May 26, 2009
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
View 6 Replies
View Related
Jan 14, 2009
I have a calendar where the user inputs the day Jan 1st starts on. This will then input the date on the following days. I need a formula to find when the 31st of Jan is and input a 1 for the 1st of Feb against the relevant day it lands on. See file attached.
View 5 Replies
View Related
Apr 17, 2006
I am having a problem where imported data from access will not be refreshing in a cell. It will not perform a calculation using the imported data. What the code does is import the data into a cell, then if the label of the row is not empty, perform a calculation. The weird thing is, if I open the VBA editor and go through my code line by line hitting F8, it works as I would expect. It is only when I run the macro either as a button or with the control toolbar that it will not work properly. I am not very experienced with VBA
Sub DrivesByAutomationByDonorGrp()
Worksheets("Mkt Penetration_DonorGrp").Range("C4:F2005").Clear
Worksheets("Mkt Penetration_DonorGrp").Columns("F").NumberFormat = "0.00%"
Worksheets("Mkt Penetration_DonorGrp").Columns.Hidden = False
'Columns("C:E").Select
'Selection.EntireColumn.Hidden = True
Dim qt As QueryTable
sqlstring = "select [Drives Without Automation], [Drives With Automation]" _
& " from qryDrivesByAutomationByDonorGrp"..................
View 4 Replies
View Related
Sep 28, 2006
Example. I have a range of information on sheet 2 in cells A1:A25 that contains values that are text. Another range in B1:B25 that has numerical values. I would like to link the last value entered from those ranges to cell A1 & B1 on Sheet 1. Is there a formula option to do this?
View 5 Replies
View Related
Aug 10, 2009
i need to identify the first and last duplicat entrys and delete all rows in between.
eg keep row 275 and 277 and delete row 276 all duplicates only in column C ..
View 9 Replies
View Related
Aug 1, 2014
I've got a spreadsheet with various amounts in cells A3:L5. I want to find the last non-blank entry in each row but only where the entry is in a column headed "Guaranteed PRB" (found in row 1). Then I want it to return the column header title found in row 2, which is a date.
I've attached a sample of the spreadsheet with the expected answer in column M.
I've got as far as formula:
=LOOKUP(2,1/(a3:l3<>""),$a$2:$l$2)
how to also make it look at row 1's headers too.
View 5 Replies
View Related
Oct 16, 2009
I m trying it out I find in the Win, Loss, Draw columns when there is no entry the formula leaves a 1 when there should be a 0 in the Draw column.
View 4 Replies
View Related
Jan 12, 2013
A stab in the dark here, but is there any way to be able to 'jump' to a particular position within a filtered list. My list has around 20,000 items in it and I'm having to go through each one systematically to make edits on the relevant line and it is so laborious trying to find the correct place in the list every time.
Is there anyway to be able to may be key in the first letter or two letters to avoid all the continuous scrolling to find the relevant entry?
View 4 Replies
View Related
Oct 22, 2007
i had this formula at one time but lost it.
i am trying to figure out how to evaluate a column and get the cell address of the cell with the last entry
example
--A
1 e
2
3
4 g
5
6 h
all other cells under A6 are blank
the answer is A6
View 9 Replies
View Related
Jul 14, 2006
Dim lCount As Long
Dim rFoundCell As Range
Dim sfind As String
Dim cl As Range
Set rFoundCell = Range("A1")
For lCount = 1 To WorksheetFunction. CountIf(Columns(1), TextBox1.Value)
Set rFoundCell = Columns(1). Find(What:=TextBox1.Value, After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
With rFoundCell
End With
Next lCount.........................
this macro looks up in column A (a list of names) the name entered in textbox1 (of userform2). if found - the value is "copied" to E13. (if not - showes message - " does not exist") for example: it found the value "Dainna" (which was typied in textbox1) and copied it to E13. i want the macro to keep on to the next cell (in the row where Dainna's in- B3) and copy the age to F14.
View 3 Replies
View Related
Jul 28, 2006
I want to find the Row Number, not necessarily the cell value, of the last non-blank entry in a Column.
If the address of this row is found, then that could also be useful. I believe there are some simple Excel functions to do this, maybe involving the X1Up feature. I've searched the threads and haven't found a clear answer this.
Below is code that I custom wrote, but its long and tiresome to use.
'The purpose of this sub is to find the last filled row in a column
'Knowing this last row is useful for telling later
' looping operations which row to stop on.
'The logic of this sub is that it will look down a column.........
View 9 Replies
View Related
Aug 14, 2008
Working on athletics worksheet. Have worked out a formula to attribute a PB (Personal Best) in a MIN range not including the first cell entry (as first entry is not a PB) in events where less =PB
=IF(ISBLANK(F6)," ",IF(F6(MAX(V$5:V6)),"PB"," ")) keeps recognising first entry as PB. Any help greatly appreciated
first entry could be anywhere in the column but all entries after will be below it.
View 9 Replies
View Related
Jan 6, 2010
I'm creating a spreadsheet where a test note taker needs to repeatedly enter the date/time code in a column as events occur.
I tried recording a macro that will enter the =now() formula and then copy that over itself with the value but didn't work.
So now I'm thinking I need to just write code that will determine "now", and write that serial value to the next open cell (with care not to overwrite the previous data point). I'd like to execute this with a simple keystroke.
The thought is to start at the top of the column, go down until the next blank cell is reached, insert the time code (can't be a formula that will change but an actual value).
View 9 Replies
View Related
Jul 7, 2009
i have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
View 9 Replies
View Related
Jul 6, 2006
Is there a way you can copy formulas to the next line when a new entry is automatically added to a spreadsheet via a form?
View 1 Replies
View Related
Jan 16, 2014
how can I prevent an entry in a cell even without formula lets say cells B1 , C1 , D 1 , E1
I want to prevent an entry in these cell and if you try an error message will display
View 1 Replies
View Related
Oct 20, 2008
I have a list of account balances in one column. How can I pick up only the bottom number in that column? I have 20 worksheets and want a total across all worksheets of the last number listed in a particular column but each worksheet is different because they contain a different number of rows with activity. So, if in col. C I have:
$482
$1,000
$899
$456
$231
... then I want $231 to be what is captured for that worksheet, but if the 2nd worksheet contains:
$500
$1000
$344
... then I want $344 to be what is captured for worksheet 2. Does that make sense. In the end I want to sum $231, $344.... etc.
View 3 Replies
View Related
Jul 21, 2009
I have a database of 6 digit numbers in one column(let's say column A). I would like to put in a 6 digit number in a cell (b1)and have another cell (c1) give me a response as to whether that number exists in the database (Exists or Doesn't Exist).
View 2 Replies
View Related
Mar 17, 2014
I have a cell with a formula in it. I want to use conditional formatting on this cell if the formula result leaves it blank. Given the formula is in the cell it's never recognised as blank. If an entry is input instead the formula then obviously I don't want the conditional formatting.
What formula can I use in the conditional formatting for it to recognise the Cell formula as blank?
View 6 Replies
View Related
Feb 11, 2012
I wonder if there is a way to enter my formulas as an array formula using Control-Shift-Enter in a more time saving way than one line by one line
I have 600 rows of array formulas all in column E but at different intervals with some blank rows inbetween
The formulas are in place but just need the CSE to enable the array formula { }
I am not looking forward to line by line entry.
View 1 Replies
View Related
Nov 21, 2013
I have a cell that has a result which is the average of a few other cells (In this case the result is in cell Y7). I now want to take that total and put it in another cell to represent todays result..I.E. I have a list of this months dates and I want the todays result to be populated in the table. So I have a list of dates and the cell next to each date has the formula:
=IF(TODAY()=A24,$Y$7,"").
It populates todays result fine however the next day it blanks the result (since the cell is no longer todays date).
SO the question: How to I have todays result oerwite the formula so the result is permanent in that cell. I guess I am looking for some sort of PUT command to take the result and PUT it in another cell without that cell being a formula itself to GET the date.
View 3 Replies
View Related
Apr 25, 2007
I have a problem to solve
I have 2 worksheets named 'Products' and 'Order'
At the 'Order' i use 3 cells
Code
Pallets
Cases
When i entered a code to the cell 'code', it reads the code from the sheet 'Products' and then transfers to the active sheet 'Order' some items.
What i want is:
Because the user has to enter the quantity either to the cell 'Pallets' or
'Cases' i want to be locked the empty cell each time and to be used for calculations.
1st Eg.
The "45870"
Pallets to order : 50
because the user used the pallets the cell "Cases" must be locked and it must calculate the 50 pallets*100 so
Cases : 5000
2nd Eg.
The "45870"
Pallets to order : null
Cases : 5000
because the user didnt use any pallets the cell "Pallets" must be locked and it must calculate the 5000 cases/100 so
Pallets to order : 50
View 9 Replies
View Related
Feb 11, 2004
I'm trying to set a cell so that it has a certain value based on other cell(s) information. I can put a formula into the cell to do that, but I also want to make the cell available for user input, preserving the formula should the user change their mind and delete their input.
Basically, it's a cell with a protected formula that can be overridden with user input, then if the user decides to take their input back out, the formula kicks back in.
View 9 Replies
View Related
Feb 12, 2007
I've found several threads describing how to make a chart title dynamic by entering a cell reference into the formula bar with the chart title selected. I am wondering if anyone knows if it is possible to have VBA "code" a formula into a chart title that would be a concatenation of several strings. Ideally, I'd like to specify part of the title from a variable, and another part from a user-specified range, as in the following snippet from a module of mine:
ChartSubtitleText = Sheets("Sheet1").Range("A5")
ActiveChart.ChartTitle.Select
Selection.Characters.Text = ChartTitleText & Chr(10) & ChartSubtitleText
where ChartTitleText is a string variable that is passed to the Sub. This works fine and correctly builds the title block during execution of the macro, but I'd like to modify the capability to allow the user to enter a new value in Sheet1!A5 and have all of the charts update dynamically without re-executing the macro. Is there a way to have VBA create the chart and build the Chart Title with a formula that will allow for dynamic updating? I've tried having it enter the following line into the formula bar, but get errors: =CONCATENATE(ChartTitleText, CHAR(10), Sheet1!A5)
View 5 Replies
View Related
