Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Substitute Names In Existing Formulas


I've got a bunch of formulas with cell references in them.

I've gone back and assigned names to those cells.

Is there a quick and easy way to update all the formulas with the cell names now that I've assigned them?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Add IF Statement To EXISTING Formulas Over Specified Range
I need a script that will search through a selected range of cells and add a simple IF statement.

For simplicity, lets assume the desired range is Q36:Q40. (The range is much larger than that.)

The existing formula is:=IF($Z37="%Sales",Q$9*$Y37,IF($Z37="%BS",Q$78*$Y37,IF($Z37="%YOY",O37*(1+$Y37))))

I would like to keep all existing forumulas, just tack on an IF statement before hand that says if a certain cell (Q2) reads "CoPrep" then do nothing, otherwise use the existing formula. I envision this as =IF(Q2="CoPrep","",IF($Z37="%Sales",Q$9*$Y37,IF($Z37="%BS",Q$78*$Y37,IF($Z37="%YOY",O37*(1+$Y37)))))

FYI, the background to this problem is that I am in the process of making a financial forecasting model that allows for choices of company prepared forecasts, hence "CoPrep", or model forecasts based off of other critera, both of which can be sensitized.

View Replies!   View Related
Defined Names Referring To Nonexistent Range Won't Pick Up Once Existing
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 Replies!   View Related
Tab Names In Formulas...drag And Paste?
I have a workbook which contains 12 sheets, 1 for each month of the year. I also have over 100 sheets, 1 for each agent that covers their stats for each month of the year based on some of the cells from the monthly sheets.

I am trying to drag the formulas over but it will always just refer to January, instead of January and then next cell refers to the February tab, etc, etc.


View Replies!   View Related
Formulas To Convert Personal Names To Different Formats.
I have a names in column A starting with row 7 as follows:

Walden, Douglas E
Haden, Michael
Wilson, Matt David

I need help with coming up with 3 formulas:

(1) That returns the First, Middle Initial, & Last Name in column P of the same row:
Cell P7 Douglas E Walden
Cell P8 Michael Haden
Cell P9 Matt David Wilson

(2) That returns the First & Middle Initial in colum Q of the same row:
Cell Q7 Douglas E
Cell Q8 Michael
Cell Q9 Matt D

However, I am so new at this, the simpler the formula the better because I want to try & understand it, not just learn it.

View Replies!   View Related
VBA To Delete Formulas, Names, & Macros Of Another Workbook, But To Keep Values
I want to delete names, formulas, macros of another workbook. For example, A.xls is my codes workbook. From this file, I want to delete the names, formulas, macros (but to keep the values & formatting in tact) of another workbook (there must be a prompt for which .xls file, the names etc. to be deleted). All files are in a same folder.

View Replies!   View Related
Conditional Formulas-to Get A Spreadsheet To Automate Calculations Of Unit Costs Based On Variable Packaging Names
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.

I have a series of packages that are denoted by text phrases. Examples:

4/6/12
2/12/12
6/4/12
18/12
24/12

For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).

I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.

View Replies!   View Related
Replace Range Names In Formulas With Range References
I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?

View Replies!   View Related
Substitute Apostrophe
I have a formula who examines if they are the same. The last number at first quote is not a problem (the 45). The problem occurs only and allways when the horse's name have an apostrophe " ' ". In first example apostrophe is different from the usual. If i manually delay it and replace it - type keyboards apostrophe everything works fine. Obviusly its a symbol. I want a small macro for replacing all these symbols at, lets say column B ,where these names are located. How can i use substitute at this case?


View Replies!   View Related
Is There A Substitute Character That Can Be Used
I have a whole bunch of dates in a column that look like this:

5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.

*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.

View Replies!   View Related
Mid Substitute Formula
I have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three.

I am attaching the spreadsheet,

View Replies!   View Related
Substitute For Filesearch
I understand that Microsoft left Filesearch out of VBA for Office 2007 because it was buggy. Sadly, however, I still need it. It would help if they placed a comment in VBA help that stated that it was discontinued. As it is, they make it look as if Filesearch is still available for use. OK, I'll stop ranting now.

