Hyperlinks :: This Operation Has Been Canceled Due To Restrictions In Effect On This Computer

Mar 6, 2010

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.

ADVERTISEMENT

Hyperlinks Won't Work When Save File To Another Location On Computer

Jan 8, 2014

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 Related

2 Lists And Finding New And Canceled Data Through VBA

Nov 30, 2006

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.


Day1
orangeapple
pearorange
applepear
bananabanana

Day2
orangeapple
pearorange
applepear
bananabanana
grapes

Day3
orangeapple
pearorange
applebanana
bananagrapes
grapes

View 9 Replies View Related

Protect Some Cells Without Other Restrictions

Nov 30, 2006

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 Related

Sub Update Comment - Length Restrictions?

May 28, 2014

I 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.

View 4 Replies View Related

Searching For Results In Database With Certain Restrictions

May 28, 2014

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.

View 4 Replies View Related

Consolidate Multiple Tabs Into One Summary Tab With Some Restrictions?

Jan 9, 2013

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.

View 4 Replies View Related

Input Box Instructions/restrictions For Filtering Data

Nov 12, 2007

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.

View 11 Replies View Related

Calculate Totals Based On Age Restrictions And Years Of Service

May 28, 2014

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‎

View 4 Replies View Related

Explicit Hyperlinks, Relative Hyperlinks

Feb 21, 2007

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

View 6 Replies View Related

Algorithm For Addition (operation For Each ID)

Sep 25, 2009

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)

View 3 Replies View Related

Copy And Rename Sheet In One Operation

Jun 14, 2014

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 Related

Data - Text To Columns Operation

Feb 21, 2008

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.

View 9 Replies View Related

Fill Each Hour Train Operation With Excel?

May 15, 2014

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 Related

Excel 2010 :: How To Create Automatic Operation

Jan 6, 2014

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.

View 2 Replies View Related

Excel 2007 :: Divide Operation Executes More Than Once

Mar 12, 2012

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].....

View 8 Replies View Related

Error :: 'operation Is Not Allowed When The Object Is Closed

Jul 28, 2009

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

View 9 Replies View Related

Do Operation On All Sheets Except The First Sheet In The Book Every Time I Update It

Jun 5, 2007

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

View 9 Replies View Related

Hide Workbook Opening/closing Operation On The Screen

Jul 6, 2007

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.

View 2 Replies View Related

Excel 2003 :: Perform Operation Variable Number Of Times?

Jan 29, 2014

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 Related

Carousel Effect

Jul 30, 2009

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.

View 9 Replies View Related

How To Add Press Effect For A Button

Oct 10, 2012

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 Related

Cost Average Effect

Oct 19, 2006

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.

View 2 Replies View Related

VBA / Function To Calculate Effect On Inventory?

Jul 6, 2014

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.

View 9 Replies View Related

Can't Get Tick Boxes To Effect Pie Chart

Apr 28, 2014

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.

View 14 Replies View Related

Formula: Desired Effect Is Under Column 3

Feb 17, 2010

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.

View 3 Replies View Related

Scroll Bars That Effect Active Row

Jan 29, 2013

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?

View 7 Replies View Related

Cool Colour Fill Effect

Sep 12, 2013

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 Related

Do Piviot Table Effect VB Code?

Dec 11, 2006

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....

View 9 Replies View Related

Invert The Effect Of Freeze Panes

Jun 13, 2007

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

Nested IF Formula That Takes Into Effect Many Variables

May 5, 2014

Basically I need a formula that takes into effect many variables. It needs to check a size, thickness, and material, to determine a cost...

1.5 O.D. - 16GA - 304SS = $X

but

1.5 O.D. - 18 GA - 304SS = $Y

and such.

The problem I have is not only am I not understanding a lot of Excel jargon, the data set is not in perfect "example" conditions.

O.D. is in N:N yet there are blanks between N(x) and N(y), GA is in O:O yet blanks between O(a) and O(b), and so on.

Here is what I have at the moment, it uses a second sheet to derive the cost based on the parameters. Please ignore the first few terms, they are used with this cost lookup to give me my final. There also may be incorrect syntax in this, but since I get a nesting error first, I really don't know.

=V7+(Q7*M7*(IF(N7=2=AND(O7=20)=AND(L7=304),’Steel Tube Cost WIP’!$DD$19,IF(N7=2=AND(O7=20)=AND(L7=316),’SteelTubeCostWIP’!$DD$20,IF(N7=2=AND(O7=20)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$21,IF(N7=2=AND(O7=18)=AND(L7=304),’Steel Tube Cost WIP’!$DD$16,IF(N7=2=AND(O7=18)=AND(L7=316),’SteelTubeCostWIP’!$DD$17,IF(N7=2=AND(O7=18)=AND(L7=2205),
’SteelTubeCostWIP’!$DD$18,IF(N7=2=AND(O7=16)=AND(L7=304),’Steel Tube Cost
[Code] ......

Mentioning LOOKUP, can I even use it? I've attempted many times however it isn't going too well What can I do?

View 2 Replies View Related

Macro To Effect Cell Based On Column

Nov 26, 2008

I wasnt sure how to name this problem, but here goes.

View 3 Replies View Related

How To Recreate ZigZag Effect On Stock Data

Sep 4, 2007

I look at stock charts that allow an overlay of "zigzag patterns." This zigzag links price extremes with a line. The price extremes are defined according to "wave size" which is described as follows:

"Wave Size: The Most Important Setting... and the thing you will want to tinker with -- is Wave Size. Wave Size is a number that defines a high or low price extreme, by stating the number of surrounding bars on either side that must not be higher or lower than the candidate extreme. Another way to think of it is: Wave Size + 1 is the minimum distance between extremes of the same high or low type."

Here is an example of what a stock chart with these zigzags drawn in looks like. [Click on the image to zoom in and see it better.]

[URL]

The black diagonal line is built using a "wave size = 5" using the above definition. And the green one is, "wave size = 20"

I am trying to build this into a spreadsheet so I can play with the data. If I have four columns: (a:open)/(b:high)/(c:low)/(d:close) I cannot figure out how to build the columns after to do this zigzag. I understand what the line is, but it just doesn't make sense to me how to derive it from the data. how to get a column n that has the value of the zigzag (with wave size = 5 for example) I

View 6 Replies View Related

Excel 2010 :: VBA To Set Fill Effect In A Cell

Jan 5, 2012

Here's a problem I'd like to be able to solve in VBA:

Retrieve the current cell color (let's say A1 = 'green')

Format A1's cell colors (using the VBA equivalent of the 'Format Cells' / 'Fill' / 'Fill Effects' / '2 color Gradient' feature) so that the background cell color (color 2) is set to 'green' with color 1 being set to 'white' with a shading style of "From Center"

I'm using Excel 2010.

View 4 Replies View Related

Limit Macro To Only Effect Open Worksheet?

Oct 26, 2013

Is there any setting or technique to limit a macro to only effect the open sheet in the workbook that it runs from?

View 1 Replies View Related

Keydown To Effect To All Controls That Support Event

Dec 31, 2006

Is there a way to fix a private sub to catch keydown effect of various combos and textboxes of my userform?

Instead of typing the same thing to each and every control's keydown event just call a sub. Code goes like:

Select Case KeyCode
Case 112
ShellExecute 0, "open", "hh.exe", ThisWorkbook.Path & "1.chm", "", 1
Case 113
If MsgBox("Backup now", vbQuestion + vbYesNo) = vbYes Then BackUp
Case 114
Col = 3
SearchD
Case 115
showexcel
Case 116
readonly
e.c.t.

Instead of that, if i place code in a private sub test() just call test in keydown event

View 9 Replies View Related

Restrictions On Drop Down List If Cell Is 'a' Then Other Cell Must Equal 'b'

Jul 15, 2009

I have two cells, both of which have drop down lists for options 'a' & 'b'. When one equals 'a' the other should be restricted into only saying 'b' and vice versa. I would also like the user to be reminded that he can only select 'a' once from the two menus. Ideally I would like to do this in a formula rather than VBA?

View 2 Replies View Related

Graph To Illustrate Desired Effect Of Marketing Campaign

Jan 21, 2014

Need a graph to illustrate the desired effect of the marketing campaign as a shift in market equilibrium with reference to price and quantity adjustments.

View 1 Replies View Related

Create Macro To Add Fill Effect On The Bars Of Chart

Jan 24, 2012

I'm trying to create a macro to add a fill effect on the bars of my chart.

However, I don't want it to change the bar's current color. Just to do the effect on the current color.

Code:
Sub fill()
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With

[code].....

View 2 Replies View Related

Charts: Vary Colors By Point With Area Fill Effect

Oct 30, 2006

I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series

So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?

View 3 Replies View Related

Loop: Macro To Go Through The Table And Preform An Operation In The Corresponding Cell In Column B Depending On The Value In Cell A

Mar 27, 2007

I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?

View 2 Replies View Related

ADVERTISEMENT