Sum If Query: Return A Value Where A % Is Used
Apr 19, 2006
I have been using the following formula to return a value where a % is used, is there anyway i can make the sum shorter.
=SUMIF(Q10,'Price Calculator'!$A$5,'Price Calculator'!B5)+SUMIF(Q10,'Price Calculator'!$A$6,'Price Calculator'!B6)+SUMIF(Q10,'Price Calculator'!$A$7,'Price Calculator'!B7)+SUMIF(Q10,'Price Calculator'!$A$8,'Price Calculator'!B8)+SUMIF(Q10,'Price Calculator'!$A$9,'Price Calculator'!B9)+SUMIF(Q10,'Price Calculator'!$A$10,'Price Calculator'!B10)+SUMIF(Q10,'Price Calculator'!$A$11,'Price Calculator'!B11)+SUMIF(Q10,'Price Calculator'!$A$12,'Price Calculator'!B12)+SUMIF(Q10,'Price Calculator'!$A$13,'Price Calculator'!B13)+SUMIF(Q10,'Price Calculator'!$A$14,'Price Calculator'!B14)+SUMIF(Q10,'Price Calculator'!$A$15,'Price Calculator'!B15)+SUMIF(Q10,'Price Calculator'!$A$16,'Price Calculator'!B16)+SUMIF(Q10,'Price Calculator'!$A$17,'Price Calculator'!B17)+SUMIF(Q10,'Price Calculator'!$A$18,'Price Calculator'!B18)+SUMIF(Q10,'Price Calculator'!$A$19,'Price Calculator'!B19)+SUMIF(Q10,'Price Calculator'!$A$20,'Price Calculator'!B20)+SUMIF(Q10,'Price Calculator'!$A$21,'Price Calculator'!$B$21)
Seems like a Pain to be using something this big, just cant seem to get it to work when i try to shorten it
View 2 Replies
ADVERTISEMENT
Nov 17, 2009
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies
View Related
Dec 19, 2006
The actual link from a list on the left side of that web page is "Printable List".
I can't work out why all other web pages work fine with;
Get External Data/Web Query, but this particular web page won't work.
Sub Test_A()
Sheets("test1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://acttab.com.au/interbet/racing?type=venues", Destination:= _
Range("A3"))
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub
View 3 Replies
View Related
Mar 15, 2007
I am using the attached code however when my database query does not return any data I dont want the Dim function to perform.
Sub macro3()
Worksheets("Data 1"). Range("I3:BU65536").ClearContents
'declare a variable to hold date
Dim strDate As String
strDate = Format(CDate(Range("startDate").Value), "yyyy-mm-dd") & " " & FormatDateTime(CDate(Range("startDate").Value), vbLongTime)
Sheets("Data 1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _...........................
View 4 Replies
View Related
Aug 20, 2009
I am querying a database in Microsoft Query and I have it set to pull in jobs that match a customers code in cell A1. I would like it so that if cell A1 is blank, it pulls in jobs for every customer.
View 3 Replies
View Related
Sep 12, 2013
I am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel. So far, I have managed to accomplish that using the code below. However, now I need to update some records in the recordset. The code below does not allow me to do so.
The following message prompted when the code tried to update the record: Run-time error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.
*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.
Code:
Sub RunExistingQuery()
'Execute prestored queries in Access
Dim Con As ADODB.Connection
[Code].....
View 2 Replies
View Related
Jun 1, 2008
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
View 4 Replies
View Related
Nov 26, 2012
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
View 3 Replies
View Related
Mar 29, 2007
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
View 2 Replies
View Related
Sep 6, 2008
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
View 9 Replies
View Related
Jul 8, 2008
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
View 9 Replies
View Related
Apr 25, 2006
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
View 2 Replies
View Related
Jan 30, 2014
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm‎
View 14 Replies
View Related
Aug 18, 2014
How to return address of the column or cell I selected from Application.Inputbox, not just return the value?
[Code] ....
View 8 Replies
View Related
Mar 24, 2009
i import a table from the internet through web query and i reformat the cells to suit my liking, but when i refresh the data the cell formatting goes back to how it started. how do i make it so it always keeps that same cell formatting?
View 11 Replies
View Related
Jul 19, 2006
Is it possible to open microsoft query without opening Excel or Query Wizerd.
View 3 Replies
View Related
Feb 17, 2007
Is it possible to use the Web Query function in Excel on a PDF file?
View 2 Replies
View Related
Feb 17, 2010
Is there a way with the following formula to tell it that if value return is = to value of cell above then find return next value?
View 6 Replies
View Related
Dec 8, 2008
I'm trying to build a query which matches two tables which say has a number like A#### , I want the query to give me the A number's which do not belong in the second table but which do in the first table. i believe i need an SQL query to do this?
View 2 Replies
View Related
Apr 30, 2009
I have a spreadsheet which we use to rank scores for our golf society.
At the start of our season it was decided that if anyone of the top 3 scores were tied we would separate them using a second criteria(back nine score). Any scores from position 4 down would still be tied.
From searching this forum i have managed to separate ties using the 'back nine' second criteria(using a hidden helper column) but only for all the ranking positions not just the top 3.
I have attached the file.
View 9 Replies
View Related
Oct 18, 2009
how I would be able to weed out and remove previous references to query names.
For example if I have an external data query named "qry1 by week", then remove the query, then create another one, how do I use the same query name again? Surely there's a reference to it somewhere, that I can eliminate?
View 10 Replies
View Related
Sep 16, 2007
Here it goes.
My spreadsheet is populated by data coming from MS Query, i'm entering a parameter value to display the desired data in my spreadsheet. My problem is, i have to close and open the file to have the parameter prompt so in that case i can enter the parameter value.
Is there anyway to call the parameter prompt so i will not open and close the file, its really time consuming...
If possible, i just want a command button that calls the parameter prompt.
View 9 Replies
View Related
May 30, 2009
I am having problems getting to work a web query in a VBA macro. The goal is simply to get a value retrieved from a web query into an excel cell.
The below code works well with standard html pages (like replacing the url with www.google.com). However, the page I want to retreive (a query at geonames.org that retreives an altitude value for given lattitude/longitude values) does not contain any HTML headers, but just a number in plain text. In this case the below query does not returns anything. I tried different formating options for the query but up to nownothing works.
View 3 Replies
View Related
Oct 7, 2009
What my excel sheet is doing is importing a bunch of data from a query. I have all my queries linked to buttons through macros. There are going to be 20-30 buttons that are linked to a query, and each query will produce a different amount of data(in this case, account numbers). When pressing a button linked to a query(i also have one 'Clear' button that just deletes the data, thats all), i use a macro to delete the previous query so it can import the new query into the same cell. The problem I am having is that when a button is pushed and there is no query, I am getting an error message. Here is my original code(macro), and the underlined code is where i am getting the error
View 3 Replies
View Related
Dec 28, 2012
I need to create a web query.
I have lot of post codes which needs to be checked in a web page and the output column needs to be updated in the spreadsheet.
Link: - Find UK Postcodes Inside a Radius
for example: - The post code WD18 1TB will be there in Excel Sheet this has to be copied and pasted in the link in column
Step 2 : Click on map OR Place radius by location name or postcode
and Step 1 : Radius should be updated as 10 and then the Draw Radius needs to be clicked. Then the output needs to be copied (in this case the output is: AL2,HA1,HA2,HA3,HA4,HA5,HA6,HA7,HP3,UB9,WD1,WD17,WD18,WD19,WD2,WD23,WD24,WD25,WD3,WD4,WD5,WD6,WD7) in our spreadsheet in the next sheet.
View 1 Replies
View Related
Jun 24, 2014
I have some thousand web queries in an excel sheet, and now I need to get the hyerlinks from each of the query into cells in another sheet. I need this as the users in addition to looking at the data I have pulled need to access the actual webpages to look at the original report - where there are a lot of images that is not effective to put into my excel report.
What I want is to get the "name" value from the properties of the web queries, and then paste the value in a cell in another sheet. I can't seem to find any answers to how to extract the actual parameter values from a web query - but it seems like it should be possible...?
View 5 Replies
View Related
Mar 6, 2007
I'm using this macro in order to bring some data to two cells.. it's currently working great...
Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M" _
, Destination:=Range("A1"))
.Name = "fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
However, as you can see from the text in bold i'm currently telling the url instead of bringing it from a cell with an url. I want the macro to be able to select the url from a cell.
I'm trying to use this solution, but i keep getting a Run-time erro ´5´:
Invalid procedure call or argument.
connstring = Range("GUS8301!B3").Value
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"))
View 9 Replies
View Related
Mar 29, 2007
I have a table that contains data for a region broken down into North South East and West areas.
New data is added to one spreadsheet (Region).
What I would like to do is have information for the North team automatically moved (or copied via a link) to the North sheet for printing off.
View 9 Replies
View Related
May 22, 2007
Is there any way to use an Excel cell as a parameter for MS Query.
i.e. Select fields from table where field = "cell value"
I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.
View 9 Replies
View Related
Jul 30, 2007
Is it possible to make an if statement in a cell that only adds the cells in a collum if they are not struck through.
I use this for when an order is complete and would like my total at the bottom of the column to change when I strike out an order in the list.
View 9 Replies
View Related