After reading several earlier posts, apparently I have to use the Dir function. I learn visually and cannot figure out how to use the function from the VBA help file since there are no examples. Does anyone have an example of code using the Dir function to insert filenames in an array? Once I see the code, I'm sure I can adapt it to suit my needs.

View Replies!   View Related
=SUBSTITUTE In Email
Is there a way to use something like the SUBSTITUTE function when sending an e-mail using a macro?

Here's my ....

View Replies!   View Related
Using Substitute When There Is Text Followed By N Digits
I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".

I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)

I understand how to use IF and substitute. its the 7 numbers part i am stuck on.

I could do it in access with the wildcards but excel is different.

View Replies!   View Related
Substitute Error Values
I've got a small problem using VLOOKUP. If the function does not find a value, it returns #N/A. I would like it to say "Not Installed" instead.

The same goes for when VLOOKUP returns a zero - I would like it to return "Installed" instead. Since I've got a great number of rows, I would need an efficent way to change this.


View Replies!   View Related
Fuzzy Substitute ABCD - AC = BD
MY DATA
LIST1 "long" strings
LIST2 strings with characters from LIST1: the order may differ

NEEDED
the expected result is the "substraction"
all characters form LIST1 which are NOT in LIST2 should appear in LIST3

COMMENT
to make it easier to read, I have put the same string in entire LIST1

EXAMPLE
LIST1LIST2LIST3ABCDABCDABCDBCADABCDCBADABCDACBDABCDADBCABCDABCDABCDADCA

View Replies!   View Related
Adding Second Substitute Command
Adding second substitute command
What I need to do is change this:

View Replies!   View Related
Nested IF: Substitute Three Variables
I can choose among three variables in cell A1. They are named P10, P50 and P90. I want to substitute them with 1,2 and 3 in cell B1. Is it possible to do this by using the substitute-function or do I have to use a nested IF.

View Replies!   View Related
Wildcards In Substitute Function
I have numeric values in approx 1000 cell entries that I need to edit:

example cell C1 contains the value 00100300308W400

I'm trying to edit this entry to show 00/10-03-003-08W4/0.

I've tried using wildcards with the SUBSTITUTE function:
=SUBSTITUTE(C1,"***************","**/**-**-***-****/*")

View Replies!   View Related
SUBSTITUTE Function To Replace Commas
I m using SUBSTITUTE function to replace commas with fullstop so I can multiply the end result with a number. But when I try to multiple for example B2 (0.1831) with 5, i get the VALUE! error.

Value Real Value (after substitute function)
0,1831 0.1831
23,3333 23.3333
12,5199 12.5199
5,5000 5.5000
20,5999 20.5999
24,4671 24.4671
200,0000200.0000
2,5386 2.5386
0,4000 0.4000
1,5019 1.5019

how I can resolve this so I can use the real values for computations (eg Real value *5), without having the VALUE! error message.

View Replies!   View Related
Substitute Function With Find, Mid & Left
Got the following formula:-

=LEFT(L5, FIND("(",L5)-1)

I need to add the SUBSTITUTE function to this but can't figure out where it goes if somebody could point me in the right direction please? My substitute formula is SUBSTITUTE(L5,"car","train").

View Replies!   View Related
Substitute Function Non-case Sensitive
is it possible to make a SUBSTITUTE finction non- case sensitive?

For example I want to replace all letters "e" and "E" in a cell.

View Replies!   View Related
Replace Or Substitute Inverted Commas With Nothing
I have a problem to achieve the above subject, as per the attached picture.

View Replies!   View Related
7 Nested Functions Alternative - Substitute Function
I want to substitute the following "special" characters for an underscore. i need to do 9 different characters!!! is there another way?

as you are limited to 7!!!

excel 2003!

=INDIRECT("_animal_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))

View Replies!   View Related
Create Array Of File Names/sheet Names
Two part question:

1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?

2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).

