while trying to view a hyperlink i just created in a cell within a worksheet, a message popped up saying:
mircosoft office excell:
X this operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator.
I've created a spreadsheet of clients and info; I want each client name to link to their specific folder on my computer for easy referencing. I entered in all the links and they work just fine; however, when i tried to save the file to another location on my computer so it could be used by another employee (same terminal, etc, just in another folder) the hyperlinks stopped working.
View 5 Replies View RelatedI have 2 sets lists which should be identical, but every day I get give one new file which I have to compare to the other one. I need to find new data and removed data in the new file.
It is a dynamic list, but I need to check every day that all the data that was there yesterday is also there today. If not then I need to be told which data is missing and which data is new (maybe thrrough a pop up box and a new sheet with a new and cnacles coloumn).
A Example of the data is below. On day1 everything matches. On day 2 Grapes is a new entry, so I need this flagged up.
On day 3, pears hsave disappeared, I need this flagged up.
What would be great is to have a messgae box pop up and say "2 new entiries and 3 deletions - Please refer top Changes tab". And then the new/deleted entries will be in a seperate sheet.
I have tried searching this on the site, but can't find anything (I can't beleive problem like this is not on the website) - If there is an article would you be able to point me to it.
Day1
orangeapple
pearorange
applepear
bananabanana
Day2
orangeapple
pearorange
applepear
bananabanana
grapes
Day3
orangeapple
pearorange
applebanana
bananagrapes
grapes
how do i protect a cell from being changed? I don't want to protect the whole worksheet, just a few cells within a worksheet? I have tried the Allow Range Users function, but cannot get it to work? Kind regards, Michele
View 8 Replies View RelatedI have added the function and it works great .
Until lately users wanted to add comment as long as a paragraph , and it did not work any more . Is it a length restrictions , and how its possible to add longer comment .
I have tried text as length below @ 143 char (it did not work)
One Two Three Four Five Six Seven Eight Nine Ten Evelen Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen Twenty TwentyOne
I get the results printed until Sixteen. @ 97 Character.
I've got an question about using excel macros to search for information in a database with certain restriction. In my attachment you can see an example of the database I use.
Example.xls
I've got a database with certain packages, the length and the width of these packages is known.
I've also got a database with certain products, the length and the width of these products is also known.
I want to use a macro which allows me to see which packages are useable for a certain product. A package is useable if the length/width is less then 10 bigger then the product. So in the example if the width is between 10~20 and the length between 40~50.
Originally I thought I could use a macro with advanced filter and having restrictions width >10 <20 and length >40 <50. But I can't fit this in as macro because I already get the width and length from the product from another database and macro so it can't say >40 only 40.
I have multiple tabs and each tab has an invoice. In the summary tab, I want to grab certain cells ie. Invoice #, Number of items, and total invoice amount.
The tricky part is, although the format for each invoice is the same, the product list for some invoices may be longer in some invoices (which will affect the total amount - aka total amount may be in cell F40 instead of F10).
Since I get these invoices on a regular basis, I would really like to create a macro where it will just find the items I need and paste it in the summary. I have no problems making this file work if the items are all in the same cell and format for each tab.
Is there a way that I add instructions/restrictions to the push buttons on my form (basically they perform filter functions).
For instance, currently the only way to search the client or employee name is to enter the entire name. Is there a way to allow partial name searches?
Can I have a message appear that says "'Joe Shmo' can not be found".
Also I would like the input box not allow blank searches. If the input box is selected but then closed with no data entered, it filters for blanks which removes all data.
I have attached a sample of my form for you to look at.
Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc.
Book10.xlsx
I have a spreadsheet and within the first sheet there are lots of hyperlinks to other cells within sheet 1 (my template).
I have made a small macro which very basically makes a copy of sheet 1 (my template)
The hyperlinks in the template are explicit and as they are copied to sheet two, they still reference back to cells in sheet 1. I need the hyperlinks to be relative, so that when I make a copy of the template the hyperlinks are copied and make reference to cells in the new sheet. I cannot work out how this should be done
Have an excel table with following data:
- ID
- number of bottles
- number of bottle crates (there are 20 bottles in one one bottle crate)
201688194000bottles
20168819200crates
2016883812000bottles
20168838600crates
201688396400bottles
20168839320crates
201688809000bottles
20168880600bottles
20168880480crates...................
I need to write a macro which will do this operation for each ID:
(bottles/20)-crates = x
and if "x" is not 0 then write down the value of "x".
There are two points I would like to point out:
- One ID may contain 3 or more rows (see 20168880)
- The macro will work with hundreds IDs so the algorithm should be fast (but it is not necessary)
Is it possible to copy sheet and rename the copied sheet in one operation .... have a hidden worksheet that needs to be copied and given a variable name dependent on the work sheets that are already present.
View 4 Replies View RelatedI have an issue with the Data - Text to Columns operation.
I paste some data into a sheet that is comma delimeted. I have a macro that runs the Data - Text to Columns operation and then moves the data to another sheet.(The data I need to paste is coming from a non-Excel application.)
I then copy a second set of data to another sheet and when I paste it, it automatically does the Data - Text to Columns operation but I do not want it to. I have seen this several times before and when I am manually manipulating the data it isn't a problem. When I use macros to manipulate the data, I don't get the desired results because my macro includes this operation.
I have done a little testing and this issue doesn't seem to always present itself. How can I paste the data without Excel trying to out-think me and just leave the data in the comma delimeted format it is in when I paste? What causes this? Its seems like Excel is seeing similar data and "assuming" I want to perform the same operation I just performed.
I have two tables excel: Table1 and Table2, as shown in the attachment below. I wish that: in Table 2 to be filled in each cell how many hours (eg. 0.166 hours) worked every train. There is an Excel function to look in Table 1, reading time intervals and automatically fill them in each cell of Table 2?
View 8 Replies View RelatedI am not too sure what this is called but I am using Excel 2010 and need particular document that I use repeatedly. Basically, if a particular cell in the document does not contain a phrase then I want Excel to automatically change the text in that cell to bold and red and 2 other cells in Excel to bold and red as well. For more information, the cell is 19–20 and K–O and the phrase in the cell is "No Inconsistency".
So if "No Inconsistency" is not found in that cell then it automatically changes the text in that cell to bold and red, and it also changes two other cells that are found in that document to bold and red as well. The coordinates of those two other cells are 19 with H–J, and the other one is 17 with H.
Using Excel 2007.
My vba code seems to be dividing a range by 1M more than 1 time
My initial value is 51543942
After by code runs the display is 0.00 MB and the value in the formula bar is 0.000000000051543942 or 5.15439E-11
I would like the final display to be 51.54 MB
what I might be doing wrong?
Code:
'Format columns
r = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range("C2:C" & r)
.Range("IV1").Value = 1000000
.Range("IV1").Copy
[code].....
I keep getting an error in this code while trying to write the results of a query to a sheet in the current workbook. The error i get is 'operation is not allowed when the object is closed'... since i didnt close the recordset before trying to write the results to the worksheet, im at a loss to explain why im erroring...
Public Sub fillWorksheetWithData(dataSheet As Worksheet, conStr As String, sqlStr As String)
'clear datasheet
LastRow = dataSheet.Range("A65536").End(xlUp).Row
If (LastRow < 2) Then
LastRow = 2
End If
dataSheet.Range("A2:A" & LastRow).EntireRow.ClearContents
I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week).
I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically:
I want to insert a column after column G.
I would like to enter the following formula into H2:
=IF(G4="u", "u", IF(G4
I am running a script that populates about 70 or so workbooks based on information from the database. Naturally this script is quite slow but what seems to take the most time is opening and closing workbooks. Is there a way to "hide" the workbook opening/closing operation on the screen? I have tried using the
Application. ScreenUpdating = False
this sped the code up but you still see the workbooks opening on the screen.
I would like to perform a sum of randomly generated numbers between 0 and 6, but I want the number of independent random numbers to be dependent on another cell. I have =SUM(RANDBETWEEN(0,6) and then I get stuck. Is there a way to perform the RANDBETWEEN(0,6) a number of times stated in another cell? In this case that value is 67 but it will vary from around 5 to 150. I think there is a way to do this with macros, but I am not versed in visual basic. I am using Excel 2003.
View 2 Replies View RelatedI would like to produce a carousel effect. i will start with a basic example and then explain the complications.
Basically I have a range of cells of indeterminant length. The left column is numbered vertically - let's say 1,2,3,4,5,6. I would like to be able to generate a carousel effect so that if I right click on a number all the cells below move down and the number at the bottom fills the gap so that if I right click on 1 the cells would become 6,1,2,3,4,5 or if I right click on 3 the cells would become 1,2,6,3,4,5.
The complication is that there will be more than one of each number eg 1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,5,5,6,6,6,6. (there will allways be the same number of each) so I would want the whole block to move down and the ones at the bottom to come up and fill the gap.
The other slight complication is that the range of cells is 5 columns wide so I would want the whole block of cells to carousel.
I have added a shape to my spreadsheet to work as a button. All is fine and it has the desired functionality however, adding the actual effect or simulate the effect of a button click/pressed?
View 14 Replies View RelatedExample number 1:
You invested 10.000 Dollar in the year 1985 into a tempelton fund and waited 20 years. Result can be seen at many forums and stock plattforms
Example number 2:
In 1985 you had no 10.000 Dollar, but only 100 Dollar a month. You invested this 100 Dollar each month for 20 years.
How big will the difference be between example 1 and 2 ?
*** Background.
If a "share" will cost 100 Dollar, you will get 1 share for 100 Dollar. Next month share is down to 50 Dollar, you will get 2 shares for your monthly 100 Dollar. Next month share is up at 200 Dollar, you will get only a half share for your 100 Dollar. Its called cost average effect.
Tool should allow "play arround", to simulate various scenarios, talking into account growth rate, time period, monthly investment, fluctuation of a theoretical share. Idealy we would love to " import" e.g. famous fund history (eg. Tempelton, Pioneer) etc. to see how investment would have developed when a steady monthly payment would have been made.
I am making an excel sheet which can track the hypothetical effect building an order has on current inventory. The user is to change the quantities in this hypothetical order whenever they want.
It is composed of two sheets, the inventory sheet and the model sheet. The inventory sheet has the costs of every part carried and how many are currently in stock. The model sheet has a breakdown of what parts are required to build the different models.
I am looking for a function or VBA which can take a hypothetical order (build xx of model one + build xx of model two + build xx of model three), see how many total parts are needed, then calculate and display the effect it has on current inventory. This is only to see the hypothetical effect on inventory, not to actually change the inventory in the inventory sheet.
I've attached what I have so far - most everything is completed except for this final calculation. I have tried using the VLOOKUP function in VBA to try and come up with a solution but I couldn't figure out the best way to go about this. I think the hardest part might be that a hypothetical order can be made up of all three models.
I have set up a spread sheet which uses tick boxes and I have calculated it all up so that the percentage of ticks ticked is displayed in a cell etc. and when I link that data to a bar chart it works perfectly. .e.g as I tick the boxes the bar chart increases
BUT when I try to do the exact same thing with a pie chart it doesn't work.
Not sure what formula to use for this set-up:
Col1 - Name_Last
Col2 - Name_First
Col3 - Ref_Name
The desired effect is under column 3, you have "Name_First Name_Last". Attached sample file.
I am trying to make a spreadsheet that contains numbers in columns D to P. My end user wants to be able to adjust these with scroll bars so I could make ActiveX scroll bars under each number in each column but this would be a lot of scroll bars (one in each row and columns)!!!!
Is there are way where I have have scroll bars in column D to P that only effect the active rows and always sits below the active row?
I want to gradually change th colour in a block of cells. my plan is to use code to sequentially fade through ten or so shades from light to dark. is there a way to slow the code down so that it takes a couple of seconds to run the sequence?
View 2 Replies View RelatedI created a couple of piviot tables from the same sheet I had VB code in. Since the creation of these P tables the code has stopped functioning.
In a attempt to re-activate the code i moved the piviots to a new spreadsheet. But the code is still not working....
I would like to freeze the bottom part of my spreadsheet, such that the user can scroll through the upper part but still be able to see the bottom part. The freeze command freezes what is above the line, I want to freeze what is below the line.
View 4 Replies View Related