Using Excel In SkyDrive / OneDrive As A Linked Database?
Mar 13, 2014
I need multiple users, each with their own Workbook to utilise data from a central database I made in Excel. I therefore need to put the central Excel database online and link to it. However, two issues so far:
1) I don't have an online domain or server to put it, nor the possibilities to get one quickly
2) Putting the central database into SkyDrive/OneDrive haven't been working so far, as the links are always local and not addressed on the OneDrive server domain.
Therefore my question:
Is there any way I can use the cloud power of OneDrive to host a (fairly simple) database without my computer being turned on and running the workbook?
View 3 Replies
ADVERTISEMENT
Aug 28, 2013
i have a excel file which has a formatted pivot table which displays customers by country in row A 2 down to 36 with approx 36 customers in the values field i have volumes by alarms and tickets along the column labels it shows the previous 7 days with date and then the alarms and tickets in each row. My problem is i am trying to find the code or location where i can change the only bring back 7 days data to another value. i have searched all connection properties and definitions. I know there is a value somewhere that allows you to change this number to say from 7 days to 30 days. If you click on the column labels for date it shows the dates back 3+ years but it still if i tick more than 7 days only show 7 in the table.I have image if needed
View 1 Replies
View Related
Jul 10, 2009
I have created a 'price list' database in ACCESS. Then in EXCEL I created a pivot table which retrieves data from one of the database queries (the query was saved as a .dqy file).
I emailed the file containing the pivot table to a colleague who is on the same server. He saved the excel file on he desktop & renamed it. When I update the databse file on a shared public drive on the server, he is able to 'refresh' his desktop file successfully !!
View 5 Replies
View Related
Jul 1, 2013
I am working on creating a medical master database. I have a master sheet with a huge number of columns. I have a couple of guys working with me, and they have their own sheets in the workbook.
So here is what I would like to do: anytime one of the guys enters data into his respective worksheet, I want the data to be copied over to the master sheet in a new entry.
The columns among the worksheets are different, and I would like the data to automatically sort in the appropriate column on the master sheet when it is copied.
I have attached a copy of the workbook.
I have included an example of what I would like to see. For example, if Charles puts in an entry in his worksheet, a new entry is created on the master sheet with the information from Charles' sheet sorted in the appropriate columns.
View 8 Replies
View Related
Jan 6, 2014
Bar those who don't believe/celebrate in Christmas, Hope you all had a good Christmas and New Year celebrations.
I have two sets of data, lets call them 2013 and 2014. I have a Spreadsheet already set up in excel, but currently I just past this information into a tab in excel (which isn't ideal at all), and given the data is growing, is slowing down my excel spreadsheet a lot.
I have some experience with Access, SQL and queries.
What I have done thus far is to link my Access DB with Excel, run a query and paste the resulting table from the query into excel all using VBA (easy peasy).
The problem i have is that the table is still linked, and i do not want or need it to be linked. I know that there is a "Unlink" Option, which is not what i need, and also a "Convert to Range" option which is also not what i need, but closer. This got me thinking, is it possible to import the data into an array (all in VBA in excel from the query in the SQL) and then i can paste that information into my Excel spreadsheet, or is there a better method?
View 2 Replies
View Related
Mar 31, 2004
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
View 4 Replies
View Related
Apr 15, 2014
Using EXcel 2013, Windows 8
I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc
Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?
Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.
View 2 Replies
View Related
Mar 20, 2014
I have this excel template which i am working on and its a bit kind of complex linking several sheets together.
My problem now is in the Junior STUDENTS INPUT.xls
the 2nd term report has to be replicated 150 times following the patter of the one already done linking all necessary cells as the first one,
View 5 Replies
View Related
Mar 6, 2012
I have 2 excel workbooks - each containing one table.I have 1 Powerpoint Prtesentation with OLE Links to the 2 tables.
All I want is for any update in the excel tables to show in the running PP Presentation. The annoying part is when I set up the linked objects, everything works perfectly and any changes in the excel files update real time in the PP presentation.
As soon as I close the excel file and reopen though, from thereon in I have to update the links manually.
Things I have tried:
A PP Add in called "Update Links" which updated the OLE links on every rotaion of the show. This would be a fine workaround if it didn't stop the slideshow if/whenever someone was updating one of the source files with the "file is already open..." message...So set both source files to shared as PP will only be reading the data anyway. Still the error appears.Inserting some code into PP:
Code:
Sub linkupdate() Dim osld As Slide Dim oshp As Shape For Each osld In ActivePresentation.Slides For Each oshp In osld.Shapes If oshp.Type = msoLinkedOLEObject Then oshp.LinkFormat.Update Next oshp Next osld End Sub
All I want to do is update the links without stopping the slideshow. I have tried numerous PP approaches, but maybe there is an Excel solution that will update links automatically on each save.
View 1 Replies
View Related
Aug 14, 2012
I am using the Format as Table feature in 2010 and I am summing a range of cells (C2, D2 and E2) with the result in F2. I have linked F2 three rows below the table in cell C5.
When I add new data in the 3rd row in the table feature I can get an updated result in F3 but my linked cell does not update as it is now pushed to cell C6.
How can I have the linked cell update with the new total from cell F3 as it moves relative to the table?
View 1 Replies
View Related
Mar 6, 2013
I have a workbook with several sheets with formulas, etc. one of the sheets its like a "resume" of the workbook. I want that sheet with the "resume" to be visualized by other person's without giving the access to the workbook.
The idea it's a file with linked data with the workbook that have the sheet with the "resume". When I change some data in the workbook the file with the linked data must be updated when someone open it and cannot edit, it is just for visualization.
View 5 Replies
View Related
Aug 1, 2013
I'm working on a project that has a master workbook and multiple files that link to the master. The master workbook calculates values based off of a ton of information: account info, pricing info, quantity info, etc. It is a pretty massive excel file, but that is not where the problem arises.
The files that I am trying to link contain relevant information for specific accounts, including prices. The cells that contain product prices are linked to the master workbook. Example: Destination file, lets call this "Company A", Source file, lets call this "Master". In Company A's spreadsheet there is a column that contains pricing specific to that company. This pricing comes from the Master. Most of the pricing is in column C. What I have been doing is copying the relevant cell in the master and "Past Special, Paste Link" in Company A. I do this for every product in Company A's worksheet.
The goal of this is to automate pricing, so that when there are price changes or account changes, one would only have to update the master and all the separate account worksheets would populate with the correct prices. The method I've been using was working perfectly, until I had to edit the master file. I had to insert and delete a few rows from the master file. I work in excel quite often and link cells alot, so I figured that the linked cells would update to the new format, but they aren't. Ex. If a cell in Company A's worksheet is referencing F46 in the master and I delete row 44, I would like the cell to now reference F45, because that is the cell with the correct information. But instead Company A's worksheet isn't adjusting for the deleted row and is still referencing F46.
The only solutions I have found for this issue are to either have all the linked files open when I am editing the master. But seeing as there are almost 25 files, that doesn't seem very practical. The other solution is to never insert or delete rows, just to add on to the end of the master spreadsheet.
View 5 Replies
View Related
Jan 5, 2012
In Excel 2007, I have a tables linked from Access 2007. I frequently delete and import a new set of data into Access, then refresh the Excel file. Most of the time this works, but I've noticed sometimes one column doesn't refresh (even though I can see it in Access). If I modify the field name in the Access query, then refresh the table in Excel the newly named field is added in the in the last column of the table with current data. The column that wasn't refreshing stays the same.
When I originally created the link in Excel, I added various columns with formulas, but do not edit the columns that are imported from Access.
View 6 Replies
View Related
Feb 1, 2012
Using Excel 2007.I have references set for Microsoft ADO Ext 2.8 for DDL and Security and Microsoft AciveX Data Objects 2.7 Library.
I am trying to refresh tables in Access dbase from Excel.
I am receiving this error:
Run-time error '3709' The connection cannot be used to perform this operation. It is either closed or invalid in this context
Debug points here
Code:
Set adoTbl.ParentCatalog = adoCat
what I am doing wrong?
Full code below
Code:
Option Explicit
Sub RefreshLinks()
'Comments: 1.)Refresh linked tables
' 2.)Set Reference To Microsoft ADO Ext. 2.8 for DDL and Security
'
'Date Developer Action
'---------------------------------------------
'02/01/12 ws Created
[code]...
View 3 Replies
View Related
Apr 12, 2012
I have a workbook created in Excel2003 as an xls with a nuumber of buttons to call macros. I converted it to an xlsm in Excel2007 and now get the message "((#Ref.xls could not be found..." whenever I click any of the macro buttons. Event code in the individual worksheets works fine.
View 6 Replies
View Related
May 30, 2013
I have links between Excel files and Word files. I use these files for multiple projects. When I copy them and paste them to a new folder the new files would be linked together. For some reason this has stopped. Now when I copy, the Word file has links to the original document, not to the copied document.
View 7 Replies
View Related
Dec 11, 2012
I am using excel 2007
I am attempting to put a drop down list using "Data Validation". I can get it working when my list is on the same sheet (sheet 1) and the column of cells I want the drop down list to show up in....(you know..when the drop down list shows up in each individual cell)......BUT...when I put the list on another sheet (sheet 2) and try to do the "Data Validation" back on sheet 1, excel won't let me go highlight the list on sheet 2.
I even tried writing sheet2 and the range and that still doesnt work.
View 1 Replies
View Related
Apr 23, 2003
I'm trying to use VBA to automatically change the data displayed in an excel chart. To do this I count the number of items displayed on the chart using Chart.Seriescollection.Count. I then loop over the Chart.Seriescollection(x) and try to change the Chart.Seriescollection(x).Formula string to link to the cells I want.
Problem is that, sometimes, the cells the curve is currently linked to are empty. Such a curve does show up in the Count, but I can't access the .Formula, even though this can be done manually from within Excel.
View 9 Replies
View Related
Dec 31, 2009
Has anyone ever tried to make a database out of Excel? I know that Access would be a better choice, but i have never done anything in Access and have no clue where to start. i know Excel, but i'm worrying that i will end up having too much data and my program will be slow.
View 9 Replies
View Related
Oct 10, 2013
I have a userform with a textbox and would like the user to type inside the textbox which in turn send the text typed to a cell on my spread sheet say sheet 1 cell ref A1. I am writing the following into the control source Sheet1!A1 but the control source does not except this. I am using excel 2007 .
View 2 Replies
View Related
Jul 9, 2014
I got two sheets of database and I need to fill the gaps in one of then, taking the information for the other one, I been told that I can do it with vlookup or if function?
View 1 Replies
View Related
Jan 29, 2014
I have sql queries in spreadsheet column and which needs to run against Db2 database daily and then update the result back to spreadsheet. This is tedious process and could there be a way to create a a macro using VB which performs the following?
1. connect to db2 database
2. take the sql query one at a time from every row and then run against database
3. Obtain the result and then update it back to last column of the spreadsheet.
View 3 Replies
View Related
Dec 9, 2011
I'm tasked with looking up part numbers in Epicor's ERP database and entering the labor cost into excel. Is there any way to write a macro or VB program that lokks at the part number in excel, goes into epicor, pulls finds the part number and cut and pastes the cost data.
I have about 30,000 of these to look up.
View 1 Replies
View Related
Jul 19, 2012
I have an access database that runs some excel subs. If the excel sub doesn't meet a certain criteria, I want to close the excel workbook and close the access database. Most google searches yield how to close excel from access but I need closing access from excel. I was thinking that if the "detonate" criteria was met, I could pass a variable over to access and terminate that way....
If x 5 then
thisworkbook.close
myaccess.accdb.close
end if
or
if x 5 then
appAccess.application.run "Self-Detonate"
thisworkbook.close
end if
View 1 Replies
View Related
Sep 19, 2013
My situation is as follows:
1. I have one sheet with all the information about my customers, such as customer name, address, mobile number and email. I have also added customer reference no...... this could act like a primary key in databases...... This is SHEET 1.
2. my other sheet, lets call it SHEET 2.... is basically a template of my quotation where at the top I need to enter the customer details such as customer name, address, mobile number and email.. Sometimes I have returning customers and I need to enter their details again and again.... it is time consuming.
Initially I would like to enter all my customers detail into SHEET 1.
When I have to create a quotation in SHEET 2, I would like to enter a customer reference number and it should bring all the data from SHEET 1 about that customer and place it into SHEET 2 (quotation template)...
View 3 Replies
View Related
Feb 26, 2007
Some time ago, a friend of mine told me he didn't use any Pivot Tables at all, due to the imense space they require.
Instead, he made connections between Forms in Excel and the Databases using SQL.
Do you know of any Internet site where I can start to learn something about this?
View 9 Replies
View Related
Oct 14, 2008
Are there any good website for a beginner on how to set up and store information in excel as a database? I have mutliple sheets that I use everyweek for payroll and I would like to store the previous weeks data in a seperate sheet so I dont have to save each weeks sheets.
View 9 Replies
View Related
Jul 20, 2006
which databases people are using with importing excel data into a database.
I want to know people's experiences on different databases and suggestions on which database i can use maybe.
On this moment i do everything in excel, but excel is not longer working properly because of large files that need to be connected to 1 or more sheets.
This is the situation now:
VAX (Dos bases database) -> Comma seperated Files -> Importing in Excel -> Linking the data to sheets.
Now I want to have a database where i can do all normal operations to have a good look and the situation of stock status, ordered parts, sales etc. (inventory control)
Who has experiences with databases ? And what are you doing with that database?
View 3 Replies
View Related
Dec 16, 2013
I wantto prepare a database in excel. This is database of five different excel files.Consolidate them in one excel file under five sheets.
Thesefiles have name say A, B, C, D, E. Macro should ask user to browse these filesone by one and copy data in new excel under individual sheet. Finally databaseshould save as name X and should contain sheet 1 as A sheets 2 as B etc.
View 1 Replies
View Related
Jul 1, 2014
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
View 14 Replies
View Related