View Replies!   View Related
Looking Names In A List With Names Written Differently And With Duplicates
I am using Excel 2003 and Windows XP.

I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:

Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.

What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.

I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was

=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)

So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.

My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.

Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?

View Replies!   View Related
Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

View Replies!   View Related
Folder Names Instead Of File Names/macro
I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________

Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub

View Replies!   View Related
Pulling Out Single Names From A String Of Names
I have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?

View Replies!   View Related
Replace Bad Names From A List Of Good Names
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?

The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.

View Replies!   View Related
Paste Formulas As Values (strip Out Unwanted Formulas)
I have a macro running this code to strip out unwanted formulas and formatting.

Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False

Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2

'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If

Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................

A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.

View Replies!   View Related
Substitute Words In Place Of "x"

=if(a1="x",1,0)

If a1 is blank, excel returns 1? Obviously, it does not equal "x" if it's blank.
If I change the formula to: =IF(a1=" ",0, IF(a1="x",1,0)), then it works. I don't see the differance?

I'm looking to substitute words in place of "x".

View Replies!   View Related
Compare 1st X Letters Of Names To Other Names
Here's what I'm trying to do:

In a spreadsheet I have a series of names with associated data, for instance: ...

View Replies!   View Related
Convert Formulas In Text Into Formulas
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?


View Replies!   View Related
Using Two IF Formulas (3 Or More To Count If Other IF Formulas Are Actually Returning A Value)
I have a spreadhseet with various functions on it and what I am trying to do is this.

Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.

What I am trying to do is get cell F4 to return certain arguments.

E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"

I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")

Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")

If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"

Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?

View Replies!   View Related
Formulas Won't Calculate. Just Displaying As Formulas
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.

View Replies!   View Related
Using The "substitute" Function
I want to confine the results of a table to one single cell but also want to include “hard returns” within the formula otherwise when I go to print, it won’t fit on the one page.

Data table is as follows:

A1 = 10
B1 = 10
C1 = empty cell
D1 = empty cell
E1 = 30
F1 = 30

Formula below includes one “hard return” for each cell:

View Replies!   View Related
How To Overwrite An Existing Row
I was trying to use Sheet1 (data input sheet) to collect 33 values and write them to Sheet2 as a row. I wanted the user to be able to make changes/corrections. So if the set of data with same date and shift number is entered for a second time, my macro should be able to over-write the existing row in Sheet2. Have been digging for days yet with no avail.

I'm using Win XP pro and Excel 2003.

View Replies!   View Related
Increase Its Existing Value With 1
I would like to add Cells(18,3) also along with the below code which should increase its existing value with 1.

Sub newinv()
Range("C20").ClearContents
R = Sheets("Sheet2").Range("B65536").End(xlUp).Value + 1
Cells(15, 4) = R
End Sub
Jane.

View Replies!   View Related
Automatically Add One Day To An Existing Day
how can i automatically add one day to an existing day..
a
1 Monday

in b1 what would be the formula for tuesday..

=a1+1

?

View Replies!   View Related
Existing Values
I have a button setup with a macro to copy cells in a column and paste them into a row on another sheet. One of the values in the column needs to be checked in the other sheet before being added.

After the button with the macro is clicked
- If the value entered in column A (sheet 1) exists in column A (sheet 2) proceed with the rest of code.

- If the value entered in column A (sheet 1) does not exists in column A (sheet 2), Popup menu to enter details of value (just a menu with an input box and instructions), then the macro should enter the details of value (entered by user into the popup menu) in column B of sheet 3, then proceed with the rest of code.

I posted this with a different explanation before and didn't get a response so I am hoping that this is a thinned down version of my request and is more specific. Please let me know if you need more info.

View Replies!   View Related
Non Existing Date To 0
I have date in column D and have a formula of

View Replies!   View Related
Add Sum To Existing Vba Module
I have an existing module that queries a SQL database and populates a worksheet using VBA. I would like to add to this module to include a sum of columns, but as this sheet is always dynamic, i am not sure how to sum this appropriately. for example, I have column B, I would like to add the rows from a certain point in the worksheet, but this is always dynamic, is there a way to accomodate for this so that I am always summing the column in the correct place?

View Replies!   View Related
Find New And Existing Entries
I have a list of new payments received (sheet 2) along with their agreement numbers which I want to match to the list I had last month (sheet 1) to see which agreements are still paying, which have stopped and if there are any new ones.

The problem is that the the list of new payments received shows the agreement numbers with random prefixes and suffixes so my lookup returns #N/A

Sheet 1 - 24020130
Sheet 2 = 24020130FLE

My basic thinking (and I mean basic) is to do an edit/replace on sheet 2 entries and replace the FLE's with with nothing thus removing them completely then running the lookup but that doesn't tell me which are new payments received.

Then I could make sheet 1 text blue, sheet 2 text red, combine them, re-sort and manually go through the list but I'm hoping there's a sexy formula just waiting to be unleashed.


View Replies!   View Related
How To Add Formula To Existing Macro
I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.

View Replies!   View Related
Adding Two Rows After Every Existing Row
Is there any way to automatically add two new rows after every existing row containing data?

For example

1 A B C
2 D E F
3 G H I

turn into

1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I

View Replies!   View Related
Check For Existing Data
I have the following macro which:

- opens a database checks each sheet in the database for a contract number,

- highlights the row in which it finds the contract and updates the database with claim number etc (not important)

- returns information to another sheet from the database

The macro works fine BUT I have recently realised that if the contract has already been paid out it will simple overwrite the information and I will not be aware that the contract had already been paid out.

I need the macro to return a value of "Already claimed" instead of "Found in Database" only if the contract number has already been updated.

Sub DataBaseCheck()
' Designed to check Database for claims issued each month
' Open Data Base file to facilitate matching process.
' Check for matching data
' Return checking data to spreadsheet & Update Database

Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Dim Myvalue As String
Dim Myrange As Range
Dim Tcell As Range

View Replies!   View Related
Save .txt To .xls Using Existing Name And Path
how do i save a text file to xls in code. i want the new .xls file to appear alongside the original .txt file.

View Replies!   View Related
Calling Non-existing Sheets
Currently I'm trying to set up a table that referneces sheets that don't yet exist. These sheets will be added by the user or by macro, either one. I want my table to automatically take these pages into account when they're created without having to rewrite my formulas. The problem is that when I make the call to a sheet that's not there, it opens a file open dialogue box and I don't want it to do that. Is there something I can do here about that?

I don't think it's really going to shed any light on the subject, but this is my call (from memory, not copy and paste)

IF(OR(ISERROR('P01'!N2),'P01'!N2 = ""),"",'P01'!N2)

Also, when I enter this formula and the sheet doesn't exist, it automatically reverts to a text field and I have to manually switch it back to general in which case it gives a #REF error (which is just fine).

Also, I'm writing it in Excel 2003, but it completely crashes Excel '97 if I try to open it there, how can I fix this?

View Replies!   View Related
Reference Existing Cells
I have a rather extensive, for me that is, excel report of daily stats with rows for daily, weekly totals, monthly totals. All have various formulas along the way for various needs - some are simple sums, some are averages, some are percentages.

I need to make some new reports for various purposes that pull the existing information/calculations over into another sheet(s).

For instance - I want to do a report for *just* the week totals so I can do more analyzinig, but I don't know how to pull the already calculated info over so if it ever changes (as it sometimes does) - then my whole dataset reflects the change.

View Replies!   View Related
Open An Existing Workbook
I tried to open an existing Excel workbook, it tells me that it is already open. There is nothing on the screen. I closed Excel (using 2003 version) and restarted computer also. I tried to open the file again - nothing shows up on the screen. When I attempt to open the file again I get a message that says "(file name).xls is already open.

Reopening will cause any changes you made to be discarded. Do you want to reopen (file name).xls?". Even if I reopen the file, it still does not show up on the screen.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved