Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Referencing Cells From Microsoft Query


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.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Returning Data From Microsoft Query
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 Replies!   View Related
Microsoft Query - Return All Unless A Parameter Is Entered
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 Replies!   View Related
Microsoft Query: Data Source .xls Files
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 Replies!   View Related
Microsoft Query :: Put Floating Date Range In Criteria
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 Replies!   View Related
Microsoft Query :: Show Start Dates Greater Than Today/yesterday
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 Replies!   View Related
VBA To Update Cells With Data From Microsoft Access Table
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 Replies!   View Related
Highlight/Flag Formula Cells Referencing Other Cells
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 Replies!   View Related
Lock Cells Based On Value Of Other Cells - Code Simplification Query
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 Replies!   View Related
Referencing Cells On Different Sheets
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 Replies!   View Related
Not Referencing Cells Correctly
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 Replies!   View Related
Referencing Non-blank Cells
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 Replies!   View Related
Referencing Blank Cells
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 Replies!   View Related
Referencing Cells In Another Sheet
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 Replies!   View Related
Referencing A Row Of Cells In A Column
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 Replies!   View Related
Limit On Referencing Cells?
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 Replies!   View Related
Referencing Empty Cells?
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 Replies!   View Related
Referencing To Cells Next To A Given Cell?
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 Replies!   View Related
Creating Sheets While Referencing A New Row In Some Cells
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 Replies!   View Related
Referencing Numerous Cells/columns
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 Replies!   View Related
Referencing Cells Dependent On Sheet Name
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 Replies!   View Related
Referencing Merged Cells In Formulas
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 Replies!   View Related
Referencing Cells By Page Break
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 Replies!   View Related
Countif -- Referencing Criteria From Cells
I want to be able to count the number of cells which meet a specified criteria.

The COUNTIF function works if I enter the criteria manually within a COUNTIF formula. But the function doesn't seem to work if I try to reference criteria from another cell.

For example, suppose the range of cells I'm searching for matches within has been named

Possible_outcomes .

In addition, the criteria I'd like to use is in a cell called

PROFIT_THRESHOLD,

and is the result of a formula. Let's say the formula result equals 130.

Countif works when I enter the criteria manually, i.e.

=COUNTIF(Possible_outcomes,">=130")

But it returns only a 0 when I write the formula as

=COUNTIF(Possible_outcomes,">=PROFIT THRESHOLD")

I can't make the function work even if the criteria is in a cell which is not named either.

This is a serious obstacle to model automation.

View Replies!   View Related
Referencing Cells From Another Sheet, Dealing With Blanks
I have a sheet which I want to populate from another sheet within the same workbook.

I am using the following, where Data = original worksheet, T2=cell to be displayed:

=Data!T2
When i do this for the whole spreadsheet, where there are blanks it is entering "0" or in the date fields, 01/01/1900.

Is there any easy way of defaulting them to be blank if there is no content in that cell?

View Replies!   View Related
Formulas Referencing Cells Stopped Working
I made a spread sheet last year that used various COUNTIF formulas that referenced other cells. =COUNTIF(I40:ER40,"A") It worked perfect.

I copied that spreadsheet to use for this year. I cannot get this formula to work. I have reentered in various locations and forms, but to no avail. If I enter the data and then enter the formula, it works.

However, if I enter the formula first and then enter the data (which I will do over the course of the year) it does not work. I even tried to reconstruct all of this in a brand new spread sheet and it still is not working, although last year's spread sheet still works.

View Replies!   View Related
Cross Referencing Cells To Input Values
I am attempting to cross reference sheets to match up IPs to building names. I have attached an example. Essentially, I am trying to take column E on the TEST sheet and match it to column A on the IPs sheet. Whatever the Building Name (column B, IPs sheet) is, I want that filled into Column G on the TEST sheet. This would be a very labor-intensive process if it cannot be done automatically.

View Replies!   View Related
Sumif Is Referencing Blank Cells And Providing A Value
How do I get 'SUMIF' to ignore blank cells? sumif(a1:a10,h1,b1:b10) - then fill down

a= ID number
h1 = ID number
b= cost info

eg 'matt' in 'h' is finding 'matt' in 'a' and reporting on the cost info against that name in 'b', but in the middle of the list a blank is matching a blank and returning a subtotal which is in the cost column. I want to put sumif(a1:a10<>"",h1, b1:b10) but this doesnt work!

View Replies!   View Related
Referencing Multiple Cells From Inside A Single Cell
I have 4 cells with simple data in them. In another cell, I would like to make a phrase and include all the data from the 4 cells in that phrase.

Example:

="Jackpot: &G2 (&G1) / &G3 = &G4 each"

I already tried this:

="Jackpot: "&G2" ("&G1") / "&G3" = "&G4" each"

Am I missing something? I'm pretty sure this is doable, I just don't know what's missing.

View Replies!   View Related
Matching Cells Query
I have 2 worksheets - main and match. In the main I have something like the following:.....

There are 200 entries in the main work sheet. In the match work sheet I have something like:......

I need to be able to flag if the entry in the match work sheet is already in the main worksheet by using the unique keyID. This flag will be in the match worksheet.
I am using the following array formula: {=IF(A3='[main.xls]Main File'!$A$8:$A$16,"y","n")} and was hoping that this would give me a Y in my flag column but this only shows a N. If I just do a straight if statement then I get a yes: =IF(A2='[main.xls]Main File'!$A$29,"y","n")

View Replies!   View Related
Get Value From Cells :: Incomplete Query Clause
Even i can get the value y and x correctly from cells excel worksheet, but i unable to execute sql2 correctly. The error is "imcomplete query clause".

Sub configure() ....

View Replies!   View Related
Query Parameters Which Takes The Date From The Cell Into The Query
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 Replies!   View Related
Concatenating Multiple Cells With Different Font Format Query
if it is possible to concatenate multiple cells that have different font formats? For example; cell A1 is font formatted SYMBOL and contains the letter "D", cell B1 is font formatted ARIEL and contains the word "delta". What I am trying to do is concatenate the two cells while maintaining their font formats.

View Replies!   View Related
Create An Conection With MS QUERY To A Csv And Query Data
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 Replies!   View Related
Fill In Blank Cells Referencing Non-Blank Cells Above
I have sporadic cell values in a column. I will be describing the lamen logic I use to fill in these blanks manually. Find the first nonblank cell in the column, then look for the next nonblank cell in the column. I count the number of blank cells in between. If even # of blank cells, then I give the first half of the blanks the value of the first nonblank cell and the second half of the blank cells the value of the next nonblanck. If odd # of blank cells, then I do the same with the exception that the odd cell that falls in the middle will be randomly designated the value of the either first or second nonblank cell.

View Replies!   View Related
RTF- How To Convert A Microsoft Word
Does anyone know how to convert a Microsoft Word .RTF file into a Microsoft Excel file? I have tried everything I know and I have no success other than getting everything crammed into column A in excel.

View Replies!   View Related
Controlling Non-Microsoft Applications With VBA
How can I control non-Microsoft applications using VBA? For example, how can I open a pdf file, print it and then close Acrobat Reader?

View Replies!   View Related
Microsoft Visual Basic Error 400
My code here...

Sub ClearSheet()
Worksheets("Summary").Activate

If Range("CustInfo") = False Then
Range("ICompany, IPhone, IFax, IContact, ICell, IEmail, IAddress, IPOBox, ICity, IState, IZip").Select
Selection.ClearContents
Else
Range("IJobDescription").Select
Selection.ClearContents
End If

For I = 1 To 5
Range("Qty" & I).Select
Selection.ClearContents
Next
End Sub

