I have a form that laods when the workbook is opened. I also have an MS query that retrieves data from another source. When i open the workbook without the form loading i get a message asking if i want to update the query automatically. When i open the workbook with the form opening the message doesnt show until i close the form meaning i have no data in the sheet to use. Is there a way to get the query to refresh before the form opens?
View 2 RepliesLet's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B
Yes 12
12 13
No 10
13
No
10
Yes
And if I want to add in column A:
A B
Yes 12
12 13
No 10
13 25
No 15
10
Yes
25
15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
This is to run stored procedures (in the SQL Database), and pass variables. But...once the query is setup...all I really want to do is change the variable...and "fix" the sql query with the new variable value.
Is there a better way than: ....
I'm trying to update a query table in excel, but it keeps giving me the error 1004
Worksheets("Data-All").Range("A4").QueryTable.Refresh BackgroundQuery:=False
Cell a4 is in my query table.
Im using a spreadsheet with several third-party add-in queries using Showcase Strategy 4.5. The trouble I'm having is in writing a macro that will automatically refresh the queries. Currently I am using the line
application.run ("refreshdata")
however this refreshes all the queries and at several points I only need to refresh one or two. If anyone has any suggestions then I'd be very gratefull.
Also I feel the need to confess that I pilfered the above code from another forum and so I dont actually know how it works. VBA help doesnt seem to have anything relevant to say and I'd feel better knowing what the code I'm using actually does.
I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
I've got a web query setup to poll a table detailing traffic information from my state's DOT page.
I have the web query set to update every 5 min.
I would like to save a particular cell from this query every 5 min and place a timestamp next to it.
Ultimately, I would like to build a table, one column = time, the other column = traffic data and have each row be 5 min apart.
I have a basic formula =C17+'Asset Depreciation 2008 Onwards'!C24, and I want to copy it down just using the drag function. Problem is that the second reference range of cells are in rows and hence when I copy it down it doesn’t automatically update the cell references because it want to update them by column number instead of row number. IE I want it to display =C17+'Asset Depreciation 2008 Onwards'!
D24, instead of C25. Do you know if there is any way of telling Excel that I want it to increase the column number by 1 every time, instead of the row number for this part of the formula?
I am bringing data in from an ODBC database, I am using ActiveSheet.QueryTables(1) and then .refresh.
I want to run a sub after the data has been brought in which finds the last row and adds a formula.
The problem I have is that the data brought in is not show on the screen until all of the macros are finished.
Is there a way of forcing the screen to update?, I have tried the recalculate and refreshall commands or is there another method I should be using?
Sub autofill()
Dim myobj As Object
If ActiveSheet.QueryTables.Count > 0 Then
Call clearscreen
Application.DisplayAlerts = False
Set myobj = ActiveSheet.QueryTables(1)
I require the data to be on the screen before I can find the last row used which is done in "addmaths"?
i have some code (see below). at the part where it says
If ActiveCell. Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
i want to make this smaller - i have to write this line bout 60 times 3 times over (incresing the numbers as i go ((0, 9..10..11 etc etc and (Skill_32..33..34 etc etc) )) to give you an idea of what im trying to accomplish - im opening and writing to a database but if the cell doesnt contain any info dont update.
Function EditCPS(ByVal StaffNumber As Long)
On Error Goto Err_Handler
Dim SkillsBuilderDB As Database
Dim RSSkillsBuilder As Recordset
Dim MySQL As String
Set SkillsBuilderDB = OpenDatabase(Worksheets("Adding Data").Range("IV1")) ' database location
MySQL = "select * from CPS where StaffNumber=" & StaffNumber
Set RSSkillsBuilder = SkillsBuilderDB.OpenRecordset(MySQL)
With RSSkillsBuilder
.Edit
If ActiveCell.Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
Else............................................
I want whenever i select any dept in the B column say ID or Design... the value into the D column should change automatically...
say if i select in B5 as ID then D5 value should show me India
******** ******************** ************************************************************************>Microsoft Excel - resource_sheet_11_Dec_07_V2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Project(Client - Name- Length)Dept.Resource 1-Oct2 3Template 4 5New Course Sample Template ID 6New Course Sample Template ID 7New Course Sample Template DesignDesign Pool 8New Course Sample Template BuildBuild Pool Project Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
We have a worksheet that contains twelve different charts. Once a month, we load data into anotheer worksheet and run a macro and it produces the twelve charts. The problem is that we have to manually update/change the month name in each chart title every time we run the macro to produce the charts. Is there a way to automatically update the month name so that we do not have to manually do it? For example, the current chart title is "xxxxxx - March 2014"....when we run the charts for April, we have to manually change March to April in each of the charts. Can this be done automatically?
View 2 Replies View RelatedThis what my excel sheet looks like that i am using the date ant time cell is set by using the now formula to get the current date and time, but I only want the date tiem change for each row when it changes.
For example when the data for the first row changes all the dates and time change, but I only want the effected rows time to change not all the times and dates, this progam is set up to monitor inventory and when some makes a change to the inventory I need to now when the that data was recorded but do not want the users to have to enter the data and want it to be enter automatically that is why i am using the now function. The data may not change at all for a couple days that why I need to be able to keep the Date data from changing automatically.
Bin #ProductAmount
D-21Red Lentils 3/47/7/09 1:54 PM
D-22Red Lentils 1/27/7/09 1:54 PM
D-23Yellow Peas 3/47/7/09 1:54 PM
D-24Yellow Peas 3/47/7/09 1:54 PM
D-25Yellow Peas 3/47/7/09 1:54 PM
D-26Yellow Peas 3/47/7/09 1:54 PM
D-27Yellow Peas 3/47/7/09 1:54 PM
D-28Yellow Peas 3/47/7/09 1:54 PM
D-29Yellow Peas 3/47/7/09 1:54 PM
D-30Yellow Peas 3/47/7/09 1:54 PM
I have a worksheet with a cell that references another cell.
Cell A1 references cell B1, and the value of cell A1 is "=B1"
When I type anything in B1, A1 won't automatically update to reflect what I've typed. The only way I can get it to update on the fly is to save the workbook. Even when I close the workbook and reopen it, it still won't update to what I've typed in B1. Is there a setting that keeps it from autoupdating?
I require a total amount of 'cost price' items we have in stock.
As you can see from sheet 1, 'Adidas', the total is £43.00. However that is not the cost of stock, as some items are not in stock, as per Column I. (The cost is actually £36.00).
How can I make a total to include just items in stock? Bear in mind new stock is inserted between others in order of Model so the forumale would need to update automatically, and there are several hundred sheets, not just 2.
I have a dashboard (CW Dashboard.xlsx) that is linked to 3 main spreadsheets. In each main spreadsheet, there are 5 tabs. One tab contains raw data, the other 4 tabs contain tables: 2 tabs with formulas based on the raw data tab, and 2 tabs linked to 20 other spreadsheets (for now). I separated the raw data into 3 spreadsheets because Excel was calculating way slower than my patience would tolerate. All spreadsheets are Excel 2010. All spreadsheets reside on our network drive.
The links update as expected on the 2 tabs that are linked to the 20 different spreadsheets in each of the three main spreadsheets.The CW Dashboard does not update. I get #REF! in each cell (although when I click in the cell, the link shows no error). I am using VLOOKUP and HLOOKUP in this spreadsheet. I can of course open the source documents from within the Dashboard and the links then work as expected. However, I would prefer the eventual users of the Dashboard not have to open 4 s'sheets.
I have attached a sample of our worksheet (GTS807) used to create quotes and generate jobsheets etc once they are orders. To ensure we have enough of a size in stock I want to carry out a check against the stocksheet (stock) which I have no problem but I cant get it to automatically update the stock once a quote becomes an order and removes the quantity from stock!
As you see the balance of stock in shown in AA13 for "115270" but is there a code to find "115270" on the stock sheet and copy the new balance back there?
I have an excel based appliction that I distribute to about 150 people in the US, London, Hong Kong and Singapore. Distribution is currently done by email containing a link to a Sharepoint site (the email has the link, release notes, etc). The end users open the file and save it to their desktops, overwritting the previous version. I'm finding that many users are not updating their versions as they should be.
So what I'm wondering is if there's a way to have this process automated - so that when the user opens the file, it checks a repository (sharepoint or otherwise) for a more current version, then prompts the user to download it.
In my worksheet I have a data validation dropdown which changes the value in a cell. I then use VLOOKUP to find values in Personal.xls (open) based upon that cell. It works great, BUT if I make any changes to the data in Personal.xls it doesn't get updated in the other file. I've tried saviing, closing, opening etc but it never updates since I first did it.
View 7 Replies View RelatedI have a working VLOOKUP formula for generating a one page inventory list. It grabs values from worksheets in other spreadsheets. The only problem is it doesn't automatically update.
View 6 Replies View RelatedIs there a way to automatically update football stats from a website. I want to update many different stats each week of player and team performances. Can I specify a website to go to update the info and prefer to do it each Tuesday. Let me know if more details are needed.
View 1 Replies View RelatedI have three columns as below,
Column A current Amount
Column B Increse %
Column C Revise Amount
If I put revise amount in column c, than Column B update automatically or if I put % in column B than column C update automatically, update must be live
I am trying to create a simple scoring system for sports that use a "legs" and "sets" format (e.g. tennis, darts etc.)
I would like the "sets" cell to automatically update as the "legs" cell reaches the required amount of legs to win a set. After that I would like the "sets" cell to continue to count upwards when/if another set is won. Is this possible?
I have a cell (H2) that contains the formula =NOW(). I would like this cell's time to be updated every minute automatically, with a Macro that will start itself the moment the spreadsheet is opened. I have attempted to find other users with the same question and have attempted to use Macro's supplied however without success. I have previously found this piece of ...
View 5 Replies View RelatedI'm trying to update an excel document which has a list of employees on it along with their training. Most of the training for individuals needs refreshed in the next year so wanted the table to use the date and perhaps trigger a fill colour change once they are close to needing that training. here's an example
John Smith
course 1
Completed 01/05/10
Refresher required 01/05/14
Once we get a month or two before the required date, I would like the fill colour to change to highlight that they refresher training should be organised soon.
Lets say I have two sheets titled "dashboard" and "raw data" in a workbook. Cell A1 in "dashboard" should always show the most up to date data from Row 1 in "Raw Data".
I update "raw data" daily, adding a new column to Row 1. For example December 19 would be A1, December 20 would be B1, December 21 would be C1 etc...How can cell A1 in "Dashboard" always reference the newest cell in Row 1 of "raw data"?
I have made a league table which pulls data from another table - in this example. The actual one i am using pulls data from a different workbook. Any way...In one of my columns i am using the countif formula to count how may times an advisor's name appears. Eg - =COUNTIF(A:A,"Sally A").
Is there another formula i can use to carry out this function. The reason i am asking this is there are multiple links in my league table to the other workbook. Once i open my league table all the data is updated - as the other workbook data is changed. However, for this to work i need to open up the other workbook where the data is , otherwise i will get the VALUE# error. I do not want to open the other workbook just to pull the data over, i need this league to automatically update once opened.
I want just the year to update because I look for information in other cells depending on the year. Also look for data based on just the month.
View 10 Replies View RelatedIs is possible to have Excel update time automatically in a given cell within the spreadheet.
I have a spreadsheet that I need Excel to update the time automatically .
I would like Excel to update the time automatically in cell A1.
I have done some research and found the following thread but this only works when we close and open the spreadsheet.....
I have a spreadsheet reports on tasks that is updated daily.
At the end of the columns is a due date column and a status colour column where 4 options are possible:
Red = out of target, yellow = on target, brown = still on target but at risk, and green = on target.
I have been asked to see if there is some way of making the status colour column automated so that as task pass their due date and are not complete or are complete, the status field changes automatically.
I have added the feature in =today() in cell A1 and another column next to due date date completed. I think these will help but not sure how to get the status colum to update automatically. I've tried using the IF function but that only returns text...