Excel 2013 :: Replacing Values In Existing Functions With Newly Defined Names

Jan 6, 2014

I'm trying to simplify a spreadsheet i've been given by defining names for certain values. I'm using Excel 2013. Is there any way to have all of the newly defined names I've created for cells automatically be inserted into all of the formulas that exist in the spreadsheet?

Example.

Old formula in one sheet of a workbook looks up a cell in another sheet with the value of ='sheet1!A1

I've given the value of cell A1 in Sheet1 a name of dgwd.

How do I get every formula in the workbook that references 'sheet1!A1 to change the value within that formula to dgwd?

View 4 Replies


ADVERTISEMENT

Defined Names Referring To Nonexistent Range Won't Pick Up Once Existing

Feb 17, 2010

I have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").

I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.

View 8 Replies View Related

Convert Combination Of Excel Functions Into One User-defined Function

Mar 12, 2014

I have a really long function which counts the number of unique values in a selected range, and ignores blank cells. The function is like this (only works as an array function):

{=SUM(IF(FREQUENCY(IF(LEN(A1:A15)>0,MATCH(A1:A15,A1:A15,0),""),IF(LEN(A1:A15)>0,MATCH(A1:A15,A1:A15,0),""))>0,1))}

I want to convert this into a user-defined function COUNTUNIQUE, which only takes one argument: the range (A1:A15 in the above example)

View 6 Replies View Related

Excel 2013 :: Truncating File Names?

Jan 27, 2014

We have had a few instances where Excel 2013 is truncating a filename when saving for example "MF Not Settled - Dec 13 Rental A.." the filename includes additional characters than this but has replaced them with .., I initially thought it could be a file path length issue but the files in question the total length is less than 150 characters including the server name.

This is also happening with links inside excel files to different tabs in the same workbook and well as macro names causing them to have to be ran manually as it has truncated the name to .. at the end.

find some examples below:

Full filename: MF Jan 2014 vols Smart churn sensitivity
Truncated filename: MF Jan 2014 vols Smart churn...

Also if you try to link to the truncated workbook whilst open it displays the formula as:

='[MF Jan 2014 vols Smart churn...sitivity.xlsm]Input c!$E$13

My work around is to save the file to another folder, without closing it, and then it works fine. However, in doing this it will try to save as the truncated name, so I first have to select the correct name of the file in the existing folder, before saving to a separate folder. However, if the file is closed, and then reopened the problem returns.

View 2 Replies View Related

Excel 2010 :: Unmerge Cells And Fill Down Values In Newly Inserted Column

Jul 9, 2014

I am using Excel 2010 and have the problem as shown in the attached file.

Input Sheet shows the Data I have at present
Output Sheet is the desired result.

I need a macro which should create an "Output" sheet by doing the following on the Input Sheet

1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.

Please note that the number of Data Rows will vary for each Region.

I have shown two Regions for explanation purpose only. There will be several Regions in reality.

The result is shown on the Output sheet

Merge Problem - Forum.xlsx‎

View 3 Replies View Related

Excel 2013 :: Renaming Charts Keeps Reverting Names

Jul 31, 2014

I am trying to rename a chart in Excel 2013, but it keeps reverting my names. It looks like there might be a 32 character limit on the name length, but I cannot find any documentation supporting this.

In case it's not clear, I'm trying to rename a chart from Chart38 to "Productivity - Tractors CY/OP - Month", but once I press Enter, it changes back to Chart38. If I can shorten the name to less than 32 chars it seems to work, but that will be nearly impossible for some other charts.

View 3 Replies View Related

Create A Formula Which Sums The Values Associated With Several Defined Names

Aug 6, 2007

I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.

View 9 Replies View Related

Excel 2013 :: Not Loading Listbox With Sheet Names From Another Workbook

Feb 27, 2014

I've created a macro that loads a listbox with the sheet names from another workbook. It works fine with Excel 2007-2010 but some reason fails with 2013. It doesn't crash, it just doesn't add items to the listbox nor doest it add the caption to Userform1.Caption. It does launch the form. Here is the code snippet:

