I'm trying to create a dynamic data table which has dates (in reverse chronological order) going down column A.
I want to find the value for a commodity (say bananas) which is housed in a master data table, based on the date that's in column A.
I have a list of two rows of over 20,000 items. I would like to know what data has been added to the second row from the first row and what has been removed.
View 4 Replies View RelatedI am trying to track cost. A master purchase order sheet has a column with a number relative to an account. I have about 50 accounts and a seperate sheet for each account. I want to enter the account number on the master purchase order sheet and based on that number, have the following data on that row copied to the next available row in the sheet for that account.
View 9 Replies View RelatedI have a list of about 1000 names with 2 columns next to it. The first contains a list of different groups that the names belong to (there are 19 possible groups). The second contains a list of figures ranging between £10,000 and £500,000. I want a formula that will look for a name in the list and, when it finds the name, return the corresponding group and corresponding amount. My problem is that some names are in more than one group and therefore appear in column 1 more than once. I have tried to use the LOOKUP formula [eg looking for the GROUP =LOOKUP(Sheet1!B33, Sheet2!A:A, Sheet2!B:B)] but when a name appears more than once I cant get all the results - for example:
Mr Phil LittleGreen Group35,000
Mr Ed GrantRed Group20,000
Mr Robert SmithOrange Group10,000
Mr Robert SmithRed Group50,000
Mr Robert SmithGreen Group75,000
Mr James MorrisonGreen Group100,000
On my summary page (Sheet1) I would like to type in Mr Robert Smith (into cell Sheet1!B33) and in the rows below (obviously in 9 different cells) have the results appear but I can only ever get Mr Robert SmithGreen Group75,000 as a result. This is my first post so I hope I have obeyed all the rules and this is clear
On Pipe worksheet, pipe numbers 1-203 are listed in column A. Each pipe number has a flow rate that is listed in column E. I need it to look up the pipe number in column A on the Job worksheet and report the flow rate (from the Pipe worksheet) in column D for each pipe.
View 5 Replies View RelatedI was interested in getting Excel to look up details of a person say age and put the details in another table under the correct heading.
E.g. Fred Bloggs age 25
Would look like this
First Name Surname 16-24 25 - 49
Fred Bloggs 0 Tick or something
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
I want to get a return value of date in column C whereas column A represent a date and column B represent a number. Simplest formula in excel is Column C (Feb 26, 2014)= Column A (Feb 21, 2014) + Column B (5). I want it to have in macro code.
View 14 Replies View RelatedI am trying to return a TRUE or FALSE based on a date in a cell.
for example:
if cell A1 = 07/11/2009
I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.
I have tried stuff like:
=IF(A1=TODAY()-14,"True","False")
but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?
I would like to programatically add information to an Excel file's Properties, the Details tab. I have alot of files in the applicable group. Files are .xls but I'm using Excel 2007.
View 3 Replies View RelatedI want to add some to a macro have which asks before performing the rest of the macro... "Are all details correct?" with a yes and no button. yes, carries on with the macro and no ends the macro.
View 2 Replies View RelatedI have as list of company registration numbers and would like you use code to input them into the companies house website - Failure Page
Comany Reg No example - 03292899
In order to get the date of the last accounts.
The problem is then when you submit on the site i cant see how it passes the company reg number through to load the next page. If I can get to the page then i have code to get what i need from the page but i cant find a whay to get the to page that i want.
how to use the example reg number to access the companies house page for this company.
Using one spreadsheet with three date columns and two columns counting days.
If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval
Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)
If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval
Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4
I have 2 workbook named jhay and the other one is peng. In a jhay workbook in cell a1 there is a formula of sum(a2:a10).
In a workbook peng in cell a1. I want to copy the value (not a formula) of cell a1 in workbook jhay using command button.
I am trying to transfer information for the 'Master CCO CCB Document List' tab into one of the other tabs ('In Progress' and 'Complete') based off of the status. If the status is Complete, Approved, Declined will transfer to the 'Complete (Approved or Declined)' Tab. If the status says 'On Hold, Pending, 1st & 2nd Session Review needed will transfer to the 'In Progress (On Hold-Pending)' Tab.
I want the master list to house all of the entries, so when the status changes it is just copied to the other sheet and not cut and pasted.
On the 'In Progress' tab, once the status changes to Complete, Approved or Declined I would like for the item to be cut and pasted into the 'Complete' tab,
I'm stumped and really not sure if this will work properly. I've tried everything and now I can''t figure out how to add the contents from the form to the master list tab.
I have 2 pivot tables on the one tab, called "Other VAT".
When I double-click a cell within the first pivot, instead of opening a new tab with the data, I want the data to appear on the same tab in cell A40, which is a free cell underneath both the pivot tables.
If I then doubleclick a cell within the second pivot table, I want it to appear underneath the data from the drill down of the first pivot.
I am currently running XC running races and am wanting to know how i can have a quicker way of sorting out the best of sex, best of age group and best overall. The age groups are Under 18, under 30 and over 30. What can i do with v- lookup and other formulas to make it easier to record when they come in with out a long wait, i just want to be able to type in thier number and get all thier details so i can add the time straight in, then from there work out the winners instantly
View 2 Replies View RelatedI've been trying to create a macro that shows the detail of some outlimg grouped rows. I wasn't entirely sure of the syntax, so I used the macro recorder and adapted. I came up with the code below, which creates an error on the final line of: "You've entered too many arguments for this function"....
View 4 Replies View RelatedI am creating a personal expense tracking sheet for the upcoming year. I have each day of the year going down as my rows. My column headers are various expenses I expect to have, such as a phone bill and internet bill. I want a way to pick which expenses (columns) are visible. I expect this to aid in the inputting of expenses and the reviewing of expense types. I know of course, I could just click all the columns I don't want and hide them, but I'm looking for a quicker way if possible.
So, if I want to record the phone bill, or just look at the phone bill expenses alone, I could use a drop down which listed all my column headers and check which one(s) I wanted to view. I thought of like the way a pivot table option looks to pick what you want to view /shrug.
Maybe a drop down is not the way to accomplish this, and maybe my spreadsheet should be laid out differently.
I have aworkbook with Employee details data sheet. And, I have built two userforms to search and view the employee’s full details. I don’t know how to link the rowsource for combobox and textbox.
I have attached the excel file of what I'm trying to accomplish.
Book2.xlsm
I create daily report for dispatch documents to our various branches. Now I am looking for formula (or formula is not possible then VBA code) that will auto update document number and dispatch date in "output" sheet.
Find attached sheet with desired output.
The file have 4 Tabs (see attached file). The tab called staff, project 1 and project 2 are manually entered. I need to combined some of the information contained in the 3 tabs into "assignment" tabs in order to show the staff workload against the projects/tasks assigned to the individual staff.
In "assignment" tab, I would like to get have pull-down function to obtained the Project 1 and Project 2 "Activities" in Cell Column B & C and automatically placed "start" & "finish" date Cell Column D & F onto the assignment tab Cell Column B, D, E & F
In "assignment" tab, the staff assigned to execute the tasks will also be able to obtain by pull-down function obtained from the "staff" tab, the position Column C, Name in Column F, Job in Column E, Start in column H and Finish in column I onto the "assignment" tab Column B, C, D, E & G
The timeline should show automatically the bar based on the start & finish date. The colour for Task and Staff to be different colour.
I have a workbook which I use for invoicing customers. Sheet one is the inovice template. Sheet two is the contact details of all my clients (i.e. column headings are Name, Address 1, Address 2, Town/City etc.).
In the invoice sheet I would like to be able to select the name of the client from a dropdown so that it automatically fills in the address into the other cells of the invoice.
My worksheet is attached. It asks the name whenever we will open this workbook. The same would be written autometically in open by cell with date & time.Then it ask "whether its new project or not"? If yes then then approved by cell & date-time would be blanks or if no then it will show the last approver's name, date & time. I need whenever anyone approve this in case of NOT NEW Project so the name of the approver & Date & time should be recorded in the another sheet name "record" along with the sr. nos. But the record will be blank whenever we will select "yes" in "whether its a new project". I want to make it password protected & password is "007".
I have seen this on many spreadsheet but never figured out how to do this - click on a cell in a spreadsheet to open up a new spreadsheet?
View 4 Replies View Related1. To compare two workbooks with exactly the same columns- Target and Source
2. The target workbook basically has more row items than the source since this is considered the masterlist.
3. The source worksheet's names and contact details are filled out by different sources but will still maintain the same columns.
4. The records of the source workbook must be transferred to the Target workbook reference to its appropriate IDs.
5. The record of source book may be updated and will be fed to the Target book but must not copy the record that is already present in the masterlist.
Below is the code I was working at
Public Sub CopyRecord()
Dim sRng As Range
Dim tRng As Range
Dim sCell As Range
Dim tCell As Range
Dim tLR As Long
Dim sLR As Long
Dim sCount As Long
'Find last row of target sheet
Workbooks(Target).Sheets("Sheet1").Activate
tLR = ActiveSheet.UsedRange.Rows.Count
'Find last row of source sheet
Workbooks(Source).Sheets("Sheet1").Activate
sLR = ActiveSheet.UsedRange.Rows.Count..........
I have in Sheet 1, in column A, the list of employees who are existing, in column B their mobile numbers and in column C the amount of mobile usage for the month June 2007.
In Sheet 2, in Column A, I have the existing list of employees of June 2007, some new employees who have joined, in Column B their mobile nos. and in Column C, amount for July 2007.
Similarly, for August, Sept. Oct. 2007 etc. In each sheet, the old employees are copied and new ones are added alongwith their details. It so happens, some employees leave the organization in between in August, Sept. or Oct. 2007.
Now, I want to extract in column A, the list of all the employees who were existing initially in June 2007, who have joined later on and who have left in between, their corresponding mobile numbers in column B and their amount in column C and show them monthwise in one single sheet side by side. E.g....
In excel, is there anyway to find the details(history) of the previously deleted rows (using vba or anyother way)?
Is there any Site which is providing the Complete Details with Example of Formulas in Excel 2007.
View 9 Replies View RelatedI'was given a piece of code to read through stationeries in Lotues Notes so I could quickly retrieve all the Sendto, CC and BCC information.
I'm now trying to modify that so I can check two specific folders in a mailbox and retrieve all the dates that e-mails have been received on.
Code is here:-
Sub new_reader()
Dim Maildb As Object, view As Object, Session As Object, entry As Object, entries As Object
Sheets("Sheet4").Select
counter = 2
Set Session = CreateObject("Notes.NotesSession")
Set Maildb = Session.GETDATABASE("servername", "boxname")
If Maildb.IsOpen = False Then
Maildb.OPENMAIL
End If