Posting Dynamic Date In Individual Spreadsheets To Overview Spreadsheet
Jan 2, 2014
I am trying to build a workbook to track patient treatments. My overview sheet needs to list the last treatment received per patient which is listed in column a of each patient's individual sheet dynamically so I can review the history of treatments as well. I have tried to create a dynamic list, but it is not functioning correctly. I was using : =OFFSET(txdate,1,0,COUNTA('patient, name'!$A:$A),1) , where column A ( the named range "txdate") lists the treatment dates in succession and should be updated automatically when a new treatment is done.
View 10 Replies
ADVERTISEMENT
Sep 26, 2013
I have a sheet from which I load data. A normal cell looks like this:
Code:
='...dataOutput[1.csv]1'!E2
I wrote a script however that writes new data into a new sheet every day, and saves it with the name of the date. The obstacle is, that I won't have a sheet for each day (ex: weekends, holidays). For that reason, is it possible for excel to scan a certain folder, and open the 20th file when sorted by date to read from?
View 1 Replies
View Related
Nov 22, 2010
I am trying to automatically insert a time into B and a date into A every time something is entered into C. I want the time and date to not change from the time it was posted. What formula will accomplish this?
I tried using if(C"",now(),"") while having Workbook Calculation set to automatic, but it would update every cell in column B every time I entered data into a new cell in column C.
View 8 Replies
View Related
Mar 1, 2014
trying to get a specific value to appear if the value falls within two dates in which it was posted.
I have the following data
1/1/2014 in Cell AB2
$1000 in cell M3
1/1/2014 in cell P3
12/1/2014 in cell Q3.
What I'm trying to do, if Value 1/1/2014 AB2 falls between 1/1/2014(P3) and 12/1/2014 (q3), then I want it to return $1000 (M3), if it does not, then to post 0 or blank.
View 1 Replies
View Related
Jun 18, 2008
I want to create a single spreadsheet containing a selected row from each of 365 spreadsheets;
Background:
(1) I have 365 Excel spreadsheets (1 for every day of the year);
(2) Each spreadsheet contains:
(a) 24 values (one for every hour) horizontally with hourly average temperatures; and
(b) 8,000 records (vertically) for 8,000 different locations;
I want to create a new single spreadsheet in which I select a specific location (say, Linden, New Jersey), and capture the temperatures for all 24 hours (horizontally) and all 365 days (vertically);
the file names are all;
20070101.xls, 20070102.xls, 20070103.xls, ............ 20071231.xls (one for each day of the year)
(I tried using =VLOOKUP(Linden,CONCATENATE(A1,A2,A3,"'.xls'!","$B$1:$CA$8017"),3,FALSE); where A1, A2 & A3 are year, month & day respectively) but was unsuccessful;
View 8 Replies
View Related
Dec 17, 2012
(i) I have a spreadsheet listing all the investments details. For example,
Investment Investment Date Amount
xxx company 1/1/2012 $10,000
yyy company 2/1/2012 $20,000
(ii) Each investments have different investors. For example, xxx company has two investors: A and B; yyy company has 4 investors: B, C, D, E
I want to assign Investors Name and their Personal Investment Amount to the main investment spreadsheet. So that I can retrieve individual investor's investment positions.
For example, I want a spreadsheet showing B's investment. Then this spreadsheet should have details of xxx company and yyy company, as well as B's personal investment amount.
View 3 Replies
View Related
Jan 8, 2010
From a master (data entry) spreadsheet of work projects, I'd like to automatically create a tab for each individual contributor that lists all projects they are involved with. The thing is that being a contributor can mean being the lead, assisting with, or being the technician on the project (all columns in the master). To make the individual tabs easier to read, it'd be great to have that tab's contributor name in red, wherever it happens to fall in the various columns.
I've created an example that's a little simpler than reality - in real life there would be 20 tabs for the individual contributors and instead of 3 "Assist" columns there would be 20 columns.
View 7 Replies
View Related
Jul 19, 2009
I'm on a BTEC in college and my tutor wants me to create 3 spreadsheets, one of them is a Master spreadsheet which shows each pupils grade for each module, their over all end year grade and how many UCAS points that grade will earn them. The other two sheets are for the modules which will have the grades of the pupils in that lesson, Pass, Merit, Destinction or Fail. I hope you're still with me... I've attached an example of one of the 'Slave' spreadsheets as I call them.
My problem is that I can't seem to find a way of doing all this without constant nesting of COUNTIF. The code I have for my 'Unit1' spreadsheet's grade is:
"=IF(COUNTIF(B3:G3,"x")=6,IF(COUNTIF(H3:J3, "x")=3, IF(COUNTIF(K3:M3, "x")=3, "Distinction", "Merit"), "Pass"), "Fail")"
It works, but it's very messy. If this sounds confusing I apologise, the attached documents should clear some of the confusion up. Once the smaller spreadsheets work, the Master needs to use them to update it's cells. If someone has a Merit grade in 'Unit 1', the Master needs to show it too by somehow linking the 'Grade' cell from 'Unit1' to the 'Grade' cell in the Master spreadsheet.
View 3 Replies
View Related
Oct 1, 2012
I have created with most of it being done by Austrada (who has done a great job) code to copy multiple spreadsheets into 1 spreadsheet. We have run into a error Run Time Error '9'. Subscript out of range.
Sub CopyData()
'----------------------------------INFORMATION----------------------------------------
'You need to make sure you have activated the Scripting Runtime reference for the FSO to work
'--------------------------------------------------------------------------------------
Dim fso As New Scripting.FileSystemObject
[Code].....
View 4 Replies
View Related
Jul 11, 2007
Linking is what I am having trouble with... I can establish the link but when I copy down a cloumn, Excel wants me to establish the link each and every time.
I purchased Link Hacker and it tells me Error 52. Bad file name or number
I have tried the mapped drive as well as the entire directory listing.
Is there anyway to force Excel to perform the formula without having to manually tell the link where to go?
I want to type a formula and it tells itself where to find the data.
If not is there another way to accomplish the reading of the spreadsheets?
View 9 Replies
View Related
Apr 16, 2014
I am trying to find a code that will allow me to generate multiple sheets depending on what is selected in one specific column. I have found a code that is perfect for this however I can't seem to set the range. For example instead of copying all of column headings I only need to copy up to column AN2.
View 1 Replies
View Related
Jun 1, 2007
I need to have totals from individual worksheets automatically enter into cells in a master spreadsheet.
What I am doing is keeping track of donations collected from individual departments - each on their own worksheet. I would like to have the totals of each page automatically enter & update onto a master worksheet that would show the totals from each dept and then give me a total of all those.
I am not that well versed in Excel. I have been able to set up the individual worksheets and the master.....but can not figure out how to accomplish what I want with the Master tally sheet.
On the individual worksheets I also want to set it up to give me the average donation per person. So if I total the # of donors and the total $$$ amount...what formula do I use to get the average?
View 15 Replies
View Related
Mar 16, 2007
I have two spreadsheets. I need to be able to match information from detailed spreadsheet for specific information from a lookup spreadsheet.
The detailed sheet (call it Purchases) has information about what was purchased during a month.
Columns: ...
View 9 Replies
View Related
Sep 24, 2013
i am trying to do collect data from 4 different spread sheets (they all consists of the same columns but they do not have the same amount of rows) and rank them based upon one of the columns in a fifth spreadsheet. in the fifth sheet i also want to display all of the information found in the four sheets, see simplified example below. Is this possible? and how do i do it? when i googles it i only found ways to do it using macro but i know nothing about macros..
Simplified example:
Sheet 1
A 3 W
B 5 X
Sheet 2
C 2 Y
D 7 Z
What i want excel to do
C 7 Z 2
B 5 X 1
A 3 W 1
C 2 Y 2
View 2 Replies
View Related
Aug 9, 2013
I have a main spreadsheet that I am consistenty adding information to. The columns are : Company name, Contact, Territory, and Status. The main spreadsheet is titled "Main". I want 5 additional spreadsheets in the same workbook that are automatically pulling information from the "Main" spreadsheet, and populating the appropriate spreadsheet . For example...I have 500 entries of different companies in "Main". All of these companies are either categorized as "North, South, East, West, Offshore" in the Territory column. So, I would like my workbook to have 6 tabs...one "Main, North, South, East, West, Offshore." As of right now, I am Sorting the column, then copy and pasting into correct spreadsheet manually.
View 14 Replies
View Related
Mar 19, 2007
have six different locations in a city in which I have 6 beds for people with psyhiatric problems to sleep in.
The managers of the different location send me whenever somebody new comes or somebody leaves the following information:
Name of client
Start date of stay (when new clients arrive)
End date of stay (when client leaves)
I have to keep all record of clients that have used our locations, so the list gets very long during the year and it is then very hard to see which beds are still available.
What I would like to see in a separate sheet is which location still has how many beds available. I then mail this overview weekly to organizations that assign people to my locations.
View 9 Replies
View Related
Dec 9, 2013
Basically what i need to do is put a formula in each cell of "Monthly Overview" that references the correct property and monthly total from the "Monthly Billing"
Example...."Monthly Overview" C2 needs to reference "Monthly Billing" for what was billed to Apex Broadcasting (B column) in Jan (C Column)
View 6 Replies
View Related
Jul 27, 2013
I am trying to use vlookup on the the income statements I am using. I have a workbook called overview with all the months and need the "total" of each month to appear automatically. The previous vlookup formula does not seem to be working.
View 13 Replies
View Related
Feb 21, 2014
I have a group of huge data as follows. I want to create a macro to just choose the first immediate date of each individual (in another word hide the second rows onwards for each Name). How can i do it?
Name
Travelling date
A
2-3-2014
A
4-4-2014
A
5-7-2014
B
3-3-2014
B
8-9-2014
View 7 Replies
View Related
Nov 25, 2008
I have a work book that consists of 15 sheets. Sheets 1-14 are for different stores yearly data and sheet 15 will be an overview of the each month but with all the stores on it.
column B-M have the heading Jan-Dec and will have data in the rows below.
What I want excel to do is by using a command button on sheet 15(Overview)
update the overview sheet with the data that is in the column, corresponding month that is stated in cell A1 on sheet15 from each sheet of the work book.
View 10 Replies
View Related
Apr 7, 2014
My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...
Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..
When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of
Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x
I had
Code:
Combobox1.list = x
And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).
View 2 Replies
View Related
Aug 19, 2013
I have an Excel file containing data about employees. Each row contains a lot of information that is relevant for a specific date range, denoted by the columns "start date" and "end date". I would like to copy each row so that I have it accessible per date that is within the date range. The only columns that would change in the copy are the date columns. For example, for 1 row that has a date range of 01/08/2013 - 05/08/2013, I want to copy the row 5 times except the date columns become "01/08/2013", "02/08/2013", etc.
View 2 Replies
View Related
Jun 27, 2007
We (the board members of an ultimate frisbee team) have an debt workbook that we would like to dispurse amongst our team members as easy as possible. The thing is that we'd like to limit what they are seeing without having to edit the xls-file, only certain columns etc. Ignorance is bliss so to speak.
So my thought was to have some sort of function that exports data on the fly from the excel book to a html / asp / php / xml (or likewise) page everytime a member would access that page. This would give our treasure (as if..) master a lot less work and make it much easier for our members to check their debt. The workbook itself has a couple of worksheets and the ideal would be for us not to split these into seperate workbooks. Is this doable? I read about web queries, but they seem to be directed the other way, from html to xls.
View 2 Replies
View Related
Apr 24, 2014
I am trying to set up a spreadsheet with linked dynamic drop down lists, based on a table.Using the OFFSET function, I have managed to get this to work if the table is on the SAME sheet at the drop down lists. But I want to be able to put the drop down lists onto a different sheet, so that I can lock and hide the table.
I don't want to use the 'name based on the previous column' idea, as the table is large and will change weekly. the drop downs show all values, even duplicates, which I would like to get rid of.
View 2 Replies
View Related
Jan 13, 2014
How to perform this function on Google Spreadsheets (which appears to be slightly different to Excel);
Calculate the sum of the numbers in column D that occur on the 10th January 2014?
And also, Calculate the sum of the numbers in column D that occur between 9am and 10am on the 10th January 2014.
View 1 Replies
View Related
Apr 19, 2013
If I post a logo into a cell of a table, and then later move the table, the cell won't move with it. How do I set it so that the image is locked into the cell of the table? I've tried googling it but all I get are results to set the background of the cell to be the image, which isn't what I want.
View 7 Replies
View Related
Sep 19, 2012
So I'm recording some data to my access database and am wondering why my data will randomly find a place (row to be exact) between existing records in the table. It doesn't always behave this way, but does periodically. I would like my newly added records to be added new, from the bottom of the table every time. What am I missing from my code?
Code:
Sub RecordOrders()
Set iNv = Sheets("vnbinv")
Ir1 = iNv.Cells(Rows.Count, "B").End(xlUp).Row
Set wS = Sheets("1vnb")
Call OpenSessame
With rs
[Code]....
View 1 Replies
View Related
Apr 17, 2014
Is there a way to post a specific cell value into a url with macro. here is the macro. I want to Paste the cell value of A1 into the end of the URL. I have also tried to put the desired url in A1 and just reference that.
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/ks?s=(A1.value)", Destination:=Range("$A$2"))
.Name = "q?s=Activesheet.name"
.FieldNames = True
.RowNumbers = False
[Code] .......
View 1 Replies
View Related
Nov 9, 2009
Adjust this code to output the differences in WS3 instead of WS2. I'm having trouble changing the worksheet in the code.
Ta in advance. Chuf
Sub CompBelowCol()
Dim ListA As Range
Dim ListB As Range
Dim c As Range
WB1 = ActiveWorkbook.Name
WS1 = "EE"
WS2 = "13"
WS3 = "New Order"
Set ListA = Workbooks(WB1).Sheets(WS1).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set ListB = Workbooks(WB1).Sheets(WS2).Range("M1:M" & Cells(Rows.Count, "M").End(xlUp).Row)
For Each c In ListB
If c.Value "" Then
If Application.CountIf(ListA, c) = 0 Then
With Cells(Cells(Rows.Count, "T").End(xlUp).Row + 1, "T")
.Value = c
End With
End If
End If
Next c
End Sub
View 9 Replies
View Related
Mar 26, 2014
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....
View 11 Replies
View Related
