2 Different Data Sources?
Mar 25, 2014
i have a excel file that comes out of one system that i want to then use as a data source to combine with details out of a SQL server.
I need to be able to take the excel file and then in MS Query lookup from SQL to return fields based on the excel table.
I can see how you can create a data source for the excel file but i cant get a second data source added.
I need to do it in Excel rather than Access as this needs to be given to other users to use who dont have MS Access?
missing how to do it?
View 1 Replies
ADVERTISEMENT
Aug 7, 2014
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.
View 2 Replies
View Related
Jan 23, 2014
My files always says this "This workbook contains links to other data sources" update.....how can I turn this off?
View 1 Replies
View Related
Oct 22, 2013
I want to take the values in Column U, attach the names in Column A, and rank from Highest to Lowest
Then I want to do the same with Column V/Column A
And then again with Column W/Column A
And then create 3 tables within the document that display the top 5 in each category.
It's already done, but by hand. Is there a way to automatically set this up?
See A27-A32 for what I want done automatically
View 14 Replies
View Related
Jan 16, 2014
I am facing a issue with arranging a data in excel as weekly reports by getting a random input in the form as mentioned in the attachment
Needed like below
Name 1-Jan 2-Jan 3-Jan 4- Jan...........
Ram 0 L 0
Ravi 0 0 0
rohan 0 0 0
sheel 0 0 0
Inputs are like below format
name 2-Jan 8-Jan
Ram L 0
Ravi 0 0
rohan 0 L
sheel 0 L
Attached File : formal needed.xlsx
View 2 Replies
View Related
Dec 2, 2009
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.
View 3 Replies
View Related
Nov 30, 2011
I'm trying to create a pivot in 2010, which gathers a cumulative amount from data sources within two different worksheets.
View 2 Replies
View Related
Nov 3, 2009
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.
View 12 Replies
View Related
Feb 17, 2013
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.
View 8 Replies
View Related
Aug 12, 2007
I have a workbook which relies on another "source" workbook for getting some data. It then does some processing on the data and displays some results.
I want to share the workbook with the results with others, but I don't want to give them the "source" workbook.
View 9 Replies
View Related
Nov 12, 2006
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
View 4 Replies
View Related
Jan 17, 2013
Please see the attached.
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.
Wheelchair Hire-Sample New (1)(1) (5).xlsm
View 4 Replies
View Related
Jun 4, 2006
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.
View 6 Replies
View Related
Aug 22, 2013
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.
View 2 Replies
View Related
May 22, 2006
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.
View 5 Replies
View Related
Feb 15, 2013
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.
View 1 Replies
View Related
Apr 28, 2009
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
View 4 Replies
View Related
Dec 6, 2006
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
View 2 Replies
View Related
Jan 29, 2013
I am trying to create a user form that will allow the user to type int values in boxes. Then when the user clicks the submit button the code needs to add the values from each user input box to the existing values in specific cells accross multiple worksheets. Then the form needs to be cleared after the cell values are updated. I can create the form it is the code on the submit button i am lost on. Also it is important that some boxes may be left blank.
If you click the Grey "Qty Form" button on sheet1 the form will open. User data numbers can be entered in the blank boxes. When the submit button is pressed the form needs to add the user entered numbers to the numbers in the corresponding cells in sheet 1 and sheet 2. How to code the submit button to do this properly. Also after the data on the spreadsheet is updated the form needs to be cleared and start the cursor back in the Item 1 box on the form.
View 1 Replies
View Related
Dec 17, 2013
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
View 2 Replies
View Related
Jun 30, 2014
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
View 5 Replies
View Related
Apr 30, 2014
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
Example Workbook for Email Macro.xlsx
View 1 Replies
View Related
Feb 8, 2014
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
View 2 Replies
View Related
Sep 27, 2011
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
View 2 Replies
View Related
Jul 4, 2012
I have a workbook that includes 4 seperate sheets that are used to record time and expenses for 4 members of staff. I want to write a macro to select the data I need from each sheet and colaberate together in a 'data' sheet so I can combine all the info to run time and expense reports per client showing combination of all time and expense incurred from all 4 staff.
I have named cell ranges in each of the 4 time-sheets. I proceed to record a macro, select the first named range, copy and paste into my data sheet, do a control home then control down arrow, then one more down arrow to get to the first blank cell and repeat the process for all four time-sheets.
This works until I add a new line and then the data will only appear for the last time-sheet (last row of data).
View 2 Replies
View Related
Oct 19, 2012
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
View 2 Replies
View Related
Jan 11, 2013
I have created a pivot table that is connected to an input sheet with data. The input sheet retrieves data automatically from a external source through an add-in to Excel. When updating data the fields expands, but only for the items which have been changed. I want the table to be updated automatically, but not the fields expand automatically. Is there any pivot options to prevent this problem?
It should be mentioned that the pivot table is not directly connected to the input sheet (which is updated from the external source), but from a "help-sheet" reflecting the input sheet with some additional columns. I use conditional formatting and name range in the pivot.
View 1 Replies
View Related
Apr 22, 2014
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
View 2 Replies
View Related
Jul 15, 2014
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
View 3 Replies
View Related
Aug 2, 2013
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
View 1 Replies
View Related
