Pull Some Statistics From A Workbook
Jan 27, 2009
I'm trying to pull some statistics from a workbook I've been sent and am having some trouble working out how to achieve this.
I have a small interface that allows the user to choose a possible answer (drop-down menu) from a questionnaire (Yes, No, Maybe)(cell: C6). On the interface the user also picks the question they wish to see the stats for (cell: C5)
The data I have been sent has been set up with the questions along in row A, and the answers below in each column (the answers run across row A from column G - AH).
So, what I'm basically after is a formula that first looks up the question specified in C5 (I've used HLOOKUP to pull some other data), and then counts how many times the Yes, No or Maybe answers appears in the column where the question data is held.
View 4 Replies
ADVERTISEMENT
Mar 30, 2009
I want to manipulate team statistics and having a bit of trouble trying to figure out how to do it. I am relatively new to excel and am using Excel 2007.
I have attached the excel file for your reference (the same file).
What I need to do is first find out the team number using the table on first sheet named "Teams". User will enter team name on call B4 of sheet "Team entry" then in cell C4 there should be a formula to find the team number (is beside name on sheet "Teams) and displays it (on C4). Then on cell D4 of the same sheet "Team entry" there should be a formula that displays cell A1 (shows a statistic for that team) of the team sheet. Each team has its own stat sheet named by its team number (easier for me to keep track). So basically D4 should show cell A1 of the sheet that shows stats of the team entered. In addition cell E4 of "Team entry" should display stat2 (of the sheet "stat2")for the team entered.
View 2 Replies
View Related
Feb 27, 2014
I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.
Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With
View 6 Replies
View Related
Nov 24, 2007
I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:
to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.
Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
Windows("testproject.xls").Activate
currentCell.Offset.(0,1).Select
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"
The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.
View 6 Replies
View Related
Jan 29, 2014
I want to be able to identify an Excel workbook in a cell, then have another cell pull the value from a specific cell in that workbook.
In the sample attached, I want the formula in cell C4 to pull the value in cell D35 from the workbook identified in cell C3. When I click within the brackets [C3] it asks me to choose a file name from my Explorer file list. "='[other_workbook.xlsx]Audit Information'!D35. I don't want to do that and want it to choose the file listed in cell C3.
View 5 Replies
View Related
Sep 27, 2007
I have a workbook I created that is full of macros, graphs, and formulas. Five of the tabs in the workbook are where I will be putting my data. This workbook will also be used by others that don't even know what a macro is or does so I need to make this as simple as possible. How I get my data:
I have a website on my intranet where I have to login and then I have five templates that I run. I can have the results given to me either in HTML or in an excel spreadsheet. The excel results are very basic. Heading for each column and the data below. I have the report generated with blank columns since my page the data will go on has formulas. Thus far I have been copying all rows starting with row2 and doing a paste special (values, skip blanks). I had a macro in my personal.xls file to do this for me but most users will not know how to import it so I need a resolution that will be saved in the WB I send out.
View 5 Replies
View Related
Feb 10, 2008
I have a folder that contains hundreds of excel workbooks. Each workbook has 20 or more sheets. All workbooks have the same look and design. All workbooks have macros that they use to function and the macro I want cannot interfere with their macros.
What I want to do is copy certain cell values (some numeric and some text) from several sheets from these workbooks and paste them into another workbook (a master workbook) that is essentially collecting these values into a table without having to open all these workbooks. For example, let’s say all of the workbooks are named with a numeric value starting with “one.xls” and that the sheets in these workbooks are named “a”, “b”, and so on. So, in the master workbook in cell “B2”, I want the name of the first workbook searched (“one.xls”); then in cell “C2”, I want the value from cell “J45” on sheet “b” of “one.xls”; in cell “D2”, the value from cell “B32” on sheet “b” of “one.xls”; in cell “E2”, the value from cell “K13” on sheet “e” of “one.xls”; in cell “F2”, the value from cell “R43” on sheet “k” of “one.xls”. The in cell “B3” of the master workbook, I want the same information as before except from “two.xls” instead of “one.xls”, and so on down to “three hundred.xls”.
View 4 Replies
View Related
Aug 6, 2014
Using Excel 2010. I have 2 worksheets, saved in same folder (if that matters).
Worksheet 1=Master_List.xlsx
Column1
Column2
Column3
Column4
PartNumber
1002
Sony
Playstation
4D
W74859
Worksheet 2=Sample_List.xlsx
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Column1
Column2
Column3
PartNumber
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
Doesn't Matter
1002
Sony
Playstation
Basically, I'm looking to populate the blank cells in the PartNumber column (Sample_List) by looking at the data in Column1, Column2, Column3 then going to the Master_List, matching up Column1, Column2, Column3. The PartNumber value in the row with the matching values would then be copied to the Sample_List.
View 4 Replies
View Related
Feb 3, 2010
I'm looking for something that will be able to push or pull data from a workbook (named "Personen - test.xls" = persons) to an other workbook (named "Personen per maand.xls" = persons/month). In attached documents i tried to make clear (sorry, it's in dutch) what i would like to do. In English; the marked data in "Personen - test.xls" should automatically be shown in the marked area in "Personen per maand.xls". I think the biggest problem is the fact that "Personen per maand.xls" is a standard-document which should be filled -time after time- with many different data's from several "Personen - test.xls" documents. These documents are all named different, the data is always given in the same way. So in my opinion i should make something where i can tell excell which document to use and something i can tell where to put the data. But i really don't know how to do this...
View 11 Replies
View Related
Sep 3, 2013
I need a formula or a macro here, or something completely different. I have one spreadsheet that tracks all records that I produce. In one of the columns (F), I have indicated whether certain forms were issued with the record. This column can either contain an "x" or nothing at all. On a separate spreadsheet, I track and record the results from all these forms. I need something that will make the second spreadsheet (TrackTrend) automatically search column (F) on spreadsheet one (RecordTracking) and if "x" is found, return the information for columns (A-C) for that given line. If nothing is found, nothing should be returned.
View 3 Replies
View Related
Sep 20, 2007
it is possible to get a range value from a worksheet without having to open the workbook. I have being trying for a long time to try and work out some code to do the good but have failed miserably. Please find below my latest attempt
Option Explicit
Sub ValuationDataExtraction()
Dim shtDataSheet As Worksheet
Dim strMfolder As String
Dim rngBrokerName As Range
Set shtDataSheet = Worksheets("Data Sheet")
strMfolder = "RD: PEP data files:PEP valuations:PEP 13200s:13235.worksheets(5th Apr 08)"
Set rngBrokerName = strMfolder.Range("brokerName")
shtDataSheet.Range("b65536").End(xlUp).Offset(1, 0) = rngBrokerName.Value
MsgBox "Done"
End Sub
View 5 Replies
View Related
Oct 30, 2008
I'm having some difficulty getting ISERROR to work in certain cells that pull data from other sheets in my workbook. I have had no trouble getting it to work in cells that do not take data from other sheets.
This formula: =IF(ISERROR(DATEDIF($E$2,D18,"d")),"-",(DATEDIF($E$2,D18,"d"))) is an example of how I successfully got the function to work. All is well with this.
However, on a different sheet using these two formula: ='Mike Anderson'!A68 and ='Mike Anderson'!J536:L536 I could not get the ISERROR formula to work.
Unfortunately, I'm enough of a novice using Excel, that I don't know if I'm doing something wrong with the formula or if it's just not designed to work with values from another sheet. This is how I was trying to make the formula work =IF(ISERROR('Mike Anderson'!J536:L536,"",'Mike Anderson'!J536:L536)). Doing so this way created an error in which the little Excel helper guy that pops up highlighted the quotes inside the formula.
The error message I'm trying to get to not display using the "" is just a #DIV/0 message that fills the cell until some data is entered into the sheet.
View 2 Replies
View Related
Apr 5, 2007
I just started playing with creating my own macros. I've written a macro to pull all the names of the worksheets in a workbook, now what I want to do is write a piece of code that will pull data from a cell in those worksheets.
So for instance I have all my worksheet names in cell A1 to A10, I want to pull the data from cell E1 from every worksheet in A1 to A10. Can someone help me with a sample code to work with. I'm still reading Power Progamming haven't gotten so far yet.
View 9 Replies
View Related
Jul 14, 2007
I have a sheet with 200 sheets in called workbook x , I need to create a summary new workbook with only data on 1 sheet.
I want to pull specific cells from the large workbook x to 1 single sheet on a summary workbook:
A6 I want to be surname taken from B1 of workbook x
B6 will be first name taken from C1 of workbook x
Then I will copy data from M1 on mastersheet to cell C6 on new workbook
Then I want to copy Row A11 up to T11 and paste on the new workbook (Summary) in D6 staying in the row 6
Not sure if this is the tricky bit I want to repeat as above B11 to T11 but paste in the next empty cell of the same row of D in my summary
I need to repeat this action upto A23 to T23
Then I need it to move on to the next sheet in X and repeat all the steps above but do it on the next row down row 7 and so on
View 9 Replies
View Related
Jul 5, 2007
I am trying to Loop Through a Folder Of Excel Workbooks and get required Info from one of the workbooks, back to my Master File.
I thought of "VLOOKUP" but I am not shore if it will work over Approx. 60 workbooks? if there is an other or better approach, I am open to all Ideas.
So far I have code to Open my Master and declare the criteria for the VLOOKUP with the help from:
Re: Run A Macro On All Files In A Directory
--------------------------------------------------------------------------------
See also this page on how to loop through a folder
Loop Through Folder Of Excel Workbooks
_________________
I am now able to open all Files in the sub directory, now I need to know how to approach my problem of looking for and getting the Info back to my Master File with approx. 5000 entrys to look for?
View 9 Replies
View Related
May 6, 2014
I want vba on pulling data from closedworkbook from certain path with range of end column values
I have two workbook one is Opencall_06-05-2014...
File string contains Half today date...
I want pull data from Opencall_06-05-2014.xlsb ,Sheename is "Opencall" Paste into Master file...with range of (A:BC).
Find the attachment of two workbook...
View 5 Replies
View Related
Feb 7, 2014
I'm trying to pull out the earliest and latest dates associated with a text string in a workbook.
Min Max dates example.xlsx
i.e. in this example, I want one column to display the earliest date associated with DU 145, and the next column to display the latest.
From what I understand, I need an array formula to do this and have written the following, which works:
=MIN(IF($A$2:$A$6=A2,$B$2:$B$6))
(confirmed with CSE) - and the same for MAX.
Where I'm struggling is that DU 145 may be entered in the workbook as DU145, DU 145 or DU-145 and I need to take all of them into account. I tried:
=MIN(IF($A$2:$A$6="du*145",$B$2:$B$6))
As this works in a COUNTIF formula in the same sheet. But this just returns a date of 00/01/1900. I'm new to array formulae & haven't really worked them out yet. Is there a way to do this? I assume it's something to do with the way I'm entering text as removing the wildcard * makes no difference.
View 4 Replies
View Related
Feb 4, 2013
Here is what I have and am trying to do:
I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):
Date:January 01, 2013
Day:Tuesday
I then entered all the data needed below that (B5:E26):
TimeTransactionsTotal SalesAverage Sales
12:0039 $352.97 $9.05
13:0053 $416.98 $7.87
14:0044 $339.42 $7.71
15:0043 $304.96 $7.09
etc.....
In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).
View 1 Replies
View Related
Apr 28, 2008
I want to pull data from closed workbook based on cell values of open workbook of column B and the source file name is on cell J1. Actually I save monthly files and opening balnce of current month should take vakues from previous month file.
Suppose current month is May 2008. Then Column Column D for May month shold take value from column G of April 2008. For simplicity the previous month’s name and thus source file name will be placed on cell J1.
The code should loop from column B of source file and current May 2008 file and should pull values for only those items which are in the current file in the Column B. Thus those products which are deleted or newly added item in the current item should not copied. Though for new item no name will be thre in the source file but for deleted items the item might be there in the source file but the code should ignore those value.
View 4 Replies
View Related
Aug 23, 2007
Been searching the archives but not finding my answers. I downloaded historical lottery numbers and want to run statistics on them- just no clue where to start. I guess the 1st and most important thing is to figure out which #s have come up most often out of all of them. I assume it's some sort of COUNT or LOOKUP code- but not sure. I have the #s arranged in a sheet like so ...
View 6 Replies
View Related
Jun 27, 2008
want to make a macro to do a 'descriptive statistics data analysis' of column b and put the results in m1 to n18.
basically doing this but in a macro.
add in= tools /data analysis/ descriptive stats
I have recorded it doing this, but it for some reason can't do what it wrote.
View 9 Replies
View Related
Mar 9, 2009
On Sheet1 I have 2 cells one has the date 1-mar-09 and the other has 7-mar-09. I need to find this range on Sheet2 which has all the dates for the year in ColumnA and then total the amounts found in Sheet2!ColumnB for the specified date range. Is this possible without VB?
View 9 Replies
View Related
Dec 3, 2009
I have two rows in a sheet with random numbers (1 till 90).
Those numbers in those two rows I change them every 5 minutes with new one still from 1 to 90
I need a "way", in one side of my sheet (lets say in column B) to count how many times a number was repeated after finish my work (example after 2 hour's)
Is that possible with excel 2007?
View 7 Replies
View Related
Oct 9, 2002
I have been looking everywhere for this. In SPSS, it is very easy to recode data, but how do you do this in Excel? For example, researchers often "reverse" their questions on surveys with Likert scales to eliminate respondent errors. In those questions, you want 1=5, 2=4, 3=3, 4=2, 5=1. Is there a plug-in or some other formula that is out there?
View 9 Replies
View Related
May 16, 2008
I have a table of contents page. The TOC is updated each time some one opens the sheet (this is a must have based on the requirements). I'm trying to include some statistics next to each item in the TOC. These statistics are on each worksheet and just need to be copied to the TOC.
Now my problem.
The worksheets are NOT keep in alphabetical order, so After the TOC is created a sort is done to put the TOC links in alphabetical order. After the order is set, I need a formula that will read the worksheet name from a cell on the TOC (which is really a hyper link to a worksheet).
I have this already:
ActiveCell.FormulaR1C1 = "=Address!R[-2]C[-2]"
If "=Address!" (Where address is the name of one of the sheets) could be replaced with a cell reference that (I think) would do the trick.
My TOC is a MACRO not a cell formula so if this can not be done with a cell formula but can be done through VBA that would be fine.
View 6 Replies
View Related
Jul 6, 2008
I have running total of bowling scores current & going back from when I first started.
What I’m trying to archive is a formula that will count the most recent 96 scores and still allow me to add new scores each week in keeping the current running total of 96 games.
The statistics are listed from A4:C215 that may have 1 or 2 scores not listed. (missed games)
Each week new statistics will be added in cell A4:C4 anywhere’s of 1-3 games.
View 6 Replies
View Related
Sep 11, 2008
Back to test you with a few problems as I work through a project I'm carrying out, based on past results data of the English Premier League.
I have some of the solutions/formulas I require already, but there are still some gaps to be filled in!
***
the spreadsheet is set out as follows:
Key Columns / Sub-Title (Remarks)
B Date (the date a match was played - the s.sheet is sorted by this column, A>Z)
C Home Team
D Away Team
E Home team goals scored
F Away team goals scored
I-M Indicates via U or O whether the match contained under or over 0.5,1.5,2.5,3.5,4.5 goals
N Points obtained for Home team (i.e. 3 for a win, 1 for a draw, and 0 for a loss)
O Points obtained for Away team (i.e. 3 for a win, 1 for a draw, and 0 for a loss)
There are 381 rows in total for each worksheet (season) - 1 for the titles, and 2-381 for each match played in the season...................
View 9 Replies
View Related
Mar 3, 2007
I try to predict some macro economic statistics but any attempt till now didn't make sense. the attached file. Note: when i used the FORECAST function the predicted values showed an unlogical drop while there seems to be a positive trend.
View 4 Replies
View Related
Mar 14, 2008
I am creating a workbook to manage golfer scores, teams, winnings, handicap, ect. I am having trouble with the statistics sheet. I Need A Sub To:
1. Column "B" , take the average of the lowest 5 numbers in columns "AB" though "AU"
2. Column "C" , take the average of the lowest 10 numbers in columns "G" through "Z" divided by 0.96
3. Column "D" , take the average of the lowest 10 numbers in columns "AW" through "BP"
this needs to happen for each row where there is a name. (names added daily)
I have included a similar sheet as the one i am working with along with some command buttons typically not included. Should show some of the problems I am having.
View 5 Replies
View Related
Apr 1, 2008
I need to record on Sheet 2 how many times enteries in Coloum B on Sheet 1 appear per date range.
View 3 Replies
View Related
