Can anyone explain how to name a chart? I've been accessing charts in my VBA scripts using chart names, e.g. "Chart 4". I've been determining the chart name by recording a macro and selecting the charts. But, is there a way to either:
1- change the name of a chart (as you would name a pivot table)
2- determine the name without having to select the chart in a recorded macro
3- reference multiple charts from the same worksheet without using their names
Is it possible to use a today or now function in an sql query to an external datasource in excel 2007? I have an excel 2007 spreadsheet, that I have linked to our financial database (MySql) using the database wizard. I can get the tables to display in excel no problem. One table shows how much staff have billed in each invoice period. I would like to create a query in excel, so that when this table displays in excel it shows how much each staff member has billed for the current invoice period, as opposed to all of them.
We weren't sure how to do this, so we decided to do a query to pull out the current invoice period, then we could combine this pivot table with the one for the amount billed table to give a table showing the amount each staff member has billed in the current invoice period (do this bit in excel).
The problem is we can't seem to make the query to pull out the current invoice period work. We have tried the query below, and all the variations of it that we can think of
SELECT invoice_period FROM tbl_invoice_periods WHERE STR_TO_DATE('" & NOW() & "', '%d/%m/%Y') BETWEEN start_date AND end_date;
We have also tried referencing a cell which shows the current date, but we cannot get this to work either (we may have made a mistake in the query here, we are not entirely sure how to reference an individual cell). The only way we have been able to get it to work is if you enter the current date directly into the query, but obviously this means it's no longer automated, which isn't a solution.
I use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.
What bugs me is EDITING data.
If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!
Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
I am working on a workbook that contains drop-downs in various cells. The source for these drop downs are several Named Ranges on a tab named 'Drop Downs'.
I am locking the whole spreadsheet down so that people are not messing with the format, etc. (which is the current problem). So I don't allow people to add/delete rows, etc.
I am trying to think of a good interface to allow users to modify the entries in each drop down list (i.e. they may need to add a person to the "Resources" Range which feeds into a drop-down box elsewhere).
So, people should be able to add/delete items. I was thinking I would put a little 'X' next to each entry, such that when pressed - it would delete the corresponding entry. (Using the Assign Macro on a Text Box). However, I have to assign each 'X' a method to call - and I can't pass in an argument - so if I assign them all the same macro name - I won't be able to figure out which one was pushed. And the number of 'X' will be dynamic, so I can't hard code a bunch of functions. I don't really have a good idea for allowing additions yet (other than a button that prompts for the name and then adds it in).
I guess I could just use a Form that allows editing of the data (that would be easy and powerful to do), but I don't really want to do that - I want it to be editable within the excel spreadsheet itself.
I do not know what function will do what I need. I am compiling info from different sources to make a mailing list. Basically it boils down to the following. Sheet 1 has Names and Parcel Numbers, sheet 2 has Parcel Numbers and Addresses. I am trying to combine it in a way that I have names and addresses on the same sheet.
I have created a scorecard which has 4 key drop down lists:
- Site, Coach, Team and Agent
Coach and Team populate the dropdowns dependant on which site is selected and Agent is based on which team is selected.
I have created an external file called dropdowndata.xlsx as the main source. This file has two sheets. Sheet 1 (dropdowndata) is the main Site, Coach and Team data and then Sheet 2 (GLAteam) contains the linking Team and Agent data.
The drop down lists are based on a number of named ranges and lookups.
So the issue I am having is that the drop down lists only work if I have the dropdowndata.xlsx file open on my laptop.
Is there anyway to get this to work without having to actually open the file?
I am familiar how to perform the task of bringing in an access table into excel, then using a data validation filter to control the data set in Excel 2003.
I'm creating report from a pivot table that has two data sources from two different work sheets. I noticed that you can use two data sources if you use the pivot table wizard which I tried but it can't combine the data. So I tried power pivot the same thing happened there - I will have all the column names but it will only show values from one report and if it shows values from other reports it will show the same value in all rows.
So I realized I need to create relationships which I did with a Concatenate of the dates. But still no luck.
i have to compare two financial reports from two different sources each and it should derive the same result.
I am still working on it and only focus on Column C & D. However, i have come across some problems and hope that these milestones can be cleared so that i can move on to another step to achieve my goal.
So, my current problem is
1) #N/A (eg Cell D9 & D32) will be resulted when hlookup function is used. Since the workbook 'CB_Consol_2009' does not have the same name appear, how can i make the answer become '-' instead of #N/A when hlookup function is being used?
2) Double counting. The same name appears more than one time (eg CURRENT ACCOUNTS-RET_ (01#0505_01) and as such, its figures also calculating more than once. Is there a way to calculate the figure only once so that i come out with the correct grand total?
As my objective is to compare two financial reports results (which should be the same), this is the table i can think of. If anyone here have any constructive comment on my current format.
Suppose I have a table with a collection of "Top Ten" rankings for something. For example, suppose I have rankings from five "sources" for the Top Ten Ethnic Cooking. For simplicity, I'll show the top 5 here:
Rank Source A Source B Source C
[Code]....
I'd like a way to calculate some sort of average ranking. I have two problems: (1) I don't know what the formula should be and (2) I don't know how to implement it in Excel.
I have put together a sort of hire chart for a charity that hires out scooters/wheelchairs.
All the formulas were done and then they said that they sometimes hire out extra equipment with the chairs as well.
I have created an 'extras' column but I'm having nightmares trying to integrate it into the current formula.
I have just changed the first 3 rows to experiment with.
There is a basic cost of £2 per week or part of a week, that price is regulated from the master page.
Column P calculates the rate from the weeks/days and uses the master page as the source of costing.
I have created a column 'C' for the extras and tried to integrate it into the already complicated formula.
Unfortunately, although it seems to work, it won't work if the cost is lower than £9. At that point, it doesn't matter what I do in column C, it just ignores column C
When it gets to £12 it seems to take notice of column C, very strange.
The main file was linked to other files. Instead of using the "edit links" dialogue box provided by Excel, I wish to use a list box. This list box will show all the files that the main file is linked to.
The user will have the the option to single select or multiple select the files. Upon clicking "ok" it will "open and close" the selected files, thus updating the main file. (it should also remove the update links dialogue box of the source files if they had it, it will be a hassle for the user to click on the "don't update" for every linked file that is open)
I know the update links in Excel is able to do it but the links sometimes will result in errors, beside it doesn't offer multiple selections.
I'm currently working on a side project for work that I believe will save many man hours as at the moment we're using paper and calculators!!! I'm scheduling buys. I've worked out formulas and formats to show how much we should buy and how they should be split (for example for UK purchases we buy x amount and split it 10% 90%). However, the dates of delivery are different. See below:
PORT L'poolPORT HKPORT Portsmouth 1.113-Aug-1206-Aug-1230-Jul-12 1.210-Sep-1203-Sep-1227-Aug-12 2.103-Sep-1227-Aug-1220-Aug-12 2.201-Oct-1224-Sep-1217-Sep-12 3.124-Sep-1217-Sep-1210-Sep-12 3.222-Oct-1215-Oct-1208-Oct-12
Campaign 1 first buy (1.1) I know that my product is sailing from port HK. Therefore I want it that in the 'delivery date' cell, it returns 6/aug/12 (that's the first lot of 10%, and underneath it returns 3/sep/12) which the remaining 90%...2.2).
However, both the campaign number AND port options are variable (using a data validation drop down list). In effect meaning if I choose campaign 2 instead of campaign one, it'll return the dates 27/aug and 24 sep....if I then change the port to Portsmouth, the dates will be 20 aug and 17 sep.
I've attached a quick view of the schedule I've created. The yellow cells are where I want the dates to be returned.
I have a program that uses a query to grab data from overseas. I have an issue where some precision is apparently lost and I was curious if there was a way around this.
As an example of what is going on I have a specific piece of data I am tracking. The value is 14.9 in the SQL database and it's datatype there is a real of length 4. After grabbing this item from the query I checked the record set and the value is still 14.9. After it reaches the worksheet though it is displayed as 14.89999962. The destination cell is currently formatted as General but I have tried Text as well. Both produce the same results. As this tool is very general I would not have it formatted as a number as I don't know what I am getting back exactly.
I know computers store real/single values inprecisely but I see it everywhere else as 14.9. I would think Excel would display what I am seeing. Is there anyway around this short of formatting the cell as number with 1 decimal place?
The report is a "template" that will be ran once a month every month and will look back at the previous month's data. What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.
SET NOCOUNT ON DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = '20070731 10:10:51.450' SET @EndDate = '20070731 10:11:51.450' SET ROWCOUNT 100 SET NOCOUNT OFF SELECT EventTagName = EventHistory.TagName, Value FROM EventHistory INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey WHERE SnapshotTag.TagName IN ('SysSpaceMain') AND DateTime >= @StartDate AND DateTime
I'm trying to produce a spreadsheet that pulls infomation from different web pages throughout the day. It needs to pull quite a large amount of information from different web pages hence I wanted to produce a subroutine which could be called with different variables for each web page, i.e. url, field, destination, etc.
To actually produce the query I recorded a macro and then used the code it produced. I then changed the URL to url, a string variable which holds the urls of the pages. The destion address was changed to destination, a range variable to store the destionation location, and the webTable was changed to table also a string variable to store the table number. This is shown below.
My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )
I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.
i would like to create reports and queries in excel just like in access is this possible or do you import a layout,i have a sheet that has data on i would like to be able to pick just one row or mutiple rows and print them off could i save them to a menu to use over and over again,would i use a userform to search for these or can it be done otherways ,have seen pivot tables but seem bit complicated
I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E?
Right now the copying happens like this:
If Not rsData.EOF Then rngTarget.CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If
So should I do 1) One query and move everything in the right places 2) Multiple queries 3) Something else
Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000