Code:
'open read-only
wbkpath = Sheet3.Cells(1, "f") 'full pathway to source workbook
Workbooks.Open Filename:=wbkpath, ReadOnly:=True
Set swbk = ActiveWorkbook
swbk.Activate

[Code] ...........

View 2 Replies View Related

How To Add More Data To Existing Cells Without Replacing It

May 2, 2007

need to add same data to every other existing cell in the column, but not replace the data already in it, but to add to it. I've tried to google the answer and look here, but I probably use bad search terms.

For example, I need to add "QW" after each of these lines:

data1432
data9292
data3933
data3939

so it would look like this:

data1432QW
data9292QW
data3933QW
data3939QW

I have a few thousand rows of data, so wouldn't rather not do it manually cell by cell by typing :-)

View 9 Replies View Related

Newly Created Tab Names Placed As Hyperlink On Content Sheet

May 11, 2006

I attach an example worksheet with the code I have thus far.

In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.

I then change the name of the sheet to the name of the client.

I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".

how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,

View 8 Replies View Related

Code To Copy, Paste Without Replacing The Existing Data

May 21, 2009

I have two sheets - sheet1 and sheet2. Everytime i run a macro in the sheet1and manually copy the rows from sheet1 {starting (A2:AI2)} and paste as values it in the sheet2 {starting (A2:AI2)}.

Then I ll delete the available datas in the sheet1 and will run a macro for different customer.

Again I need to repeat the same action (manually copy and paste the datas to sheet2) without replacing the existing data in the sheet1.

In simple I require a macro code where it copy the available information in sheet1 and paste it as values in the sheet2 without replacing the existing one.

View 14 Replies View Related

Excel 2013 :: Populating Column Without Duplicate Values

Jul 14, 2014

I've got a column of client company names and because it is based from accounts, the companies names appear many times. I just need to populate a new column with unique names, so I have found a formula to do this:

=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

However when I do this, I CtrlShiftEnter and drag to copy that down, and it's just a copy of the original list - client names are appearing on my new list multiple times. I don't see how this doesn't work... I'm using 2013.

View 1 Replies View Related

Excel 2013 :: Averaging Values Of Rows From Particular Column

Aug 6, 2014

Is it possible to perform average function on subsequent group of rows and make a new set of rows ?

For example: As below, in column 1 the average of values of first 3 rows (i.e, 1, 2 & 3) is 2. similarly average of values in next 3 rows (i.e, 4,5 & 6) is 5 and so on....

Is this possible to get a new set of rows by averaging values of rows from a particular column. without applying average formula in each row of column 2. i m using MS Excel 13.

Column 1 ______ Column 2
1 _____________ 2
2 ______________ 5
3 ______________ 8
4 _____________ 11
5
6
7
8
9
10
11
12

View 4 Replies View Related

Excel 2007 :: Test For Existing Values

Nov 7, 2011

I have a workbook with several tabs. the first tab is a data input sheet: subtotals from this sheet get posted to cells on the other sheets. at the top of the Data sheet is a drop down validation list that the user will select the next month from. On each of the other sheets, is a grid with calendar months down the left hand side and years across the top:

Excel 2007BCDEFGHI26Incoming111009080727Month201220112010200920082007200628
JAN1201135317522050194029FEB1392150817082130217230MAR1538164117831823246831
APR1338132717301819211632MAY1485129615762008244933JUN14991380162618652028187034
JUL15491382166020362091170335AUG16531383148619252132197236SEP15901411155620051982214937
OCT23391324159420732014247738NOV1440146718842186282139DEC15631602203721472197Incoming

I am trying to figure out a simple method to check whether there is data on any of the four other sheets in the spot corresponding to the month selected by the user on the Data tab. the format of the validation list dates are dd/mm/yyyy in cell B2 on the data tab.

View 3 Replies View Related

Excel 2013 :: Pivot Table - Adding Rows With Zero Values?

May 28, 2014

I have some nominal data that I'd like to get into a pivot table (Excel 2013). For simplicity let's say it's a one-question survey with 6 respondents:

Q1: Dogs are better than Cats
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree

Responses:
Agree
Strongly Agree
Strongly Agree
Strongly Agree
Agree
Strongly Agree

