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.
I 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.
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
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.
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.
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
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.
I 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?
I 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.
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:
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.
I 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.
Example 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.
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?
I 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.