Running XL07. Need to have one workbook pull data from several dozen others.
Have columns to the right that refer to the query table.
As I add in queries to other workbooks, the time to update each individual query goes up a lot; it feels as if the update time is increasing geometrically. I'm giving up after 2 hours, for query updates that used to take
I need grouping and then summing in either msquery or back in excel after the data has been extracted from msquery.
I have external data that i have interogated in msquery, and I would like to to sum the amount column if possible.
I have two fields, first field = account number
2nd field = amount due
the first field could contain 5 sets of the same account number, but with different amounts in the Amount Due column. I would like to sum the amounts due for each of the account numbers in one column.
If i cannot do this in msquery, how do i then go about doing it back in excel. I know i can use subtotals, but then when the data updates from the external data source.
[url]
In the above, i am trying to calculate the time taken between queries. I do not want any times/queries that are from someone else on the network to be counted.
I need something to ignore the blank cells, and take data from the next cell above which has data. I3, I8 and I14 are currently the trouble spots, and should be displaying 00:00:00, 00:04:00 and 00:06:00 respectively.
Note that someone from the network could have literally thousands in a row before I perform another query, not just alternating like the screenshot.
B is the real life time the query was performed. F is how long the query took. F does not take in to account the seconds between queries, which build up, so i am using B.
I have a special use PC which will power a projector 24/7. When it reboots I want it to load Excel, open a spreadsheet, and then connect to the web to get data using web queries.
My problem is that I get a dialogue box each time the spreadsheet is opened: "Enable Automatic Refresh." I need to remotely reboot the PC from time-to-time so I can't click the button.
I have set my security levels to low and that does not help.
I have 2 drop down menus based on car make and model.
The first contains Make and the second contains the models of that make using the indirect function (All through data validation (this may be my problem))
Now what I'm wanting is that when the Make changes, the Model will automatically changes to either the top (or any really) model from the list for that make or to change to blank. So can this be done without VBA?
It's some code I found and altered that automatically updates the cell to the right of a column that has drop down box data validation, creating a list of the selected values seperated by commas. The thing is, I only want it to do this in columns 8 and 10 but data validation drop-down boxes have been used in several other columns. I've tried various methods of limiting the scope, the latest being the line: If Target.Column = 8 Or 10 Then. But the auto update is still being applied to all drop down boxes in the sheet. So, how can I make sure the auto update only happens when the drop down boxes in columns 8 and 10, updating into columns 9 and 11.
View 4 Replies View RelatedEverytime that I insert a row into a worksheet which a number of formulae refer to, those formulae get updated automatically in such a way that it is not in my favour.
The formulae refer to rows 2:2000 on a number of different columns. Adding a new row shifts the references to 3:2001. I presume that I can stop this from happening
Example:
= SUMPRODUCT(('List of DD Donors'!E2:E2000)*('List of DD Donors'!F2:F2000='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")))
changes to...
=SUMPRODUCT(('List of DD Donors'!E3:E2001)*('List of DD Donors'!F3:F2001='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")))
I have to open some woorkbooks via VBA disabling macros automatically (the reason of this is very hard to explain, my boss wants it in this way)
Does any parameter exist to add it to the woorkbook.open function? or any trick to do this?
What I am wanting to do is have excel insert a static date automatically. I know ctrl + : inserts date and ctrl shft + ; inserts time. I want excel to do this automatically. The now() and today() functions auto update the date. I dont want the date auto updated when the file is reopened after it is saved. The file is a template so I guess what I am really wanting is a automatic date/time stamp when the user saves the file so when it is emailed and reopened the date does not auto update.
View 6 Replies View RelatedSo, I work in a large indoor grow facility, and as such, we need to keep an active, updated calendar of all activities that will need to happen in the grow. If something happens to a set of plants, we need to change to date of transplant into the next pot size and every transplant after that, as well as moves into the flowering room from the vegetative room.
I have tried to manually make a calendar (I'm not worried about auto-generating a calendar or auto-filling dates, only things that happen on those days.) and tie in this information, but I'm having issues automatically searching a column and finding the date, and then pulling more information from that row automatically. (i.e. If anything in Column E has the date 7/3/14, then fill the data from Column C and Column B on the same row.)
I'm attaching a copy of the spreadsheet that I am trying to pull the Data from, and a copy of the Calendar I mocked up.
FYI, the spreadsheet I am pulling from Automatically fills once you put in the Clone Date, and then if you add in an adjusted date, it will adjust all later dates from that same section.
I tried to find something other than Excel as well to do this for me, but AFAIK nothing exists to make the calendar update Live, only to reproduce a new calendar with the current spreadsheet. This is not an option, I need it to stay up to date.
I have fought with this for 2 days and I am trying to have a worksheet cell update the date and time if the cell it is referencing (B3 see below)
Here is the formula I am using however it updates every time a new row is added to the worksheet. So the Date/Time stamp on each row is always updated to NOW() everytime.
=IF(ISNA(B3),"0/0/00 00:00",NOW())
I would like to have a Date/Time stamp stay as is once that row has been added. Is there anyway to stop the field from updating once the date and time have been set?
It seemed to be working OK for the first hour I had my sheet open. I then noticed that a variation of the following Sub would not update unless I clicked somewhere else in the sheet or edited another non-included cell. It's like something is holding up Excel from running this function and updating the cells if b2 is greater than a3 then a3 will not update to = b2 immediately.
Private Sub Worksheet_Change(ByVal Target As Range)
If [b2] > [a3] Then [a3] = [b2]
End Sub
My data in column B is formula derived and that formula contains data that is live updated data brought in from DDE link.
My question is, should I setup this formula as part of a macro, instead of workbook code and attach an Application.Ontime function to ensure reliable execution? Could too many IF statements cause delays (about 100 of them currently in the code Private Sub Worksheet_Change(ByVal Target As Range))?
if it is possible te refer to a cell in another worksheet.
I have 10 worksheets in sheet one I put in cel A4 a digit en sheet 2 I put the formula that refers to sheet 1 cel A4.
Now I want a formula that refers to sheet -1 so in sheet 3 the formula automatically refers to sheet 2 cel A4 and in sheet 4 the formula automatically refers to sheet 3 cel A4 without corrections to the formula.
I got a workbook that is linked to many external workbooks and it is very slow to calculate, so I work it with manual calculation and only calculate the sheet (SHIFT+F9) when necessary. To work with this workbook I need to be able to make faster manual calculations. I have tried many solutions and the only one that seems to work, is to work without the links to the other files (by breaking the links and turning the formulas to values). The problem with this solution is that I can't lose the formulas. I think that must be a way to calculate the sheet without calculating the cells that got a reference to the external files. Only the formulas in the current sheet.
View 3 Replies View RelatedI have the same question as described in Graphs Not Updating When Source Data Updated; however, the poster did not reply to the request-for-clarification questions.
My chart does not always update when my source data changes. My Calculations setting is Automatic (not Manual). There is no macro code working to produce the source data or the chart. My chart is on the same worksheet as my source data. The source data is simple links to other cells in the same workbook that update as the underlying data is updated. Saving doesn't work. Manually calculating (F9) doesn't work. The only way I have found to get the correct data to display in the charts is to open the Source Data and re-enter the Series range. Obviously, that gets old.
Have been trying to set a chart here to autoupdate which normally is fine to do. In this case however there are other columns in the data table that run down to Dec 2014. Even if i define my needed range, the graph will show the full timeframe (with lots of unneccessary space).
I've attached an example. Rate % is the column that will be updated monthly and I would like the graph to only increment along when a new figue is entered here. I've defined this range as 'Rate' within the sheet.
I have the following macro which runs vlookups between two sheets in excel. Whenever i add columns to the range the vlookup column reference is not the correct cell. Is their any way I can adjust the macro so that the column number adjusts in the formula when a new column is added??
Sub template()
Range("C7").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE)="""", """",VLOOKUP(C6,'Project master'!B7:BG150,3, FALSE))"
'category
Range("c9").Select
ActiveCell.Formula = "=if(VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE)= """", """",VLOOKUP(C6,'Project master'!B7:BG150,4, FALSE))"
'sub-category
Range("c10").Select...................
I need it to be like:
=B10 2014
(So the first part changes depending on what B10 is but the 2014 always stays)?
I'm trying to create an autoupdating price list using a part number and a website. I've tried Excel's data import wizard.
Website: WebFLIS - Public Search
Sample data Category
Chaplain Kits
Item
Chaplain's Kit, Consumable
NIIN
9925-01-326-2855
Price
$276.94
Description
Also called the ReSuppply Kit
VBA Code that allows me to automatically open the page.
Dim IE As Object
Sub submitFeedback3()
Application.ScreenUpdating = False
Set IE = CreateObject("InternetExplorer.Application")
[Code] ......
Ideally, I'd like to click a button that says "Update Prices" and it will search WebFlis for the NIIN listed and update the price. I have 717 items on my list so updating would be by click only (I think I can write that portion).
If that is not an option, I'd like to be able to click on each item row (think hyperlink) and be able to see the results for that individual item.
Is it possible to create a new field in MS Query which uses an IF / IIF statement to determine it's value?
I am querying an Excel file/table, and have a field called 'Mo Nm' (month name) which stores the name of the month of a sales record, with the sales value stored in 'Sale Value'.
I'm trying to create 12 new fields in my query output to report the total sales by customer by month in a single record, rather than 1 record per month, and having to then do a Pivot Table on the result.
MSQuery is using a table called Data$ (the XL file has a sheet called 'Data', which stores the XL list being queried).
Whenever she clicks on a cell, the cell to the right of it is also selected. When she tries using tab to move to a new cell, she can only move between the two selected cells. Same with using the enter key. As such, it is extremely difficult for her to modify only one cell, since she always has two selected.
The F8 key, as well as Ctrl+F8. However, pressing the F8 key only adds more cells to the autoselection, and Ctrl+F8 allows her to select one cell, but also highlights the cells around it, and when we tried to select other cells, every cell we clicked stayed highlighted.
Additionally, sometimes when she clicks a cell, it will just select that one cell. Click it again, and the problem is back. I haven't been able to determine any patterns to this behavior, and I know there is no problem with the input (the keyboard and mouse are standard-issue in our lab, and we keep them well maintained).
I have a spreadsheet that queries another spreadsheet, using MSQuery. The query is of a named range on another workbook. What I am trying to do is change the workbook reference, to another similar workbook, with the same range name. MSQuery is not easily letting me do this. I figured there was a way to simply change the file that the created table (in MSQuery) is using. There doesn't seem to be a way to do this.
The named range is "data." This is what the SQL view looks like: ...
Here is part of the vba code for a query
"(Mr_Scrap.PART_NO='" & partno & "') AND (Mr_Scrap. DATE>={d '2006-12-25'}) AND (Mr_Scrap.DATE<={d '2007-07-04'})")
I would like to have the dates be from cells A2 and B2, but this does not work.
startdate = range("A2")
stopdate = range("B2")
"(Mr_Scrap.PART_NO='" & partno & "') AND (Mr_Scrap.DATE>=" & startdate & " ) AND (Mr_Scrap.DATE<=" & stopdate & " )")
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
I'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Work-related), so auto-calculate is casuing crashing, so what I've done with this sheet is disabled auto-calculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess).
My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?
I import a file and paste as CSV. I believe everything is considered text
then, but there are numbers as well. The worksheet has over 4000 lines. I
try to concantenate two cells (ie. a2 and b2). I use the function, but the
result shows the formula. What I want is the new value or the string of
words. I have checked my options and the auto calculate is turned on. I
even try the various F9 keys.
Every time i open my workbook it recalculates and takes ages
is there anything i can press to halt the recalculations so i can go in and change the formulas that are making it take so long.
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days.
Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month?
IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29".
The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).
What line of code would I need to used to turn off the auto-calc upon opening a workbook?
I have an ODBC connection to a db2 database in MSQuery. I want to return the length of a string. I've tried Len(string) but it says its not in the library.
View 2 Replies View Related