How To Link 2 Tables - One Is From SQL Source

Jun 6, 2013

I have a spreadsheet in which I have several sheets, each of which has its own table populated by an SQL query that displays the data per sheet by customer name and then sorts by a status column that is either "open" or "closed" (Open in Descending order) and then sorts by created time (also in descending order). This table is 17 columns wide. Right next to this table, I have another 2 column table that is to be populated by hand. The problem is that the 2 tables don't talk to each other. For example:

If row 3 of table 1 contains an open action, then row 3 of table 2 will contain data pertaining to that action. If the action is closed via the database refresh, then it may move the action to row 10 now (as open is always shown at top) - however the data that was entered by hand in table 2 is still stuck in row 3.

find a way to make the 2 tables communicate and work together?

I cannot make it only 1 table as far as I can tell because the database refresh wipes out my additional manual columns every time.

View 1 Replies


ADVERTISEMENT

Auto Go To Link Source

Feb 8, 2006

I have created some link formula,
Example
CellA1 has ='Corridor Select- Input'!C5.

I was told by a colleague that there is a function in excel that allows me
to auto go to that linkd source. ie if I double click on Cell A1, I can
automatically go to Sheet"CorridorSelect-Input" Cell C5.

View 9 Replies View Related

Break The Link Or Change The Source

Jan 18, 2007

I've recently aquired a spreadsheet previously maintained by someone else. After my initial review the speradsheet has many links that need to be updated, which I do via Edit-Links, but there are also some links that no longer need to exist. I have reviewed all sheets and some links just no longer exist, however when I try to break the link or change the source nothing happens. I have had the original owner also check and confirm and they have the same issue. Finally I tried copying all the tabs into a new workbook, but the link shows up again.

View 3 Replies View Related

Change Chart Link Source

Apr 18, 2008

I have a periodic task which involves moving some sheets from an existing workbook and presenting it in a new workbook. There are some diagram that I move as well from the worksheet. However, I also move the source data for the diagrams into the new workbook as well. Everytime I do this I need to change the source data to the new worksheet which is quite tiresome. Is there a smart hack to actually point the diagram source data to the new workbook?

View 2 Replies View Related

Break The Link Between Tables?

Apr 28, 2014

I copied a worksheet with a pivot table as a new worksheet. I wanted to group each pivot table differently on each worksheet, but the changes I make on one worksheet is reflected on the other. Is there a way to break the "link" between these tables?

View 2 Replies View Related

One Slicer For Multiple Pivot Tables With Different Data Source

May 2, 2013

I have multiple pivot tables with different data source. I wish to have one slicer which control all the pivot tables. I would have one common colum for all the pivot tables which is the one i wish to control for instance the person in charge. Note that all my pivot tables are from different data source. how to do it?

View 1 Replies View Related

Update Source Data For Multiple Pivot Tables With VBA

Dec 16, 2013

The spreadsheet is comprised of 4 sales data sheets, one for each store. Each of these sales data sheets runs several pivot tables (One for dept sales, one for waste, one for sales on Selected UPCs etc) and I used to have 1 copy of each pivot table for each store (ie 24 pivot tables). Now I have found a way to have only 1 copy of each PivotTable which changes based on which store is selected using the following code:

Code:
Sheet13.PivotTables ("Top10LW").PivotTableWizard SourceType:=xlDatabase, SourceData:= _
.List(.Value)

This works great however I was repeating it for every pivot table I had in the spreadsheet, causing messy code and file corruptions.

Is there any code which I can use for updating all pivot tables, using as minimal code as possible, all in one hit?

View 3 Replies View Related

Change Source Path In PPT Link From Excel?

Dec 13, 2013

I have created a PPT which is linked with my excel data and charts. Now I have to copy it at different location (Both PPT and excel), But due to change in path; my PPT is not updating with latest data points and charts. I know, I can change link using edit link.

My PPT is having around 50+ link. some VBA code which can replace old path using the current path.

View 1 Replies View Related

Link Spinbutton To Chart Data Source

Nov 21, 2006

For example, if my graph currently shows B1:L1 range, how by clicking a spin button to make the graph show B2:L2 range?

...

View 9 Replies View Related

Copy Paste Cell & Link To Source

Sep 22, 2007

I would like to copy cells from one worksheet to another, but have the cells that I paste link to the original document. Is there an easy way to do this?

So if I'm pasting from worksheet A into worksheet B, I would like worksheet B to link to worksheet A.

View 9 Replies View Related

Link Datepicker To All Pivot Tables?

Aug 4, 2014

I have problems in linking my date picker to the pivot tables

For example: I have 6 pivot tables, all of it has date fields, and then I have a button that will show date picker to select start and end date. After I selected dates, I want to auto-filter the pivot tables using that selected dates.

View 1 Replies View Related

Link Sheets To Access Tables

Jun 28, 2007

Is it possible to somehow link sheets in excel to different tables in Access so when I update the sheets in excel it can automatically update the tables in access?

Doesn't have to be on line but I want to avoid the continous copy paste or exporting and trying to automate this process.

View 4 Replies View Related

Multiple Pivot Tables - Single Table Of Source Data

Mar 9, 2014

I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.

Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work.How to tell excel that these pivots are independent, and I don't want them changing in unison? See screen grab of my source data and where I am up to...

Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg

View 1 Replies View Related

Create Report Table From Two Original Tables (source And Select Criteria)

Mar 31, 2014