keeps giving me an error 400. I have reinstalled xl just to see if it was related toan error I recieved earlier relating to VBE6.dll. This still works in another workbook but it doesn't work in this one. All I did was change the Range names and cleaned it up a bit.

View Replies!   View Related
To Convert Microsoft Word Data Into
I am currently working on a project, and basically I want to convert Microsoft word data into excel. Is there a way excel can read specific fields of data from word?

For example, search for a field called a particular name and a corresponding value amongst several data held within Microsoft word pages?

View Replies!   View Related
How To Remove Microsoft Excel In Title Bar?
i don't like the "- Microsoft Excel" typed on the Title Bar. how would i be able to remove it?

View Replies!   View Related
Build A List To Put Into Microsoft Access
I am trying to build a list to put into Microsoft access

Cell A1 has "
Cell B1 has 8W###
Cell C1 has " Or

I want a formula that makes D1 "8W### Or ". edit: I got it =(A1) & (B1) & (C1)
I kept trying to use "" and it would work.

View Replies!   View Related
Microsoft Date And Time Picker
i have just discovered the Microsoft date and time picker. at the moment i have a text box on my userform into which i put the date.can i use code to transfer the date to the text box or do i use the date picker instead of the textbox. if i used the date picker what is the code for the date picker

View Replies!   View Related
Microsoft Visual Basic :: Error In Loading DLL
i have a macro he was working good but now i got a box labeled "Microsoft Visual Basic" saying "Error in loading DLL" and the macro won't open. I can't figure out what I'm missing.

View Replies!   View Related
MISSING: Microsoft Windows Common Controls-2 6.0
I have windows XP and Excel 2003. In Visual Basic Editor, References, I have missing the Microsoft Windows Common Controls-2 6.0. How can I enable it? When opening the file, excel prompts Error compiling some modules. Opening the file in another pc with this common controls enabled, it works fine.

View Replies!   View Related
Macro: Open Workbook From Microsoft Project
I have discovered that I can write a macro in Microsoft project that opens Excel

ActivateMicrosoftApp(pjMicrosoftExcel)

but this call does not allow me to specify a file to open, it just opens an instance of Excel (or activates the open instance). What I ultimately want to do it to, from a Project macro, open a particular workbook and then execute a macro in that workbook.

View Replies!   View Related
Create Instance Of Microsoft Windows Journal
1) When using the command below, how can I determine what the "XYZ" component could be. I see plenty of examples of it being used to start a MS Word session or Powerpoint but I'd really like to see a definitive list.

At the moment, I'm not even sure what to type into Google that describes this part of the command's argument.

Set target = CreateObject("XYZ.Application")

2) I'm just a frightened noob on this site and I tried to follow the advice on bumping my previous post on this subject, having altered my plea. All I got was a telling-off for being a double-poster and my old old thread sat where it was despite my 'Bump' command. Do you have to have at least one reply before your thread moves?

View Replies!   View Related
Missing Microsoft Forms 2.0 Object Library
i'm using a user form and i have the microsoft forms 2.0 Object Library checked. i have sent to another user, and i can't compile, when i took at this vba tools reference, microsoft forms 2.0 Object Library checked is missing (and not available in the drop down)...?

View Replies!   View Related
Query An Access Query With VBA Function
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 Replies!   View Related
Edit Query Using Query Wizard
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 Replies!   View Related
Coding A Reference To The Microsoft Scripting Runtime Library
Is there a way that this can be coded so that when the workbook opens up it turns on the reference to the Microsoft Scripting Runtime library?

View Replies!   View Related
Search The Users Signature Folder For Microsoft Outlook
To search the users signature folder for Microsoft Outlook see if the have any .htm files, if so list them in a message

Make the print find the default printer work out if it is black and white or colour and find the nearest opposite printer. i.e. if default is colour find nearest black and white

Protect a sheet but allow tabbing from certain cells to others.

Enable the users to use the workbook without having to rely on their macro settings being Low.


View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved