a while back I posted Central Glossary to feed multiple documents to put a glossary at the end of a Word doc. Fabulous help and got it all sorted. Now I'm trying to do it at the end of an Excel w/b.
View 4 RepliesI have set an excel spreadsheet set up to update live data from a URL every 5 minutes. However I am wanting to do try and save a bit of time if possible.
Basically I am monitoring horses bought on a game website. Each horse has the same URL except for the number at the end which goes up in 1 increments. The URL is already set up and when the horse is bought it changes from being blank to having the horses details etc.
At the minute I am able to autofill cells with each uinique URL using fill, then series menu. This is so I dont have to manually enter each URL, as hundreds may be bought each day so it would take too long to do this.
My question is this, am i able to have a automatic series linked to the 'data from web' function in data tab, instead of having to manually adjusting each URL for each individual horse.
I am processing files with terms in excel and I need an easy way to manipulate them.
FIRST CASE In the first case, there are simply two columns.
In the file test-dic-edit.xls you see in Sheet 1 what the file is like at the first stage. After that is should become like Sheet 2 (left column duplicates broken in new lines) and then right column duplicates merge like Sheet 3.
A new sheet is created with the merging results.
So, if the macro does not find any duplicates in left column, it moves to stage 2, merging duplicates in right column.
SECOND CASE In the second case, there are 3 columns (term in language 1, term in language 2 and category). Sheet 1 as it is originally and Sheet 2 as it should become.
A new sheet is created with the merging results.
Some notes: No tab characters exist in the text. The delimiter used for multiple translations (within a cell) is [space];;[space] The actual terms might contain punctuation like fullstop and comma and their length might be up to 200 characters or more (i.e. one phrase in language 1 translated in language 2). The files might be 50,000+ rows big.
I am processing files with terms in excel and I need an easy way to manipulate them.
FIRST CASE
In the first case, there are simply two columns.
In the file test-dic-edit.xls you see in Sheet 1 what the file is like at the first stage. After that is should become like Sheet 2 (left column duplicates broken in new lines) and then right column duplicates merge like Sheet 3.
A new sheet is created with the merging results.
So, if the macro does not find any duplicates in left column, it moves to stage 2, merging duplicates in right column.
SECOND CASE
In the second case, there are 3 columns (term in language 1, term in language 2 and category). Sheet 1 as it is originally and Sheet 2 as it should become.
Is it possible to have the formula written in one remote cell, so that any modification done in this formula is reflected on all places where its refereced to?
z26 : Formula A
A1 : Formula A
A2 : Formula A
A5 : Formula A
A6 : Formula A
A7 : Formula A
A9 : Formula A
A11 : Formula A
in this example i store the formula in z26 and variety of cells in A are using it (not all of them)Suppose if i change anything in z26. I want it reflected in all the cells that are using this formula?
I have thousands of Excel files, each with a generic names (i.e. 2009092812163503.xls)
Each of them contain a header with contains column titles like Company Name, Executive Contact, Address, etc and then a single row of data for a single company.
I want to be able to task excel to extract all the data in the second line and enter them all into a single spreadsheet for further work.
We're talking 30,000+ unique files here, what would be the best way to approach this?
I have attached a spreadsheet with a small indicative data set to assist in understanding. I am trying to count the number of documents each individual has assigned to them that are not yet 'completed' (ie REGISTERED, IN WORK, REVIEWED). The problem I am trying to overcome is that the document state can be 1 of several values indicated in the same column.
I have tried using this SUMPRODUCT formula:
=SUMPRODUCT((($E$2:$E$11="REGISTERED")+($E$2:$E$11="IN WORK")+($E$2:$E$11="REVIEWED")*($B$2:$B$11="Jones")))
but it is generating incorrect values!
Specifically:
- Jones shoulld return 1
- Franks should return 3
- Smith shoudl return 0
I'm trying to create a macro that will save backup copies of documents that have changed when a user closes them. I created an add-in and put a Workbook_BeforeClose sub in "ThisWorkbook" that saves a copy of the document in a temp location on the hard drive.
If I have three Excel documents opened and each one has changes and I close Excel (with the "big red X") it only seems to call the Workbook_BeforeClose once backing up one of the files. If I use the "Close Window" (the "little X") it doesn't call that event at all.
Is there a better event to use or a better way to backup these files when closing them or Excel?
At my work, we receive manufacturing plant reviews periodically from each plant. Information on the plant's completed tasks and dates of completion are then updated to a central summary document for all plants. These updates are completed forms which each plant supervisor fills out and uploads to our network. At this time, someone manually has to shift through all these plant update forms and pull key information out so the central summary document can be presented. I want to streamline this process so there is no double handling of review information or continuous code changes.
What would be a way to have the central summary document extract date information from the most recent file in a plant's update folder (i.e. a folder contains plant update 05/02/2013, 06/20/2013, 06/25/2013, 07/2/2013, 07/22/2013, 8/05/2013, 8/13/2013 and it will only pull date information from 08/13/2013. This also means that these worksheets have yet to be completed and therefore do not exist in the update folder until the plants post them.
Have a dde feed coming in from an external source. I also have a VBA written below to play sound whenever the feed goes below a certain level. When I key in values manually through the function bar the sounds play perfectly. But for some reason it is not reading the DDE feeds as value and it can go past my level without playing the sound.
Code:
Option Explicit
Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long
[Code]....
I have a cell $A$1 that contains a value, CLIENT or INTERNAL
I also have 2 named ranges, CLIENT or INTERNAL
I would like it (using Data Validation) so that the named range being selected for the list dropdown in cell $C$1 is the value of the cell CLIENT or INTERNAL cell $A$1. Tried indirect but not working with named ranges?
I want the following macro to be used as an excel addin, how to create addin and feed this macro on that particular addin so that its available each time I open a new workbook.
The macro is : [Code] ...........
I am not good at excel. I would like to split buy and sell orders from the live feed and updating when new feeds come in.
LIVE FEED
BUY/SELLQTYPRICE BUY QTY PRICESELL QTY PRICE
B 4150
S 6200
S 4300 CHANGE TO
B 3350 B 4 150
B 2180 B 3 350 S 6 200
S 2220 B 2 180 S 4 300
B 1120 B 1 120 S 2 220
I want to auto update ( refresh ) an external feed containing odds from Pinnacle Sports website, at 30-60 seconds intervals. Here is the link : [URL] .....
I would also want to apply some formulas to other columns in excel, but mainly I would want to know where is the change in odds. For example if Bayern Munchen has the odds 2, and after the refresh the odds dropped at 1.9 I would want to see the difference in another cell.
Is there any method in excel that can pay the way for drawing figures according to data feeded.
for Ex.
Say
Radius of circle=x
Now this x can be used for drawing the circle and the radius can be entered by user.
I new to excel and vba , and i have live stock feed data in excel.
The live feed has 17 rows..of ticker names and more column about values.live feed starts at 9.00 to 3.00
I want only 13th column data to save for every 5 min..in new spreedsheet.
I am also including a sample : live crude.xls‎
I've written this code:
=IF(((BP3="1")*AND(C3="Visit")),"The following code rule is being tested: "&D3&". This test is for the Family Practice specialty.","")
What I need to do is insert a carriage return/line feed right before the first & sign so that it is easier to read.
Call center stats: I have a SQL database which is LIVE. I would like to create a live report on excel from this database. (update my excel table automatically)
View 6 Replies View RelatedEach cell in Range("A1:A2000") contains a remark, each phrase or remark is
Between 5 & 70 characters all written without line feeds (carriage return,i mean Alt Enter) Just spaces between words. What I would like to do in every cell is to force a line feed (Alt Enter) every 10 characters, and if the 10th character happens to be in the middle of a word I want the line feed to be inserted at the end of this word. Note that the phrases are not necessarily multiples of 10.
I thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...
How to modify the code a bit...
VB:
Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown
I tried to change the code, but it did not worked.
I am using the following code to copy the contents of a DDE feed.
Code:
Private Sub Worksheet_Calculate()
Worksheets("Sheet2").Range("A1").EntireRow.Insert Shift:=xlDown
Worksheets("Sheet1").Range("A3:F3").Copy
Worksheets("Sheet2").Range("A1:F1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
What I want to do is only copy the row when the columns contain data, as it stands at the moment it's copying blank rows to sheet 2. Is there a way that I can do this?
I would like to use Excel as a measurement device. I have an inexpensive digital microscope that will store sequentially numbered pictures at the click of a button. I am then going to import the most recent picture into Excel and place a grid of transparent rectangles over the picture to serve as my ruler. Would it be possible to continuous update the Excel workbook with the output from the digital microscope?
View 7 Replies View RelatedIf I write a multi-line text in a cell (then go down with alt+enter) and after copy the cell pasting on Notepad, it display before inverted commas and after textual content.
Instead, if I select directly the content from the formula bar, it isn't happen.
Do exist a way for copy and paste directly from the cell without select from the formula bar?
Maybe with a macro?
I want to set up daily charts to monitor various things but Im not sure how best to set up the data in order to do this so it calculates automatically as data is refreshed.
I want to run daily activity data that tells me how many people attend each room per day. the aggregated data would include:
Number of attendances, by room (there are 11 rooms), by day
Each attendance grouped by category of patient (up to 25 categories)
Each attendance by type of attendance (various groups)
How should I set up the data to show these things in a way that it can be linked to a text file and refreshed daily on opening? the data at the moment is in rows per attendance rather than grouped in any way.
I would like the data to have dates and months etc automatically genetared by the attendance date in order to graph the above indicators, as well as a % atttendance in relation to capacity per room etc
simple excel template that I could try to manipulate my data into so I can get started. The data is generates using a transact SQL query and saved as a text file which I wabt to use for the daily stats.
It is my general understanding that the change event system within Excel vba is fairly particular as to what will fit the mold of a qualifying change event.
For example, changes that the user imparts to the worksheet and other written code are legitament candidates for change events. However copying down data and cells changing their values indirectly rather than directly may not be considered in the Microsoft change event design.
I would like to know 2 things:
1) Does a cell updated by a market data feed mechanism qualify for a change event?
2) Any list of qualifying change event types. It seems that Microsoft does not have this information.
This code will take the value of the active cell (which is a filename) and open the corresponding PDF document of the same name.
Sub OpenPDF()
Dim fName As String
Dim fExt As String
Dim fPath As String
Dim fFullPath As String
fName = ActiveCell.Value
fPath = "M:Books"
fExt = ".pdf"
fFullPath = fPath & fName & fExt
ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True............
I receive a Weekly list; let’s call it “Doc1” with a load of order numbers in column A. I then have another excel document, lets call it “Doc2” also with order numbers in column B.
Is there a way to see if an order number in Doc1 already exists in Doc2; perhaps change the font colour to red for example (in Doc1 only)? I don’t want to affect Doc2 in any way I am not allowed to alter it at all.
I have several excel documents all linked to one unique excel document which I will call document A.
Document A is a performance spreadsheet where I have percentages, sales figures and times, for my sales team.
Each sales person has their own unique dashboard (another excel document) that I am currently trying to finalize.
Their personal dashboards have pie charts with text boxes. The text boxes within the charts link up to their relevant cells in document A.
When Document A is open and I open 1 of the sales person's dashboards. Everything looks good and works, however.
When I close document A the dashboard information changes. For example, the dashboard information could be showing 25% but when I close document A it reverts to 0.25
If I close down the dashboard and open document A back up, change it to 30% then close it. The dashboard recognizes the change BUT reads as 0.30. This is the same with them all.
The only fix I have figured out is creating another tab in Document A then duplicate of all the information required for the dashboards then save the cells as TEXT (as opposed to currency, percentage etc).
This kind of defeats the object because I have formulas set up in Document A and don't want to be doing twice the work.
I have a file containing thousands of hyperlinks to respective image files. How I create a macro to save them with respective document names as shown in individual cells ?
View 4 Replies View RelatedI am having problems opening Excel documents on first try. If I double click a excel doc icon it will start excel, hang for about a minute and a half then just display a excel blank doc. If I then go back to the original doc icon with excel still open and double click it again the document appears.
View 9 Replies View Related