I can create a pivot table with this data and get the following:

Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Grand Total
6

This all works nicely, however I require that the other options ("Neither Agree or Disagree", "Disagree", and "Strongly Disagree") be present, even if their values are 0, like the following:

Row Labels
Count of Q1
Strongly Agree
4
Agree
2
Neither Agree or Disagree
0
Disagree
0
Strongly Disagree
0
Grand Total
6

What I tried doing was adding a new column and calling it something like Ratings with the following:

Ratings
Strongly Agree
Agree
Neither Agree or Disagree
Disagree
Strongly Disagree

Then I set the Ratings column in the "Rows" section of the pivot table and the Count of Q1 column in the Values section. This is what happened:

Row Labels
Count of Q1
Strongly Agree
1
Agree
1
Neither Agree or Disagree
1
Disagree
1
Strongly Disagree
1
Grand Total
6

View 4 Replies View Related

Excel 2013 :: Find And Replace Values In Selected Range?

Jul 13, 2014

I have Excel 2013. I would like to use the code below but I need it to let me select the range instead of specifying it in the code, then pop up a form into which I can fill in what I want it to search and what I need it replaced with.

[Code] ......

View 3 Replies View Related

Excel 2013 :: Create New Values Based On Number In One Cell?

Jul 21, 2014

I have the following data set with about 500 rows. It has one large value in column A, followed by a number between 1 and 24 in column B.

I am trying in Excel 2013 to develop a formula that will: divide A by the value in cell BPlace in cell C, D, E, F... the result of this division, where the number of times the value is repeated depends on the value in B. E.g.

Example of source values/data set:
A
B

18504
18

2011
2

23694
24

(about 500 rows more)

An example of the output result that I am looking for:
A
B
C
D
E

2011
2
1,005.5
1,005.5

In above example, the value in column B is 2, so the result of the division of value A with value B is repeated twice. In case value B would be 18, the value would be repeated 18 times...

I have been trying to do this in Excel for more than 2 hours, but I did not manage to complete it.

View 2 Replies View Related

USER DEFINED FUNCTIONS

Jan 2, 2007

Possibility of creating "on measure" functions for the necessities of every customer, but I have some things to ask in merit and I hope that you can answer to me:

- which are the differences between a User Defined Function and a Macro?
- also User Defined Function is written in Visual Basic?

View 9 Replies View Related

User Defined Functions - How To Create One

Jun 17, 2013

I have a mental block when it comes to writing functions.

In a cell I have the Post Code followed by a "#" sign (no speech marks) and sometimes more text

e.g. FY6 8JF#ABCD (where the Post Code is FY6 8JF)

The "#" sign always indicates where the Post Code ends, i.e. I want to return all the characters to the left of the "#" sign.

In a spreadsheet I would accomplish this with "=LEFT(AA14,FIND("#",AA14,1)-1)"
where the string of interest is in cell "AA14"

How do I write a UDF to do this?

View 5 Replies View Related

Combining User-defined Functions

Oct 13, 2006

Say I have written three user defined functions: first(a,b,c); second(a,b,c);third (a,b,c), which all give value for a,b,c inputs. How do I write a VBA program that uses the functions above? Say the code is called Combine(), after the user have typed in the value of a,b,c, the function Combine=first+second+third.

View 4 Replies View Related

Disable Update When Replacing Formula File Names

Jul 26, 2007

I am using find and replace to change file names and ranges in vlookup formulas. The files are on a remote server and I don't want the Update Values dialog box to display.

Can I disable this before starting the find and replace?

View 5 Replies View Related

User Defined Functions And Cell Reference

Feb 28, 2012

referencing constant cells in custom functions. This is a sample of my code:

Code:
Select Case Name
Case Is = "Ball"
Valve_Days = [V4] + Cushion
Case Is = "Check"
Valve_Days = [V5] + Cushion

Now, the problem is when I open the workbook up, or someone downloads the file from the server, it resets all fields to div/0 based on what sheet the last user 'Saved' the document on. I think this is occurring because it doesn't know which sheet to pull the value V5 from? The problem never started to happen until I added another sheet in the workbook. Is there a way to tell VB that it isn't just cell V5, but it is cell V5 on Sheet 'X'?

