I have a workbook that need to call for external data (this is information from January till May) from another workbook to manage it. I get this external data as a Tabla_A that needs to be updated continually because the extenal workbooks is adding information constantly. However, now i have to incorporate a specific range of old information (information of December, 2013) before the external data in the same Table_A. This last information wont ever change and needs to be incorporated to analize de information from December till Now.
The problem is when I insert the December information as new arrows at the beginning of Table_A, later I update the table and this old data is remplaced automatic by the external Data range. For that reason, I'm wondering to know if there is some way to have one Table_A with a static range of arrows (december) and another range of arrows (January onwards) that is updatable.
I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).
In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.
BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.
I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.
Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.
One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?
An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
I have a External Data Table, but I wanted to add a extra column to it for comments. I just need to be able to write down what I did with that particular order or things like that. I did make a column, but the problem is whenever I update the table the comments column fields stay static I mean they don't move with their row. So it ends up the comment belonging to a row is now on another row.
is there a way I can tell excel to move the cell with the record or something like that.
I have a table made up of 4 columns and variable number of rows. Column A has 8-digit number; B has 8-digit number; C has text up to 40 chars; D has a number with two decimal points. I would like to generate or replace a text file in csv format containing each row's data EXCEPT where column D value (number with 2-decimal points) is 0.00. Using Excel 2003.
I have a pivot table report connected to an external data source (OLAP cube).I want to automatically refresh the pivot table report everytime the OLAP cube data changes.
I know we can use Automatic refresh on open of workbook or time intervals after which to refresh .But i want the auto refresh to work even if the workbook is already open and there is no definite time interval after which the cube is likely to change.SO,by defining intervals for auto refresh i do not intend to waste time refreshing even if there is no change.
There are multiple pivot table reports and pivot charts connected to the same OLAP cube.
I am tracking business hours of various business locations. I would like to be able to enter the businesses operating hours on the "overview sheet" and, based on those hours, have a table generated on another sheet that "autofills" based on the date entered on the "overview sheet". For example:
I would enter the operating hours of the business on the "overview sheet" and it would look something like this:
Day Open Close
I want a table for each day created. I would then manually enter the data for the "Ranking" column. The issue is that I have numerous location I want to do this for and all with varying hours of operation. It is very tedious to manually create the tables.
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer) Dim initialCell As Range 'specify the upper left cell Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet ActiveWorkbook.Sheets("Calculator").Activate Set RefCell = ActiveSheet.Cells(2, 3) WS.Activate Set initialCell = ActiveSheet.Cells(initialRow, 1) initialCell.offset(numRows, numCol).Select Selection.Table ColumnInput:=RefCell 'the calculation should be automatic, if not, then calculate WS.Calculate End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
I want to generate a letter to parents that shares reading fluency data from an excel spreadsheet into a table and a chart. I got the info to post into cells in the table through mail merge. (sample below) Now I want to make a matching line chart to visually show the student's growth over time. I want the data on the chart to change according to the data I put in the cells in the table through mail merge. Is this possible?The data in the cell that says "Cory" and the "88, 100, 112" are placed in this table through mail merge. I want to now be able to take just Cory's information and display it as a line graph. If I highlight those cells and choose "insert a table" it doesn't work.
Is there a way to create a pivot from multiple external worksheets. I have the same format and field names in different folders on our network. I am proabably looking at reading from 5 different files. Is it an option to write a query with UNION ALL?
I am using pivotable with access queery as the source.
When I set it I cuse that external source browse tool, but it seems to only recognize the source if the dive is the same. When I go downstairs to try on a users computer there dirive letter(K is different than my (H and it does not work.
I have a pivot table created from a cube of data not within the file I am working in. I have created a macro that will update, but only for the specified timeframe. I have a separate worksheet within my file that will allow a manger to specify what timeframe he wants to see. Can I edit a macro to read from a source that is different than where the data is being pulled from?
I would like to create one page (sort of like a form) to use to collect specific weekly payroll related data for multiple employees and be able to automatically transfer that data to its own weekly data page based on the week date, and then also automatically post specific data to each individual employees' total pages. While I am self taught and comfortable with formulas I do not yet have a lot of experience with VBA and am just now recognizing and exploring the true power of Excel so I don't always yet see the best way to accomplish my goal.
My current way of doing this is to reuse the same form page. I plug in the data for each employee that week, it calculates, I print it out, and then I manually update each employee's total page for that date. As I do this I lose the previous week's details. I would like to retain a page for each week's details in addition to having each employee's total pages. Is this possible or is there a better way to accomplish my task?
after spending hours searching the forum, I begin to seriously doubt my ability to even formulate a suitable search question. My problem SHOULD be a known one, but I am unable to find any clues. Here we go:
I have made a pretty stright-forward forecasting model. It is used to calculate economics of a coal fuelled power plant (even though that shouldn't matter). The outcome is, among other things, a single number for Profit / MWh in Euros. There are several input data, such as fuel price, price for CO2 emission rights, operational expenditure, capex, etc.
My idea, then, is to lock all in-data parameters except ONE, then let this variable vary incrementally and then produce a new small table with a) the variable data (example: price for CO2 rights ranging from 10 to 50 Euro in 2 euro steps), and b) the resulting net profit in each case. Ideally, I wish to be able to chose which indata to lock and which to vary, i.e. solution need to have certain flexibility.
NB: The calculations are not that simple, i.e. I cannot use a simple formula - I need to use the exising output cell(s) to feed data.
How do I do this?
The outcome should be basically two rows with a suitable number of columns (or the other way around, two columns in X number of rows). The table will then be used to produce illustrative graphs.
I guess I could hard code a table by simply enter data, but heck, that's not the way to use Excel!
column A = Date : 01/02, 07/02, 14/02, 21/02, 28/02 (x-axis : shows when the table is updated) column B = project: x, x, x, x, ,x (name of the project and trend line) column C = Delivery Date : 01/05/2009, 08/05/2009, 20/05/2009, 30/05/2009, 28/02/2009 (plotted on the graph) column D = Status : Green, Amber, Red, Green, Blue (status of the project. the points should be the same colour as is described in the table) column E = Comments: original, delay, supply, out of money, on track, delivered-wow! (these comments will pop up if the user holds the cursor over a point)
NB Y-axis scale : 01/01/2009 to 31/12/2009 with increments of 14 days. this will be the same scale used for all projects.
Date Project Delivery Date Status Comments 01/02 x 01/05/2009 Green original date 07/02 x 08/05/2009 Amber delay supply 14/02 x 20/05/2009 Red out of money 21/02 x 30/05/2009 Green on track 28/02 x 28/02/2009 Blue delivered-wow
So id like the macro to draw the line for project x based on the 'delivery date'. The points should be coloured according to the 'status' column and when you hover the mouse over the point the data lable will show up taking info from the 'comments' column.
Would it be possible to create a macro that will be able to generate this graph automatically. I have a few projects id like to do the same thing for.
I've read a lot of tutorials on how to use pivot tables, but found them either too basic, or too advanced, and never finding any example of what I am looking to do.
I have a database, column A holds names, and column B their phone numbers. I'd like to generate a list of unique names and their associated telephone numbers. I figure I can use a pivot table to do this.
But I do not quite understand how to format the pivot table to put the name in one column and the phone number in the next. I will use this pivot table as a vlookup range. I assume I can do this.
we have a network of small routers and wifi aps. The Ip range has got a bit out of control so I would like to be able to audit it every once and a while.
how I could go through a set of IPs on a spreadsheet, open a telnet session or whatever, try pinging each ip individually, paste a response or no response in the adjacent column then move to the next ip in the first column (some sort of loop).
Time is not too much of an issue as I could run this overnight if need be.
As a response, the macro could just copy the last line of the ping eg
Minimum = 7ms, Maximum = 9ms, Average = 7ms if it responds
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)
if it does not respond
Col1 col 2 Ip 22.214.171.124 Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)
126.96.36.199 Minimum = 7ms, Maximum = 9ms, Average = 7ms
188.8.131.52 Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)
184.108.40.206 Minimum = 3ms, Maximum = 21ms, Average = 9ms
I am trying to develop a formula for internal repayment which must fulfill following requirements(Net cash position-cash needed to start the quarter),the remaining amount goes to internal repayment but that must be <=Begining of the quarter debt owed.
I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.
I'm trying to set up a direct link to the internal excel calculator, using a button in VBA. I have the shortcut on my own toolbar, but I have many users that use the workbook, and to get them all to set up is almost impossible, so I am trying to make it simple for them.
I've tried doing it through a hyperlink direct to the calc.exe file, but get a lot of messages popping up (that'll scare a few of them). So thought I would try and find the excel name for it and use VBA, but I cannot find it anywhere.
I'm trying to make a spreadsheet that references data tables I have pulled from the internet, with the hopes that these tables will update as I refresh the data connections. I'm in the early stages of creating this spreadsheet right now, but whenever I try to refresh my data connections, it takes a tremendous amount of time. I have about 6 connections with 100 row lines total. Is this typical with data connections to the web? Is there a better way to do this?