Referencing Cells From Microsoft Query

Jan 26, 2009

I have a worksheet that pulls data from an ODBC datasource (import, External Data). The datasource is a SQL server 2005 database.

the query's sql is complex enough that it says it cannot be represented graphicly.

part of the WHERE clause fro my query specifies a date range for one of the date fields.

I am wondering if from within the SQL query in Microsoft Query I can reference the value of a cell. that way i coudl have the user enter his date range values in two specific cells.

ADVERTISEMENT

Returning Data From Microsoft Query

Sep 7, 2009

I have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:

SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;

View 2 Replies View Related

Column Sequencing Using Microsoft Query?

Sep 7, 2004

I used the Get External Data function within Excel and the query returned the fields in alphabetical order. I went back into the query and rearranged the fields into a more logical order, but when refreshed the query continues to return the fields to Excel in the original alphabetical order.

Am I missing something, or do you only get one shot at the order of the fields being returned?

View 2 Replies View Related

Microsoft Query Multiple Parameters

Mar 12, 2014

I would like to have set of data as Parameter if possible.

Example: In Microsoft Queries I have two data sets. In first one shows let's say Item Number, and second one I should have table with components of all Item numbers listed in first table.

So first table I set one Parameter (?) and i get like 8 results. I would like that second table takes those 8 results (sometimes more or less then 8) as parameter and shows data (components) for them.

If I set sign '?' I can only select one cell as parameter, is there any way I can select multiple cells as parameter and use it like SQL function IN ?

Code:
SELECT 1.ITMNR, 1.COMNR, 1.QTYPR
FROM 1.1.1 1

WHERE 1.ITMNR IN ?

And second table should look for results in first table under 1.COMNR and select it as 2.ITMNR

Code:
SELECT 2.ITMNR, 2.COMNR, 2.QTYPR
FROM 1.1.2 2

WHERE 2.ITMNR IN ?

View 4 Replies View Related

Excel 2013 :: Delete Using Microsoft Query?

Apr 8, 2014

Using Excel 2013. Is it possible to delete some records from Sheet1 if the records exist on Sheet2?

I tried several SQL variations in MSQuery but all come back with error messages.

I tried:

[Code].....

View 3 Replies View Related

Returning Data From Microsoft Query To Excel?

Jun 23, 2013

I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.

I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.

View 3 Replies View Related

Microsoft Query - Return All Unless A Parameter Is Entered

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

No Data Returned To Excel From Microsoft Query?

Sep 25, 2013

Im using a query to connect to a SQL server and return data into Excel. I can query and return the data in Microsoft Query editor but when I attempt to return the data to Excel in a table, it just says the name of the connection in cell A1. If I attempt to return it into a pivot table a get and "Problems obtaining data" notification.

View 1 Replies View Related

Microsoft Query: Data Source .xls Files

Mar 23, 2008

Trying to work through tutorial on creating offline cube from an Excel file, A CompleteGuide to PivotTables: A Visual Approach, Cornell, ISBN 1-59059-432-0, pg 248. Steps I am taking:

1. Click on Data Menu
2. Import External Data
3. New Database Query

On Databases tab
4. Click on Excel Files*
5. Click on Browse Button

I migrate to the loction of the sample files I downloaded, but no files appear in the Browse Data Sources dialog window. Files of type drop down reveals only "Data Sources" (w/o quotes). I browsed the folder with Windows Explorer. There are several Excel files in the folder as well as sample, .cub and .mdb files. I do have Excel 2007 installed on the PC as well, but working through the tutroail with Excel 2003.

View 4 Replies View Related

Microsoft Query :: Put Floating Date Range In Criteria

Jun 21, 2007

Is there a way to put a floating date range in the criteria much the same as can be done in excel ie greater than Today() but less than Today()+3? I don't want to keep going in and adjusting the query if I don't have to and if I don't keep the range tight there is too much data.

View 9 Replies View Related

Excel 2013 :: Creating Two Tables That Can Reference With Microsoft-query

Jun 28, 2013

I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.

My department processes payments, both for internal clients and external clients. My Payments table looks like:

Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity

Additionally, I have a table for Mail Opening, which looks like:

Date | Employee | Operation | Quantity

My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.

View 3 Replies View Related

Microsoft Query :: Show Start Dates Greater Than Today/yesterday

Sep 29, 2009

I am importing data that contains specific start dates and I was wondering how to filter the criteria in Microsoft query to only show start dates greater than today or perhaps yesterday. This would eliminate all entries that have already occurred. It seems I can only select a date in the criteria that exists in the data.

View 14 Replies View Related

Excel 2003 :: Referencing Cell As (MS Query) Value?

Jan 22, 2013

We have a query, that looks at an external data source (db2 i believe). It will pull all data under a certain product set, and as an additional Criteria we currently have Serial Number.

Now, if the value entered is the serial number, lets say '1234567' for example, it will happily pull all instances of that into the table.

However, what I would like is a cell where I can enter the serial number, and the query will look at the Criteria, the Value will reference a cell (where we can type any serial number), and it will then populate it! I thought that would be fairly intuitive even for a complete novice like myself - now I am likely to have nightmares about Function Errors!

What I have tried:

*Setting the value to a cell ref... ie instead of '1234567' i have set the value to =$a$1 and in that cell i have a serial number.

*Tried to create a parameter. So value of [Serial Number] I then get a prompt saying "Enter parameter Value (serial number) - Regardless of if i type a cell reference (ie =$a$1, or i input the actual serial that worked before when it was '1234567' I still get a function error!

I keep trying different permutations of things, but I am ill prepared.

View 1 Replies View Related

VBA To Update Cells With Data From Microsoft Access Table

Jun 30, 2009

I have a spreadsheet and an Access database that contain the same items and prices. The database and spreadsheet are on a server shared drive. I would like the spreadsheet to automatically update its list of items and prices from the Access database whenever the spreadsheet is opened. Right now, we update the item and price list on both the spreadsheet and the database but I want to just update the database and have the spreadsheet pull the updates from the database.

View 2 Replies View Related

Highlight/Flag Formula Cells Referencing Other Cells

Sep 27, 2006

I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.

View 3 Replies View Related

Lock Cells Based On Value Of Other Cells - Code Simplification Query

Apr 22, 2009

I'm using the code below to lock certain cells depending on the value of other cells. The code below deals with one line of my spreadsheet only and as the spreadsheet comprises 38 data entry rows I've repeated this code 38 times in the worksheet module with the appropriate changes to row numbers.

It works, but causes much screen flickering and "thinking". I'm new to vba so no doubt I've made this code too extensive or lengthy or whatever (or just plain wrong).

Can anyone assist with suggestions on how to simplify the code and/or help with code to handle all 38 lines without repeating the routine 38 times?

View 7 Replies View Related

Referencing Cells Not Next To Each Other From A Formula?

Mar 26, 2014

I have a long list of amounts one per day. Sun-Sat repeating. I Total the amounts for each week and place it next to Sun. now i have the total for that billing week.

I need to reference these numbers from a different sheet that only has one row per week. I tried to do ='sheet1'!a2 then next row down ='sheet1'!a9 and ='sheet1'!a16 etc then fill down but when i do it seems to start again and do a3, a10 and a17.

Is there a way I can get it to only use cells with figures in them? IE if cell is empty then move down one else use the amount in cell... or how do I get it to reference 7 cells below each time rather than the next immediate cell.

View 8 Replies View Related

Limit On Referencing Cells?

May 2, 2009

For example in this formula I typed in WWW as the max:

=SUMPRODUCT(($F$5:$WWW$5>=C2)*1,($F$5:$WWW$5<=C3)*1,ABS($F$6:$WWW$6))

If I try to type in a value higher than WWW it will give me an error. I was just trying to put in an amount that I would never hit within the row.

View 5 Replies View Related

Referencing Blank Cells

Jun 8, 2009

I would like a formula that checks for blank cells only between C1:C10 and E1:E10 and then insert the first and last names of the person associated with these blank cells (which are located in A1:10 and B1:B10) on another worksheet, which for the sake of argument can be called 'sheet2'.

View 2 Replies View Related

Referencing Cells In Another Sheet

Oct 4, 2009

I need assistance referencing cells in other sheets. I have a summary tab which I would have each refenced sheet listed (named for the activity being billed). In a separate cell in the summary tab, I want to bring back the value of the total amount billed for that activity. The total value is in the same cell of each sheet, as all invoice tabs needing to be referenced are identical in layout.

There is one hitch to this, the sheets referenced have not yet been created (that would be too too easy), and the activity list in the summary sheet has place holder names that will change when the activity being billed is defined (hence prompting the biller to create the sheet to be referenced).

View 5 Replies View Related

Referencing Non-blank Cells

May 22, 2006

Say I have a column with cell values 4, 0, 0, 5, 0 , 0, 0, 2, 0, 3

What I am trying to do is have in the next column a formula which returns
the number adjacent to it multiplied by the number of blanks between it and
the next non-blank cell.

So, for the example above, the output would be 8, 0, 0, 15, 0, 0, 0, 2 etc

View 12 Replies View Related

Referencing To Cells Next To A Given Cell?

Nov 20, 2008

My current worksheet is being built for use in Eve-Online, a game I play. In specific, I am keeping track of profit made by manufacturing different items in the game. I am trying to build it like this:

Column 20 is a list of items, and the next 7 cells to the right of each item contain the mineral requirements for that item. So C20 says Bantam Frigate, C21-C28 will all contain numbers. Now, I am trying to create a function in the upper-right cells (like A1-A8) where if I type in "Bantam Frigate" into A1, A2-A8 will fetch the numbers that I put into C21-C28.


Now, I will be working with thousands of items, so I have to make sure I name both the 'database' and the query exactly right, but that's not a problem to me. Can I make something that will reference a value from (cell+1 column right) or (cell+2 columns right)

View 4 Replies View Related

Referencing A Row Of Cells In A Column

Nov 24, 2008

I have a row of cells on one "Sheet1." I am trying reference these cells into a column on "Sheet2." I know I can reference one cell at a time, but I have a large number of cells in the row on Sheet1 and I know that there has to be an easier way to do it, I am just not excel-savvy enough to know what it is.

I know you can copy and "paste special" (transpose), I am just wondering how to do that while referencing. I have heard that the "offset" function can be used, but after extensive trial and error, I haven't figured it out.

View 2 Replies View Related

Referencing Empty Cells?

Jul 13, 2009

I have set up a spreadsheet in order to track and calculate the number of students we teach at our organization. It is running prohibitively slow when entering new data. On one sheet each individual session is entered (the school, program, date, number of hours etc.) This is the Session worksheet.

On the next sheet(the Program worksheet) the maximum number of students for each unique program (this is usually a combination of school and the program) is calculated to ensure we are not counting students we teach weekly as new students. This is calculated using a formula:

:{=MAX(IF(Session!G:G=Program!A2,Session!D:D))}.
"Session!G:G" = the unique program name on the Session worksheet.
"Program!A2" = the unique program name on the Program worksheet.
"Session!D:D" = the number of students in that individual session...........

View 4 Replies View Related

Cross Referencing Cells

Jan 16, 2014

Should I use the 'IF' or 'LOOKUP' functions to fill a cell based on the content of another?

If C2 contains 'Cork' I need D2 to read 'Munster'. Indeed I will need D2 to read 'Munster' for a number of different C2 values.

View 4 Replies View Related

Referencing Cells On Different Sheets

Oct 14, 2007

I currently have 3 sheets 1, 2,and 3.

Data is input onto sheet 1, this is copied into sheets 2, no real problem there. However i need to enter data onto sheet 3 and if that data appears on sheet 2 i want the line, not just the cell to be coloured on sheet 2. The data on sheet 3 will not be in any specific order in the column

I have tried conditional formatting but keep getting a message to say it cant be done and offering a formula which i dont understand.

View 9 Replies View Related

Not Referencing Cells Correctly

Dec 8, 2008

I'm trying to simply reference a cell from another tab in the same workbook. In other words I press "=" and then point to the cell in another tab and I would expect to see that value - however I don't. What I see, as an example, is: ='Nav&SPDiary'!B24 instead of the cell value (which I know exists because I've just pointed to it.

Anyone have ANY idea why all of a sudden it's started to do this? The most annoying thing is it worked fine before and now it doesn't do what I want...

View 9 Replies View Related

Referencing Cells With Dynamic Row Values?

Mar 2, 2014

I'm trying to reference text from one worksheet to another. The data is all in column A on the "Template" worksheet, however the row varies (ie starts at row 5, then 7 the 9 etc). The cell row is always +2 rows on the previous row, and column A.

What can I use to pull across the cells with the information that I want, onto the "Report" worksheet?

View 3 Replies View Related

Referencing Cells In Other Sheets Using INDIRECT

Aug 6, 2014

Applying my indirect formula to the rest of the cells in my sheet properly.

I am getting the values of cells in other sheets, using INDIRECT to use cell values to name the sheets. Here is an example of how the cells are now...

A
B

1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))

[Code] ....

The formulas in B1 are going to return the value of A5 in "Sheet 1" (or whatever value is in A1) and B2 will return the vale cell A5 in 'Sheet 2. The next line will be the value of A6 in Sheet 1, and the next will be A6 in Sheet 2 and so on.

So I have quite a few rows to get through I want to be able to autofill so it looks like this...

A
B

1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))

2
Sheet 2
=(INDIRECT("'"&A2&"'!$A$5"))

[Code] ......

As it is now, when I autofill, it autofills the Sheet name part of it. It is looking like this...

A
B

1
Sheet 1
=(INDIRECT("'"&A1&"'!$A$5"))

[Code] ......

Excel isn't intuitive enough to know I want to move cells in the referenced sheet.

View 5 Replies View Related

Referencing Cells Dependent On Sheet Name

Oct 28, 2008

I have a workbook that has a number of sheets with various project stages and dates on them. These details are arranged in small tables displayed vertically down the page. Each worksheet's name is the project code - e.g. 30033_99

I have now been asked to create an overview page that displays all these stages and dates on one page. What I'm trying to do is get Excel to look at the column with the project code and then pull information from the worksheet with that name.

View 4 Replies View Related

Creating Sheets While Referencing A New Row In Some Cells

Jul 24, 2009

I am trying to find a way to create new sheets in a workbook that when created will reference a new row on a data sheet in that workbook. Each workbook could end up containing varying numbers of sheets (anywhere from 2 to 150+) based on size limitations. There are some lookups and referenced data on each sheet that would be copied as is and not reference the data sheet. I have not included these in my example.

I'm trying to avoid having to change each cell's reference manually for all of these sheets (which is one of the solutions I've come up with so far). My current method of creating these workbooks does not involve the data sheet and each workbook starts off with data only on sheet1. I select and copy that sheet into sheet2 and every subsequent sheet. I want to incorporate the data sheet so it can be filled out by someone else and copied into that tab to populate all of the data sheets.

View 13 Replies View Related

Referencing Multiple Cells In Footer

Mar 27, 2014

I've tried to a create code from examples in previous posts but I can't seem to figure out how to get what it is that I'm looking for.

I'm trying to reference the value in cell O9 which would be a number.

Preceding this value, I would like to have the word "Rev" displayed. Just underneath those values, I would like to reference the value in O7 which is a date (3/27/2014 for example). I would like the footer to be updated anytime that cells O9 or O7 are updated.

These values would be placed in the right side of the footer of the active worksheet, and be displayed on each page that is printed out.

It's not much, but here's what I have so far:

[Code] ....

View 2 Replies View Related

Combobox Selection Referencing Cells?

Mar 22, 2012

I'm using two comboboxes to simplify data query of a database: the first narrows the query by displaying employee names, the second narrowing the selection further by displaying the equipment that employee owns. I am able to populate both comboboxes fine.

The first combo box for employee names:

Code:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("User&EquipOverview")

[Code]....

Where I am running into trouble is that I want the selection made with the equipment name combobox (ComboDevice) to populate several textboxes with values related to that specific selection in the database. That is, the combobox population needs to have the cell location included as information to pull the correct information.

Each employee has several pieces of similarly named equipment, so it's not so easy as to search the initial list for the value of the combobox selection.

View 1 Replies View Related

Drag A Formula Referencing Different Cells?

Apr 26, 2012

I have data in every cell within a column. I want to pull data from every 7th Cell.

Ex.

Cell CP7 needs to reference AG10
Cell CP8 needs to reference AG11

View 8 Replies View Related

Distinguishing Between Blank Cells And 0 When Referencing

Dec 17, 2012

Suppose I have a cell containing the formula:

Code:
=INDIRECT("'"&B$3&"'!J"&ROW($J11))

Which pulls data in from another sheet in the workbook. If the referenced cell is empty, this has value 0.

I'd like to then include this cell in the calculation of an average, ignoring it if the cell it pulls from is empty but including it if that cell contains 0.

View 2 Replies View Related

Referencing Highlighted Cells In Dataset?

Jul 11, 2013

1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008

[code]......

I have a dataset with over a 1000 rows. I just pulled one row in my dataset as an example. My formula above finds the last cell with a value in the 2 row, finds the year associated with it in the first row, and pulls the year from the first row. What I am trying to find the last value highlighted red and pulling the year associated with it in the first row.

View 1 Replies View Related

Create Array By Referencing Other Cells?

Nov 5, 2013

I have the following problem:

In cells A1 to A4 I have 4 numbers (8,7,12 and 15).
In cells B1 and B2 are the numbers 1 and 4

Now I want to use the max function to find the highest value in the cells A1 to A4. But instead of typing =max(a1:a4) I want to use other cells that tell excel where to look. What I exactly want is to say =max("a"&B1:"a"&B2) because in B1 and B2 the rows are indicated from which to which excel shall find the maximum.

View 4 Replies View Related

Referencing Range With Cells Property?

Jan 8, 2014

I have been stuck in this piece of code. Depending on what I try, I get instances of 1004 run time error.

When this code is running, the sheet is not the active one. I tried to activate as well, with no success.

Code:
Sub WriteRankData(Region As String)
Dim i As Integer, z As Long
With shCurrentYear
For i = 9 To 41 Step 4
If .Cells(1, i) = Region Then

[code]....

View 1 Replies View Related

Referencing Numerous Cells/columns

Aug 8, 2008

Let's see who's got the logic.

What I have:

Column C containing either 1 or 0.5 or ""

Column E starting at E9 which may contain "deal" or a few other things

Column I starting at I9 which may contain "here", "away", or "no"

Cell J4 which will contain a number........

View 9 Replies View Related

Referencing Cells By Page Break

Aug 5, 2006

I have a macro that uses subtotals on a dynamic list to generate page breaks by Department. Rows 1:6 of the table are set to print at the top of each page and column A is not set to print at all. Would it be possible to get cell B6 to return the value of the first cell of each page break (from column A) when the page prints. (Column A contains the department names, and though I don't want to print the column itself, I would like the name of each department to appear at the top of the page.)

View 6 Replies View Related

Referencing Merged Cells In Formulas

Sep 13, 2006

Take the following simple sheet. My data is in column A, and includes some merged cells. I place the formula INDIRECT("RC[-1]",0) in column B, and copy it down the sheet. I get the result as shown in column C, but I would like the result shown in column D!

  | A | B | C | D |
--|-----------|------------------------|-----------|-----------|
1 | Bob | =INDIRECT("RC[-1]",0) | Bob | Bob |
--|-----------|------------------------|-----------|-----------|
2 | Harry | =INDIRECT("RC[-1]",0) | Harry | Harry |
--|-----------|------------------------|-----------|-----------|
3 | | =INDIRECT("RC[-1]",0) | Charles | Charles |
  | |------------------------|-----------|-----------|
4 | Charles | =INDIRECT("RC[-1]",0) | 0 | Charles |
  | |------------------------|-----------|-----------|
5 | | =INDIRECT("RC[-1]",0) | 0 | Charles |
--|-----------|------------------------|-----------|-----------|

I realise WHY this doesn't work (as Excel refers to the merged cells A3:A5 as just A3, and A4 & A5 don't essentially exist any more), but I would like to know if there's a way to get around it, and achieve the result in column D.

View 2 Replies View Related

ADVERTISEMENT