View 2 Replies View Related

Argument Text For User Defined Functions

Sep 9, 2008

when writing user-define functions in VBA, how do you set up the help on function arguments, so that they appear in the dialog box for the user to enter the correct argument values?

(For example, in the VBA function ACOS, yoh have to enter "Number", and the dialog box explains: "Number is the cosine of the angle you want and must be from -1 to 1." I want to do the same with my own functions).

View 3 Replies View Related

Excel 2013 :: Copy Entire Row If Cell Contains Specific Text And Paste Values Into Another Worksheet

Jun 23, 2014

I am using the code below in Excel 2013.

Sub Test()
For Each Cell In Sheets(1).Range("J:J")
If Cell.Value = "131125" Then

[Code]....

This works great except that it pastes formulas. I would like to paste values only. I've tried
" PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" and it gives me an error.

View 3 Replies View Related

Excel 2010 :: Macro To Copy 3 Cell Values And Append To Existing Database File

May 14, 2014

I'd like my macro (in an Excel 2010 workbook) to copy values from three worksheet cells and append them to an existing Access or SQL database file -- without having to leave my Excel spreadsheet or open any other programs. The database file simply needs to be appended. Keeping a running list. That file isn't being used for anything else, we can set it up however is necessary to do this.

For example, Excel cells A1, A2, and A3. Representing Job Name, Job Number and Job Total.

how make VBA do this(in SIMPLE terms)? Or give me a link of where to look or what to search for? I understand VBA but not Access or SQL.

View 1 Replies View Related

User Defined Functions And Using Application.Volatile To Update Sheet Automatically

Jan 13, 2009

I created a user defined function and using Application.Volatile to update sheet automatically.

But this slowing down my sheet execution alot.

When i remove that it doesnt take much time but no automatic update.

View 9 Replies View Related

Excel 2003 :: Stuck On User-defined Type Not Defined Error?

Oct 9, 2012

Trying to convert an Excel 2003 macro to work in Excel 2007.

The problem line is

Dim MyDataObject As DataObject

I suspect the problem is a Missing Reference, but I cannot figure out which one. I have the same ones (in 2007) as 2003 except for one which is not showing

Microsoft Forms 2.0 Object Library

Is this the one it needs? It is called something else in 2007?

The ones I do have ticked are

Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Scripting Runtime
Microsoft XML v2.6

View 8 Replies View Related

Excel 2010 :: User-Defined Type Not Defined Compile Error When Creating PowerPoint Using VBA

Jul 29, 2014

I am using Microsoft Excel 2010 and Microsoft VBA 7.0 on my system. I would like to eventually create a PowerPoint and insert charts generated in the Excel workbook. In the meantime, I cannot get the basic PowerPoint created.

The line in red is highlighted blue when the compile error "User-defined type not defined" message box appears.

Public Sub TryAgain() Dim myPowerPoint As PowerPoint.Application
'
' do nothing for now
'End Sub

I have set the references such that Microsoft Project 14.0 Object Library is indeed checked. The Excel file only contains this code in a module. All sheets are blank. Nothing else is written yet.

View 1 Replies View Related

Excel 2010 :: Win 8 User Defined Type Not Defined?

May 5, 2013

Base 64 Encoding.

Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMElement

Are causing the User Defined error, but the same workbook is OK in XP and Win 7.

VB6: Free, Easy and Quick Base64 Encoding and Decoding in Visual Basic [a NonHostile article]

Says you need a reference to Microsoft XML, v2.6 (or later) and the Win8/2010 workbook does have one for Microsoft XML, v6.0

View 1 Replies View Related

Using Defined Names In VBA?

Aug 6, 2012

Trying to use a Defined Name as the cell reference in VBA. The idea is to change the value of a couple of other cells base on the value of the cell "FreightRow". I am starting with a Message box just to make sure the code was reading the cell reference.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim FR As Range
Set FR = ThisWorkbook.Names("FreightRow").RefersTo
If FR = 3 Then
MsgBox ("Help")
End If
End Sub

View 7 Replies View Related







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