I have a workbook which relies on another "source" workbook for getting some data. It then does some processing on the data and displays some results.
I want to share the workbook with the results with others, but I don't want to give them the "source" workbook.
Any way that two or more people can share and make changes on a excel workbook at the same time.
View 1 Replies View RelatedMy files always says this "This workbook contains links to other data sources" update.....how can I turn this off?
View 1 Replies View RelatedI have a workbook which has a database and a pivot table. I wish to track changes without sharing the work book.
Is that possible?
Also how can I keep track of the users accessed to the workbook ?
I build a workbook with 7 users and each user has his own password and this workbook putted in our server drive ( Common Drive ) and each user has the ability to make any thing in his own sheet
So How could make this workbook shared with this 7 users using VBA Code?
We have an Excel file that 20+ Users update daily. Every time we share the workbook, it corrupts the file within days.
We are using Excel 2010. We go to Review tab, Share Workbook, and check the Allow Changes box.
My Workbook contains several UserForms in which employees will be entering hours and activities on a daily basis. In the UserForm, they select their department and enter hours for each specific type of work/activity that shows up for that department in the userform. They click the submit button and all the data (date, name, department, type of work and hours) is added to a database table in another sheet, which in turn feeds a number of reports.
I need to be able to have anywhere from 20 to 100+ employees enter their hours on a daily basis, many of them at the same time, all from different locations (within the network of the client's system), using the same UserForms.
We've experimented with Google Drive, but that seems not to be the solution. It kind of works, but is not secure (it installs a copy of the entire workbook on the users system and then synchs the data back to the server upon "save") and way too cumbersome. I've heard of SharePoint, but not every client has that installed (and frankly I don't know how it works).
I have created a workbook with vb code that include 4 modules, 1 userform. These all work in the original workbook. When I copy and paste the workbook onto another laptop, none of the scripts work? Both laptops run the same os plus office 2003.
View 2 Replies View RelatedI have a workbook with about 30 tabs in it. It is currently protected by a password. The workbook itself has a couple of 1000 formulas across the tabs.
What I would like to do is to lock certain cells on each worksheet depending upon if the workbook is opened as "read only" or "in edit mode" (when someone uses the password).
So when someone uses the correct password, I would still like certain cells to be locked, or after the initial password is entered for editing, another popup box with "would you like to edit cell formulas? Y/N (only on selective cells), if no, then no editing on those cells allowed, if yes, then prompted to input another password to do this.
I've been working on a userform that I've been adapting from a workbook I had set up to record real time stats. Its pretty much where i want it to be now and I am trying to focus on using the stats created by the individual users to populate team stats. There will be many users using this and many different teams. so i imagine, it wouldn't take too long for just one workbook to fill up and cause problems. Would i be correct in thinking that an IF statement in my current code (that's set up to send to one master sheet at the moment) that will pick out the manager/users name from the userform and send the data to the respective teams workbook, would solve this problem. Or am i over thinking this and is there a more simple option.
Code:
Private Sub UpDate_Raw_Click()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
[Code] .......
Situation:
I have two drop down lists 1) Country and 2) States/province
Country has list: [US, Canada]
If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list
Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"
Probem:
Now I have "Canada" and "Florida" selected in the sheet
Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
i have a excel file that comes out of one system that i want to then use as a data source to combine with details out of a SQL server.
I need to be able to take the excel file and then in MS Query lookup from SQL to return fields based on the excel table.
I can see how you can create a data source for the excel file but i cant get a second data source added.
I need to do it in Excel rather than Access as this needs to be given to other users to use who dont have MS Access?
missing how to do it?
I'm creating report from a pivot table that has two data sources from two different work sheets. I noticed that you can use two data sources if you use the pivot table wizard which I tried but it can't combine the data. So I tried power pivot the same thing happened there - I will have all the column names but it will only show values from one report and if it shows values from other reports it will show the same value in all rows.
So I realized I need to create relationships which I did with a Concatenate of the dates. But still no luck.
i have to compare two financial reports from two different sources each and it should derive the same result.
I am still working on it and only focus on Column C & D. However, i have come across some problems and hope that these milestones can be cleared so that i can move on to another step to achieve my goal.
So, my current problem is
1) #N/A (eg Cell D9 & D32) will be resulted when hlookup function is used. Since the workbook 'CB_Consol_2009' does not have the same name appear, how can i make the answer become '-' instead of #N/A when hlookup function is being used?
2) Double counting. The same name appears more than one time (eg CURRENT ACCOUNTS-RET_ (01#0505_01) and as such, its figures also calculating more than once. Is there a way to calculate the figure only once so that i come out with the correct grand total?
As my objective is to compare two financial reports results (which should be the same), this is the table i can think of. If anyone here have any constructive comment on my current format.
Suppose I have a table with a collection of "Top Ten" rankings for something. For example, suppose I have rankings from five "sources" for the Top Ten Ethnic Cooking. For simplicity, I'll show the top 5 here:
Rank
Source A
Source B
Source C
[Code]....
I'd like a way to calculate some sort of average ranking. I have two problems: (1) I don't know what the formula should be and (2) I don't know how to implement it in Excel.
Can anyone explain how to name a chart? I've been accessing charts in my VBA scripts using chart names, e.g. "Chart 4". I've been determining the chart name by recording a macro and selecting the charts. But, is there a way to either:
1- change the name of a chart (as you would name a pivot table)
2- determine the name without having to select the chart in a recorded macro
3- reference multiple charts from the same worksheet without using their names
I am having trouble sharing an Excel Add-in I've created which contains three macros (modules). I have looked everywhere but, oddly, I can't seem to find a definitive answer. I would like for people to be able to install the add-in, and have access to three macros which it contains.
What is the best solution to this problem? I would like to add buttons to the add-in ribbon that execute each macro individually. The macros should be available to all workbooks once the add-in is installed.
Please see the attached.
I have put together a sort of hire chart for a charity that hires out scooters/wheelchairs.
All the formulas were done and then they said that they sometimes hire out extra equipment with the chairs as well.
I have created an 'extras' column but I'm having nightmares trying to integrate it into the current formula.
I have just changed the first 3 rows to experiment with.
There is a basic cost of £2 per week or part of a week, that price is regulated from the master page.
Column P calculates the rate from the weeks/days and uses the master page as the source of costing.
I have created a column 'C' for the extras and tried to integrate it into the already complicated formula.
Unfortunately, although it seems to work, it won't work if the cost is lower than £9. At that point, it doesn't matter what I do in column C, it just ignores column C
When it gets to £12 it seems to take notice of column C, very strange.
Wheelchair Hire-Sample New (1)(1) (5).xlsm
I want to take the values in Column U, attach the names in Column A, and rank from Highest to Lowest
Then I want to do the same with Column V/Column A
And then again with Column W/Column A
And then create 3 tables within the document that display the top 5 in each category.
It's already done, but by hand. Is there a way to automatically set this up?
See A27-A32 for what I want done automatically
I am working on a workbook that contains drop-downs in various cells. The source for these drop downs are several Named Ranges on a tab named 'Drop Downs'.
I am locking the whole spreadsheet down so that people are not messing with the format, etc. (which is the current problem). So I don't allow people to add/delete rows, etc.
I am trying to think of a good interface to allow users to modify the entries in each drop down list (i.e. they may need to add a person to the "Resources" Range which feeds into a drop-down box elsewhere).
So, people should be able to add/delete items. I was thinking I would put a little 'X' next to each entry, such that when pressed - it would delete the corresponding entry. (Using the Assign Macro on a Text Box). However, I have to assign each 'X' a method to call - and I can't pass in an argument - so if I assign them all the same macro name - I won't be able to figure out which one was pushed. And the number of 'X' will be dynamic, so I can't hard code a bunch of functions. I don't really have a good idea for allowing additions yet (other than a button that prompts for the name and then adds it in).
I guess I could just use a Form that allows editing of the data (that would be easy and powerful to do), but I don't really want to do that - I want it to be editable within the excel spreadsheet itself.
i need to share the valve of the job over the months. see attahed excel sheet. say the job worth 10k has a start date of the 30th of nov and the end date is the 1st of dec. the value of the job is shared between two days and then put into the relevent cell. as regards to the months they are not calander months so the start date and end date of the months are diffrent see attached sheet for full details.
View 2 Replies View RelatedI have 3 sheets in the same workbook. how could I have all three sheets share the same value if I edit one sheet or the other? Right now I have to edit sheet 2 to get the same value on sheets 1&3. I would like to know how to edit sheet3 to be same as sheet 1&2 vice versa.
View 4 Replies View RelatedI've spent a fair amount of time creating a financial planning spreadsheet that I'd like to try to sell. My biggest problem is selling it to people and allowing them to use it without them being able to email it to anyone else. My first thought was to code it where a macro will run when the spreadsheet opens that checks if the spreadsheet is being opened on a specific computer and will close down if it is opened on an unauthorized computer. I'm not sure if this will work or how to code it
View 2 Replies View RelatedI do not know what function will do what I need. I am compiling info from different sources to make a mailing list. Basically it boils down to the following. Sheet 1 has Names and Parcel Numbers, sheet 2 has Parcel Numbers and Addresses. I am trying to combine it in a way that I have names and addresses on the same sheet.
View 2 Replies View RelatedI am facing a issue with arranging a data in excel as weekly reports by getting a random input in the form as mentioned in the attachment
Needed like below
Name 1-Jan 2-Jan 3-Jan 4- Jan...........
Ram 0 L 0
Ravi 0 0 0
rohan 0 0 0
sheel 0 0 0
Inputs are like below format
name 2-Jan 8-Jan
Ram L 0
Ravi 0 0
rohan 0 L
sheel 0 L
Attached File : formal needed.xlsx
Is it possible to use a today or now function in an sql query to an external datasource in excel 2007? I have an excel 2007 spreadsheet, that I have linked to our financial database (MySql) using the database wizard. I can get the tables to display in excel no problem. One table shows how much staff have billed in each invoice period. I would like to create a query in excel, so that when this table displays in excel it shows how much each staff member has billed for the current invoice period, as opposed to all of them.
We weren't sure how to do this, so we decided to do a query to pull out the current invoice period, then we could combine this pivot table with the one for the amount billed table to give a table showing the amount each staff member has billed in the current invoice period (do this bit in excel).
The problem is we can't seem to make the query to pull out the current invoice period work. We have tried the query below, and all the variations of it that we can think of
SELECT invoice_period FROM tbl_invoice_periods WHERE STR_TO_DATE('" & NOW() & "', '%d/%m/%Y') BETWEEN start_date AND end_date;
We have also tried referencing a cell which shows the current date, but we cannot get this to work either (we may have made a mistake in the query here, we are not entirely sure how to reference an individual cell). The only way we have been able to get it to work is if you enter the current date directly into the query, but obviously this means it's no longer automated, which isn't a solution.
The main file was linked to other files. Instead of using the "edit links" dialogue box provided by Excel, I wish to use a list box. This list box will show all the files that the main file is linked to.
The user will have the the option to single select or multiple select the files.
Upon clicking "ok" it will "open and close" the selected files, thus updating the main file. (it should also remove the update links dialogue box of the source files if they had it, it will be a hassle for the user to click on the "don't update" for every linked file that is open)
I know the update links in Excel is able to do it but the links sometimes will result in errors, beside it doesn't offer multiple selections.
Sharing excel file over network for use by multiple persons. File has suddenly stopped opening giving message "File cannot be found" etc.... I back the file up daily to my hard drive so didn't lose much.
This is the second time this has happened. File size is only 300Kb.
Excel seems to be creating temp files to the same folder, then gave a message saying changes could not be saved to the excel file.
Ran the corruption program from this site, it could not open the file.
I have an add-in which I want to share with others, but I want to protect the code from modification.
I know how to apply a password to a VBA project (Tools -> Project Properties -> Protection) but the problem is this: when a colleague installs the add-in, the subroutines within the add-in are not available in the Macros list to be added to the ribbon (in the Customize Ribbon window). The subroutines only appear when I remove the password.
Our ERP system generates reports in CSV. I have created a custom excel menu, which houses a lot of buttons that launch various macros to format these reports. I have been asked to share these formatting macros with other users.
I understand that the custom menu structures are stored in an excel11.xlb file. My thought was to simply replace the users *.xlb file. This only half worked. I get the menus and the macros, but when the users try to run the macros from the menu excel can't find them because the path is pointing to my user name.