I have a table comprised of 52 weeks (7 days each week) totaling 365 days. Here's what one week only
looks like
Week 2
Sun.
Mon.
Tue.
Weds.
[Code]....
I keep the prior year in a separate sheet in this same workbook. I need a formula or function or something to search the prior years table and pull the last years sales and last years customer count from the prior years sheet based upon the date.
I need to create a table of dates which is structured as such:
A B
1/7/08 Yes
7/7/08 no
25/7/08 yes
Column A is date, Column B is a binary selection (yes, no)
I then have a separate sheet with rows corresponding to each date in July 08. I need a function which will look up the date for each row with the above date range and return the value of column B.
for example - if the date is between 1/7/08 & 6/7/08, it would return Yes. If the date is between 7/7 & 25/7/08 it would return No.
The above table is not fixed and can have many dates, with no pattern for the dates. I though of using a nexted IF statement but it would become a bit clunky.
I am attempting to do a search on a 2 dimensional table, based on Product Code/Month. Is there a function, addin or a macro that will allow me to do this? All I can come up with is a messy combination of VLOOKUP/HLOOKUP/COLUMN.
View 2 Replies View RelatedI have several hundred rows of date/times which I need to modify just the date (for example 08/23/2007 11:00 to 08/15/2008 11:00). Each row may have a different time so it is just the date I'm targeting for change.
If I manually use the replace dialog, replacing 08/23/2007 with 08/15/2008 works just fine. However, if in code I attempt to use the Cells.Replace function, it does not locate any data to change.
I have found that if I search for the string 8/23/2007, the dates are located and changed. Only when I attempt to find the fully formatted date 08/23/2007 does the function fail.
I have data going in to a small table which has some empty rows as that data is not yet available... My problem is, I need to sort this table in date order but with the date nearest to today's date at the top...
The sort function puts oldest at the top or oldest at the bottom which is no good for what I need...
I use xl 2003.
I have a simple(?) problem but for some reason I can't find solution for it...
I just need to find if a value exists in a table.
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
View 2 Replies View Related0000AAAAABBBBBCCCCCDDDDD
1111SugarRaisinDirt
2222MilkWaterWaterFlour
3333FlourSugarDirt
4444EggsWaterFlourSugar
5555WaterMilkDirt
I looked for a way to represent my data in a cleaner way than this but couldn't find any tools....a point in the right direction would be helpful. I guess the old plug-in that converted Excel->HTML isn't available?
Column A is a finished product and B-D are the ingredients to make it however values in Column A are also ingredients (sometimes multiple times). I want a formula that searches for values in Column A within the table B1:D5 and returns an array, if possible, of the finished products where Column A was used.
For example, searching for Sugar would return "Flour, Water". Searching for Water would return "Milk, Eggs" (I'd rather not have Milk listed twice but beggars can't be choosers).
I have a table which details an applicable fee given a level of performance. However the levels of performance can be any value and the fee level table which i need to look up gives conditional ranges such as if (out performance is) >4.5% and <5.00% then applicable fee will be 0.7%. However my table is large enough that using a nested IF formula seems unfeasable. I've attached a sample of what i mean which explains it better.
View 2 Replies View RelatedI am having a littler trouble with using a function to find text (last name) in a table and then return full name from the table. See attached sheet with example of what I am looking for.
A quick explanation:
Have a table with columns that have team and name of player. My input is the last name of the player and the team. Need a fomula that searches all the table and then returns the full name of player based on 2 conditions of last name and team.
I have been working on a variation of this for a long time and I cannot get it to work.
I am trying to depending on the textbox selection get the proper ROW from DEALLIST to show up.
Basically my search value is in B4 however to refresh pivot table
this is fine when I enter plain text within B4
I have trouble with an vba code using pivot tables
Private Sub Worksheet_Change(ByVal Target As Range)
'set handler for unexpected issues
On Error GoTo Fatality
'exit unless cell altered is that pertaining to the PT Page Field
If Target.Address(0, 0) "B4" Then Exit Sub
'validate selection
Select Case IsError(Application.Match(Target.Value, Sheets("DATA").Columns(2), 0))
Case True
'invalid selection
MsgBox Target.Value & " Invalid Store Number - PT Not Refreshed & Selection Reset", vbCritical, "Error"
Application.EnableEvents = False
Application.Undo...................................
In a large spreadsheet that receives external data, I have codes (U, N or V) allocated at various times to different rows as shown in sample worksheet attached. Elsewhere in the spreadsheet, I need to display (for later export) these codes along with their respective number in a sorted list. Please review the attached:
In the spreadsheet you'll see a column of codes, the next column is the data reference number, then to the right is three columns, one for each code. As displayed in the sample book attached, each Code column is to display the data reference number (from column 2)that matches that column's code.
What formula can I use to list these numbers in the appropriate columns?
In column A, I have dates; In column b i have security levels. I have made a table called "Security" it contains to columns, a list of security levels and no of years when each security level is required to be reviewed. the table is setup -
d1 e1
Restricted 5
etc
Example of data
ie.
a1 b1 c1
Restricted 1/06/2012 1/06/2017
What I am looking for is a formula to look up a1 "restricted". then lookup the security table and find "restricted" its value is 5 (years) then add the 5 years to date in b1, but place it in c1.
i have a large table and i need separate x and y coordinates(to plot on a graph) of a particular repeated value that is known to me in a large table of numbers. how can i do this?
View 4 Replies View RelatedI'm looking for a formula to search for a value in the SECOND (instead of first) column of a table array and return a value in the same row from FIRST (instead of the same or another) column in the table array. Formula would be searching for the unique production order number in the column B and return production line id from the column A.
A1 production line_id
B1 poduction_order_number
A2 L1
A3 L2
A4 L1
B2 505212
B3 504234
B4 505663
I was trying vlookup(504234;B2:A4;2;0) to make formula go search from right columns to the left but then excel is switching the search table to A2:B4 and gives #N/D!
One remark-there is no possibility to switch these 2 columns to simplify. I have to leave them as they are.
I have a complex list of text (a chart of accounts) with various roll ups for subtotals on one worksheet, and monthly download on another worksheet showing the name of the account and the value. I wish to use match the text to return the value in the front sheet and avoid taking the simple route of linking the cells in case additions are added during the year.
I've tried using IF and VLOOKUP formulas without success - i guess becasue I cannot sort the table in descending order?
What would be the best sort of formula to search using the text, and return a value on the same row?
I have my data arranged in a table on sheet1 as shown below.
Columns B to H contain the hours each person normally works on the days of the week Sun to Sat. In columns I to O are the hours that each person has booked as holiday for that week.
On sheet2 I would like to be able to enter a holiday day in C1. In A3 to A15 I would then have a list of all the people who will be working on that day and in C3 to C15 have a list of the people who are on holiday so the output on sheet 2 would look like this.
Basically I'm after the formulas for A3 and C3 that can be copied down the column depending on how big my table gets. I am using Excel 2010.
I have a couple of columns, once edited need to update a table in SQL. This is my code
[Code] .....
However it is not taking UK date formats, it is assuming it is an american format, I get conversion of varchar data type to datetime results in an out-of-range value. What should I add to change the format? The format is in UK on the spreadsheet seemingly....
I have an 'existing results table' as per my attached sample.
I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.
I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.
Please can someone show me the formula on my attached sample?
Please see my example in red.
I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
I have a spread sheet with Column A = Document number, Column B = revision.
I am trying to get a macro that, when run, increases the revision letter in column B. The problem is that we do not use a few of the letters such as I, O, P and Q.
I thought it would be best to have a table of the used revision letters (Say in Z1 to Z40)
Look at the text in column B of the row currently selected and find it in the Z1:Z40 table
Move one cell down the Z1:Z40 table and copy that text back into column B on the row initially selected.
I'm using a code to search some information in a pivot table and copy the value in the cells with offset(0,1) and is very very slow, I tryed another method with the using of Find but isn't working: error message: missing object in the with cycle.
Here is the working code:
Dim DataFine, DataInizio, UltimaRiga, Gg As Date
Dim NomeMacchina, Plant As String
Dim Cl As Object
Dim Pr As String
Application.ScreenUpdating = False
[Code] .......
Here is the code who doesn't work
For i = 6 To 500
Giorno = Sheets("OEE03").Cells(i, 2)
With Sheets("01")
Pr = Range("A5:A500").Find(Giorno).Offset(0, 1).Value
Sheets("OEE03").Cells(i, 9).Value = Pr
End With
Next
if I use this code on a normal sheet it works but when I try to use it on a Pivot table fields give me always the error message.
I am using excel 2007. I have a worksheet with a list of words I wish to step through this list replacing the selected words in another workbook with nothing (ie deleting them).
I have the following code
Code:
Sub replacewords()
Dim MyWord As String
Dim wbLibrary As Workbook
Dim wbWorking As Workbook
Dim myExcelColumns As Integer
Dim myExcelRows As Integer
Dim MyRow As Integer
Dim MyRange As Range
[code].....
It does not seem to evaluate "myRange";
I have sheet 1 that in cells V5:V20 is a data validation drop down box. In cells W5:W20 I have another data validation drop down box. On sheet 2 I have a table that in column A matches the data in the drop down box's in column V on sheet 1. Row 1 on sheet 2 data matches the data in the drop down boxes in sheet 1 cells W5:W20. What I'd like to do is on sheet 1 Cells Y5:Y20 have a VBA code to lookup the data in columns V and W from sheet 2 and return the value.
Windows 7
Excel 2010
I have this sheet for a search of data with two conditions (K4 and L4) to give me the minimum value in E column. Its working great but i need something more. Sometimes the minimum value in E column (Cons.) is the value of the date in J4, and i need some condition, if possible, in the formula on I7 that gives me the same result that it gives me now but without considering the data of the date in J4.
View 3 Replies View RelatedI have a spreadsheet with dates filled in all the columns, in the first row. How can I use VBA to bring up a Pop up Calendar so that when I click on a date, it will take me to the cell with that date?
View 9 Replies View RelatedI need to find month / year and count the number of times it occurs..
This is what I have so far..
=SUMPRODUCT(--(main!D:D="name"),--(main!M:M="dormant"),-- (ISNUMBER(SEARCH("??????????",main!h:h))))
the problem is that the date column i'm searching had the following format: 28/1/2009 11:37:00
what do i need to replace the ????????? with to to make it look for the month and year only from the format above.
inside cell a30 i have a date function which displays: Mon 07/22/2013 3:30:14 AM. i realize that excel actually uses 41477.146 Can i search for something kin the display, i.e. "Mon"?
View 2 Replies View RelatedI have this horizontal list of dates.
4/1/2014 5/1/2014 6/1/2014
A C E
B D F
I am trying to search for a certain date and get its row and column number. I am having trouble finding the dates due to its date formats.
4/1/14 4/01/2014 etc
I am using this code to search Set Foundcell = Range("1:1").Find(What:=(Sheets("Data").Range("AF1").Value)).row
When debugging the value of the Foundcell I am getting NOTHING as a result even if the the value at AF1 is 4/1/14
However if I find using the .Find(What:="4/1/2014").row i am getting the row number
I also tried application.match function. foundcell= Application.Match(Sheets("Data entry").Range("AF1").Value, Sheets("Data entry").Range("1:1"), 0) result is NOTHING
Not sure where to start with code to do the following.
I have a Large workbook 150 worksheets in which i need to remove rows based on date criteria.
I need to remove all rows which contain 2011 in cloumn D within the range of B10:T40 without filtering the work sheet first. on every worksheet in the workbook. also I would need the code to skip/ignore sheets that do not have a 2011 date incolumn D.
Time is not a issue for this procedure.