I have two tables (Table 1 and Table 2). Table 1 has names of all employees in my organization ('Name' column) and corresponding information in the second column ('Textinfo'). I need to create Table 3 from table 1, but only select those employees who are in my team, ie matching names in the Table 2. I also need to extract corresponding 'Textingo' column information. I tried vlookup but it did not work because if there more that one name in the table 1, vlookup confuses it (i think). Would you know what function or code to use?

Sample file is attached : excel question 1.xlsx

View 2 Replies View Related

Excel 2010 :: Pivot Tables - Data Source Reference Is Not Valid

Jun 27, 2012

I've been trying to create pivot table in excel 2010. Unfortunately I've been getting this message "Data source reference is not valid"?

View 2 Replies View Related

Link And Change Source For Multiple Tabs From Excel To PowerPoint

Jan 18, 2012

I have to prepare a monthly presentation for my company. I have all financial data in multiple tabs in excel that have been linked to many powerpoint slides.

The problem is, I update the same excel sheet every month and save it with a different name. How can I change the source in powerpoint to select and update all the slides with all the corresponding tabs.

At present, I use Edit link (ALT+E+K) > Select each slide on the powerpoint> Change source to new excelfile. When I select all the sheets, the "change source" greys out. Hence I have to do it individually.

View 2 Replies View Related

How To Prevent Pivot Tables From Changing Source Data Numbers Into Dates In Field Columns

May 13, 2013

why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?

View 1 Replies View Related

Link To External Source Workbook For Data Validation Lookup Values Returns Zeros

Mar 25, 2014

Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.

I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).

I've investigated the techniques in this link: [URL]

Questions:

1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?

2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?

View 5 Replies View Related

Change Pivot Source Data In Multiple Pivot Tables?

Jan 21, 2013

I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.

Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.

Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.

View 4 Replies View Related

Excel 2010 :: Creating Multiple Tables In VBA And Referring To Newly Created Tables?

Jul 1, 2013

Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.

Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.

Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then

[Code].....

View 2 Replies View Related

Validation Source: Change The List Source

Apr 1, 2009

Not sure if this can be done, still a rookie at this stuff. Everything works but can something be wrote into code too change list source?

View 5 Replies View Related

Formula That Uses Link In A Cell To Create Offset Link

Jun 28, 2013

I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.

On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.

In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?

View 6 Replies View Related

Pivot Table Retains Old Source Data In Addition To New Source Data

Sep 7, 2006

I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).

Does anyone know how to purge the old data from the internal Pivot Table memory?

I hope this is enough information....let me know if you need more.

Thanks in advance for any help,

Jon

View 9 Replies View Related

Row Source For ComboBox

Apr 28, 2008

Below is my code. I need the row source for my combobox to be dynamic. I created a named formula and referenced the formula in my code. It doesn't work.

This is the formula "listname"

=OFFSET(MyList!$C$2,0,0,COUNTA(MyList!$C:$C)-1,1)

Private Sub ComboBox1_Change()
ComboBox1.RowSource = Range("ListName")
End Sub

View 9 Replies View Related

Automatically Going To The Formula Source

Mar 17, 2014

Is there any way to directly go to a formula? Instead of searching it through all the sheets? For example, if I have a formula =SUM(Sheet1!A1:A5) is there any way for me to automatically go to the source?

View 7 Replies View Related

Getting Web Page Source (HTML Or XML) In VBA

Jan 19, 2008

I would like to retrieve contents of a web page, be it HTML or XML, into VBA variable!

Later, I would chop, cut, parse or extract the data I need.

Both importing as XML or WebQueries is unsatisfactory for a certain number of pages I need. XML has bad schema, WebQuery tells me it can't find any data.

I tried with WinHTTPRequest, but Excel gives me back error "undefined user type" in other words it doesn't recognize that object.

Basically I want the source of web page to become a string in my VBA code. In other words that would be replication of funcionallity of

View 15 Replies View Related

Copy Web Text / Source?

Oct 29, 2009

The problem is, these identifiers are in no discernable or predictable pattern. I cannot open the page directly in excel, nor can I use the Import Data from Web function (2007) ... results are simply a blank page.

What I thought I could do, then, is automate the procedure that obtains the source code, which I can parse and look for the current date. Once I have the line with the current date, I can extract the unique identifier, then paste it back into a string and resubmit to the browser.

I just can't figure out how to get to the source code... anybody out there have a way to get to it? Since this is going to ultimately be distributed to 20 or so analysts in different countries, I don't think I can use other tools (like the HTML Extractor from Iconico).

View 2 Replies View Related

Adding The Source Cell #

Dec 28, 2009

I need to add what specfic cell was modified on the sheet tab "data" or it could refer to the reference in column A for the specific row within the sheet "data"

View 10 Replies View Related

Sum Numbers Depending On Their Source

Mar 25, 2009

I need to sum numbers depending on their source. Consider the following sources


Source .....................#
M D11 24 ......... 5
M D11 23 ......... 6
M D02 25 ........ 7

I want sum of all numbers whose source has D11 in its name (Answer 11). What's the best way of doing so?

View 5 Replies View Related

Cannot Set Control Source Property

Aug 31, 2007

I am trying to set a controlsource property so that when a cell on a worksheeet is changed, the label will also change. However, when I try to set the controlsource I get an error that says :

Could not set the ControlSource property. Invalid property value.

I've tried it with a label in the code,

lblUser.ControlSource = Worksheets("User List").Range("C1")

and I've tried it in the properties window of a textbox, and I cannot get either to work. I've also unprotected the sheet first, thinking that might have something to do with it.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved