Excel 2010 :: Difficulty Breaking Link Possibly Caused By Name Function

Sep 11, 2013

I have researched a problem I am experiencing in Excel 2010 and have had problems in the past with previous versions. It seems that occasionally I run across a link that just refuses to break. During my research I have found some reports that this may be linked to the "Name" function. !!!Bing!!! the light comes on. When I copied my template to my new folder and tried to cut and paste data into it I got a message saying something like "A formula or sheet you want to move or copy contains the name "...", which already exists on the destination worksheet." and I opted to replace the existing name with the new one.

Apparently I created an external link at that point and one that cannot now be broken unless I delete my named range and start over the naming process. I mention this here because I see lots of people having this problem and the stock answer is to goto the Edit Links menu and attempt to break the link. Try as they and I might, this doesn't work. I also saw a post about it being a phantom validation problem and that too panned out although I can see how the validation could be linked externally and create this problem.

Does this accurately describes the issue and is there a remedy besides re-creating the "Names"?

View 1 Replies


ADVERTISEMENT

Excel 2010 :: Breaking Out Numbers Out Of Text?

Jan 13, 2013

I have this text at J22 and i would like to break out 13 and 95 000 out of this:

<td><span>13</span></td><td class=""right_align""><span>0-0-2</span></td><td class=""right_align""><span>95 000

The numbers and lenght of the numbers where 13 and 95 000 are may vary in lenght. I need them to parse the values at those spots to separate cells.

Im using excel 2010

View 12 Replies View Related

Excel 2010 :: Breaking Data Links To Another Workbook

Oct 18, 2012

I am running a macro-enabled Excel 2010 file, and there is a link to another workbook that pops up every time I open it asking if I want to update. I have searched formulas and pages, and can't find where it's linking from. I searched the VBA module I've been working in, and I can't find the link. When I click Data->Edit Links->Break Links and confirm, nothing happens. It still shows up as linked, and still prompts me to update next time I open it. I'd really like to get this data link severed.

View 3 Replies View Related

Breaking The Link Between Charts And Data

Jan 22, 2008

I am trying to record a macro so i can click a button and have excel break the link between my charts and the tables that drive them. during the record phase, i click on each chart and use F9. the problem i am facing is that now my macro sets the charts to the data that was there when i recorded the macro, erasing any changes i have made.

View 9 Replies View Related

Difficulty With IMPOWER() Worksheet Function

Jun 7, 2008

1) The cubic root of complex numbers "x+yi" are calculated using IMPOWER().
The w/s function appears to return incorrect values when the coefficients of the complex number are -ve.

2) For example:
A1:: -8+0i
B1::= IMPOWER(A1,1/3)
the function returns: 1. + 1.7320508i
instead of the correct value of -2.

3) The problem appears to be consistent with calculating theta [=atan(y/x)] as "pi" instead of "0" for the above example "-8+0i"

View 9 Replies View Related

Difficulty With IF Function For Cells Containing Both Numbers And Letters?

Feb 1, 2013

I have a sheet with part numbers, costs and wish to add varying number to the cost in each row, based on a certain aspect of the part number:

A
B
C

[Code].....

In the example, 01/1X/2X/3X are the differentiating aspects of the part number. To rows in column A containing 01 (preceding the dash) I want to add nothing, 1X+.75,2X+1.5,3X+9.

I have researched the nested IF formula and can use it in a test as long as column A contains either numbers or letters. The problem I have is because there is both numbers and letters, the logical argument won't validate "if true". Here is my formula (omitting my attempts with parenthesis etc. around the letter in the logical argument):

=IF(IF(A1=1X,B1+.75,IF(A1=2X,B1+1.5,IF(A1=3X,B1+9,B1)))

View 9 Replies View Related

Excel 2010 :: Insert Picture Without A Link

Feb 5, 2013

I'm using a macro to insert a photo in to a Excel 2010 workbook. If the photo moves location it will no longer display the photo in the document as I belive the photo is being referenced instead of embedded.

If you then use the "Change Picture" icon the photo embeds but how do I get my macro to embed the photo.

Sub InsertPhoto(PictNo)

Dim SH As Worksheet
Dim Rng As Range
Dim MyPic As Picture
Dim sPath As String
Dim PicH As Single

[Code] ......

View 1 Replies View Related

Excel 2010 :: Link A Cell To Textbox?

May 13, 2013

I am using Excel 2010. I am creating a flowchart which will require formulas. Unfortunately the flowchart connectors work best with text boxes rather than the cells. To make this method work, data will be entered into the text-boxes and will be calculated into other text boxes. Basically I need the data I enter into a text box to automatically fill itself into a cell in the background. I know there may be other ways to do this whilst not using text boxes at all, but I will not be using them.

I can fill a text-box from a cell by using F2 on the textbox and then typing =A1 etc, but I am having a hard time going the other way - getting a cell to fill from the value in the text box.

View 1 Replies View Related

Excel 2010 :: File Sharing - Link 4 Spreadsheets Where Information Will Be Updated By One Person

Apr 21, 2013

I have a Excel Spread Sheet which has been created, there will be 4 people in 4 different locations who will have access to this spreadsheet and will need to update/amend it through "Dropbox", my question/problem is, how can I link the 4 spreadsheets where information will be updated by one person, but will update all 4 sheets at the same time.

Is this possible and how do I go about it?

View 4 Replies View Related

Excel 2010 :: Workbook Not Closing Correctly When Accessed Via Link On Sharepoint Site

Feb 18, 2013

I was asked to create a userform for the use of our whole team which would enable them to select a name from a dropdown list and then it would display their business contact details in the rest of the form.

I'm running Office 2010 and Windows XP server/enterprise edition (not quite sure which). After some research I've managed to use VBA code to open the userform while hiding the workbook, then when the close button is clicked it closes the user form and it's associated workbook using the code below.

Code:
' ThisWorkbook module
Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub
'UserForm module
Private Sub CommandButton1_Click()
Unload UserForm1
ThisWorkbook.Close (False)
End Sub

This works great when the excel file is accessed directly. However when the excel file is accessed via the link on my teams Sharepoint site it doesn't. When the Userform is closed the underlying workbook and data is then displayed which then has to be closed separately.

View 2 Replies View Related

Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function

Apr 8, 2014

I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:

[Code] ........

When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.

View 14 Replies View Related

Breaking Ties In Rank Function

Jun 21, 2014

I am trying to break ties in the Rank function.

Example worksheet attached.

I have an eight digit number (a digit can show up more than once) and determine the rank of the sum of each number. I had the ties broken by whatever digit showed up first.

In the following example, June 13th row showed 6 digits tied for second place and the tie was broken by the order in which the digit show up in the range.

On the June 14th row it showed digit 4 and 5 tied with 3 occurrence each (running total for the two days). But digit 4 came in twice that day, and reach a total 3 first before digit 5, but Rank2 shows that digit 5 was ranked first, which should of been digit 4.

View 14 Replies View Related

Excel 2010 :: Link Data In Different Spreadsheet And Data Will Be Automatically Updated

Nov 3, 2013

I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?

View 2 Replies View Related

Excel 2010 :: Function To Create PDF

Jan 24, 2014

The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.

So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.

Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?

View 4 Replies View Related

Excel 2010 :: Function Error In VB

Dec 21, 2011

Having problems with the code below on a 2010 Excel spreadsheet. The function flags me at first line and highlights Mid. I get a "compile error, cannot find project or library". I'm trying to set GetSheetName = m_sSheet but the GetSheetName function is not declared as returning any type.

Code:
Function GetSheetName(ByVal m_sFormula As String) As String
Dim m_sSheet As String

m_sSheet = Mid(m_sFormula, 2, InStr(m_sFormula, "!") - 2)
If InStr(m_sSheet, "'") Then
m_sSheet = Mid(m_sSheet, 2, Len(m_sSheet) - 2)
End If
GetSheetName = m_sSheet
End Function

View 1 Replies View Related

Excel 2010 :: Random Numbers In One Function

Jul 22, 2012

I'm currently working on my masters dissertation and am using excel 2010.

Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.

I'm sure there has to be a faster way than creating 1000 tables for each random number generated.

Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.

View 2 Replies View Related

Excel 2010 :: Creating VLookUp Function In VBA

Dec 28, 2011

Im trying to write a code in vba in Excel 2010 that would incorporate the vlookup function in excel.

My Data Looks like this:

List 1Weight 1List 2Weight 2List 3Weight 3List 4Weight 4a2.00%j20.00%a14.00%p2.00%b4.00%k32.00%d2.00%y3.00%
c7.50%e10.00%g14.00%u6.00%d8.00%d15.00%h20.00%h7.00%e9.00%q13.00%y45.00%f8.00%f20.00%r5.00%q5.00%
d9.00%g35.00%n5.00%t10.00%h10.00%r14.00%i4.50%w15.00%b0.18Z0.04S0.04Total100.00%Total100.00%Total100.00%100.00%

Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.

What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.

So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.

The code I came up with so far looks like this:

Function AK_Overlap_Go(x, y)
Dim Temp(1 To 2, 2 To 1)
Dim x As Integer

[Code]...

One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.

View 3 Replies View Related

Excel 2010 :: Copy And Paste Function?

Apr 18, 2012

The problem is that whenever I have any browser open, IE, Chrome, Firefox, etc... the Cut & Paste, Copy & Paste function does not work correctly in Excel 2010.

When I Cut or Copy the blinking marquee around my selection briefly appears and then disappears. When I try to paste, I only have two options under the paste special function: Unicode Text and Text, same with cut and paste, however, the text doesn't actually cut, it only copies.

As soon as I close down any of the mentioned browsers, the full functionality of the cut/copy & paste functions are restored, no need to restart excel.

I need to have open a browser most of the time for work as our system is web based, so closing and re-opening is more than just an annoyance.

View 2 Replies View Related

Excel 2010 :: Random Numbers In One Function?

Jul 22, 2012

I'm currently working on my masters dissertation and am using excel 2010.

Basically, I'm trying to generate a list of say, 1000 numbers within a range. I can already do this using the rand() function. However, I need excel to take each random number, apply it into a formula, and then list the answers in a seperate location. This would be easy to do if there was one simple function (which i could just insert into any adjacent cell). The problem is that each random number value is input into a table. The table, which has about 10 columns and 200+ rows, gives one final answer.

I'm sure there has to be a faster way than creating 1000 tables for each random number generated.

Let's say I have a list of 1000 random numbers in sheet 1. the function table is in sheet 2 and produces the answer in the same sheet, in a single cell. I'd like excel to use each random number generated, input it into the function table, attain the answer from the table, and list it in the cell next to the random number.

View 1 Replies View Related

Excel 2010 :: Color Function Substring

Sep 16, 2012

Using Excel 2010 Pro.

I have a formula to create a string to create a lable for a Trial Balance report
=LEFT(I5&" "&REPT(".",95),101)

But the decimal portion of the string is distracting. I would like to make it a grey instaed of a black so that the text portion of the label "pops".

I don't believe I can do it with conditional formatting and I do not want to do it manually.I was thinking some kind of function or UDF like:

=LEFT(I5&" "&RGB(REPT(".",95),(191,191,191)),101)

View 3 Replies View Related

Excel 2010 :: Average Function With A Variable

Jan 4, 2014

I am working with Excel 2010 and I have a problem that I can't seem to figure out. I am trying to find the first nonzero cell and begin an average function from that cell and down through 29 rows (a total of 30 rows).

Sub average()

FinalRow = Cells(Rows.Count, 3).End(xlUp).Row
For I = 3 To FinalRow
If Cells(I,3).Value 0 Then
'I use the above to identify the first nonzero cell
ActiveCell.FormulaR1C1 = "average(........)

This is where I am stuck, I don't know how to code the first nonzero (which can change with different data) cell and average rows below it.

View 7 Replies View Related

Excel 2010 :: Lookup For Match Function

Jul 17, 2014

I think I need some version of a lookup for match function. Here is essentially what I need: I have a column of data points. I need to output every position at which the data point changes to a different value (going from top to bottom). See below for an example.

Here is some sample data (with column headers)

Position
Data Pt

1
-7.5

2
-8.0

[Code] .......

Here is the result I need (I want to put a consistent formula into the second column below that returns the results shown in the second column below):

split 1 position
2

split 2 position
4

[Code] ........

Is what I am describing possible? Because I am inserting this into a template, I ideally need to do this outside of any sort of VBA work.

View 2 Replies View Related

Excel 2010 :: Function Or Formula For Advanced Filtering?

Apr 29, 2014

I am looking for a function or a formula that performs the same process in Excel 2010 as:

Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.

View 2 Replies View Related

Excel 2010 :: Return In Function Gives Syntax Error

Apr 4, 2014

I get syntax error on return statement. I am using Excel 2010.

View 2 Replies View Related

Excel 2010 :: Function For Last Modified Date Of Cell?

Feb 14, 2012

What I wanted was to a function, say, "=LastModifiedDateOf(CELL)", where CELL, is a parameter that indicates the cell I want to monitor. If the value of such cell ever gets changed, the cell containing the function has its value updated to the current date.

E.g.
A1 = "AA"
A2 = "=LastModifiedDateOf(A1)" -> "10/03/2011 10:30:32"

-- Make an update:

A1 = "BB"
A2 = "=LastModifiedDateOf(A1)" -> "14/02/2012 12:15:11"

I'm not quite a super user in Excel, but this function would be very very useful for me.

ps. I'm using Office 2010

View 4 Replies View Related

Excel 2010 :: Call Function In Another AddIn (XLAM)

Jan 10, 2013

I try to call a function/procedur in an Excel2010-AddIn (.xlam file) from another Excel2010-AddIn (.xlam file):

Code:
Application.Run (filename.xlam!functionname)

Both AddIn-files are in the same directory (the default AddIn directory).

This call causes the error message "runtime error 91: object variable or with block variable not set".

This problems came up with providing an existing Excel2003-AddIn for Excel 2010.

The same code runs in following cases:

1. call a function in an Excel2003-AddIn (.xla) from another Excel2003-AddIn (.xla)
2. call a function in an Excel2003-AddIn (.xla) from a Excel2010-AddIn (.xlam)

View 1 Replies View Related

Excel 2010 :: VBA - Using Date Via Defined Name In Match Function

Feb 20, 2013

I'm using Excel 2010. I have three columns. Column A contains the date, Column B contains the time, and Column C contains the date and time. Column C is the result of a formula, which adds Columns A and B (ie. =A2+B2). So I have the following...

Date
Time
Date and Time

1/15/13
9:00:00 AM
1/15/13 9:00:00 AM

1/15/13
9:00:05 AM
1/15/13 9:00:05 AM

[Code] ....

I'm using a defined name to store a date and time, which I'm going to use as a lookup value in a Match function. I've defined MyVal as follows...

1/15/13 9:00:20 AM

Excel automatically converts this to the following decimal number...

=41289.3752314815

When I first run either of the following two macros, they correctly return 5...

Code:
Sub test1()
Dim x As Double
x = [MyVal]

Debug.Print Application.Match(x, Range("C2:C10"), 0)
End Sub

Sub test2()
Dim x As Date

x = [MyVal]
Debug.Print Application.Match(CDbl(x), Range("C2:C10"), 0)
End Sub

However, when I save and close the workbook, and then re-open it, these macros both return an error. Is this some sort of bug in Excel?

View 9 Replies View Related

Excel 2010 :: Median IF Function For Certain Values On Different Sheet

Oct 16, 2013

I'm trying to get the Median (and min/max later) of certain values on a different sheet. For example, I'm operating in one sheet and pulling the values from the sheet "Form Responses". I want a median for numbers when the values in column B are "Aransas" and the values in column D are Yes. The values are in Column F. My formula is:

=MEDIAN(IF('Form Responses'!B:B="Aransas",IF('Form Responses'!D:D="Yes",'Form Responses'!F:F,""),""))

It's giving me the median for ALL of the numbers in F and I can't figure out why.

Following that, I'll want a median for all the numbers in F when column B value is "Aransas" (in other words, dropping the Column D condition).

Excel 2010, Windows 7

View 1 Replies View Related

Excel 2010 :: Replicate Conditional Formatting Using A User VBA Function?

May 6, 2014

Using Excel 2010.

The background to this question is that I'm trying to replicate conditional formatting using a user VBA function, because I require a thick border around the cells (and the conditional formatting within Excel only has thin borders)

In the final function, there will be 9 combinations of formatting {Red, Amber, Green} interior with {Red, Amber, Green} thick borders.

The formatting is determined by a number in another cell (the "target" cell), which returns a value 0,...,8

I've only got as far as filling in the interior for the first combination, but the function returns an error "Application-defined or object-defined error".

VB:
Function VBA000_003_SetRAG(strTargetRange As String) 'strTargetRange is the reference for the target cell that contains the value 0,...,8

On Error Goto handler [code]....

There is not a problem with the target range, if I remove the two lines relating to rngCaller then the function works OKExecuting the rngCaller.Interior.Color = RGB(255, 0, 0) command through the Immediates window works OKI've tried calling a subroutine & passing the range across

Same errorChanging the range in the subroutine to an absolute range (eg. Sheet1.range("A1")) also causes the

same errorExecuting the subroutine on its own (with the absolute range) works OK

So my guess is that it's a bug/limitation with Excel VBA when trying to execute commands from inside a user function

View 3 Replies View Related

Excel 2010 :: Macro To Perform Find And Replace Function

Apr 8, 2014

I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.

So for example:

Sheet1 says the following on column N:
cat
dog
lion
bear

Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear

Column B
B1: 2
B2: 8
B3:15

I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved