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?
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.
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.
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?
I have two columns listing about 400 and 500 names respectively.
First Column has names in alphabetical order and the second column is random.
One column has names in this format: Last name, First name
And the other column lists the names as first name and then last name without the comma
I am trying to see if the list of names in one column exists in the other.
Is there a way to see this without changing the format of the names in each column? If so do I have to erase the comma?
Name Lists (1).xlsx
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.
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.
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 5 Replies View RelatedI 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.
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 9 Replies View RelatedI 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,
Following code doesn't work
Dim Value1 as String
Value1 = Substitute(Cells(4, "A"), "A", "", 1)
correct syntax to assign the result of substitute function in the Variable?
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?
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.
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.
Is there a way to use something like the SUBSTITUTE function when sending an e-mail using a macro?
Here's my ....
I've got a workbook with multiple formulas e.g.:
t=S/D
TCh=Ch*(S^2/2D)*(D/Q)
TC=Co*(D/Q)+Ch*((i-D)/2i)*Q
which are built by implementing the symbols:
t, S, D, TCh, Ch, Q, TC, Co, i
I'd like to substitute the symbols in following sequence:
t = a
S = b
D = c
TCh = d
Ch = e
Q = f
TC = g
Co = h
i = x
Questions:
1) Which formula could substitute the symbols automaticly?
2) If there are up to 100 formulas in the workbook in the range of A1:E100,-
would it be possible to to set the substitution for the mentioned range?
See the attachment.
I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.
Substitute.png
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.
Adding second substitute command
What I need to do is change this:
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,"***************","**/**-**-***-****/*")
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 2 Replies View RelatedIs this possible because when i try to use the Substitute formula my Concatenate formula is doesn't work. This data in cell A1 is linked from another sheet
PHONE
WIRE
CANDY
INDIA
When I use the Concatenate formula only, it works fine but when i try to add the Substitute to this, it looks like this
PHONE WIRE CANDY INDIA
I want it to look like this, removing any blank lines within cell A1
PHONE
WIRE
CANDY
INDIA
Something wrong with the formula? how come it doesn't do both?
=SUBSTITUTE(CONCATENATE(A1),CHAR(10),"")
if i obtain an eqn by plotting a graph in excel, etc,
y = 1.22952117E-06x3 - 1.08197863E-05x2 + 9.97531712E-01x + 3.99014495E-01
and i wish to subsitube a cell value from another sheet into the 'x' in the above eqn and i need to do this 20 times.
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
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.
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.
I have a text variable MtgDate containing "25/03/2014"
I need to produce another text variable (to build into a file name) MtgDate1 with the text "2014-03-25"
I was going to use the Substitute function to replace the "/" with a "-" then Mid to juggle the dd-mm-yyyy to yyyy-mm-dd but at the moment I can't even find the right syntax for the Substitute.
Got as far as:
[Code] .....
but this just sets MtgDate1 as "=Substitute(MtgDate, " / ", " - ") ie reduces the double quotes to single ones.
In trying to create a formula that remove EVERY symbol from a cell (C6) and replacing it with a space.... the "SUBSTITUTE" function is telling me I have too many nested Substitutes.
This is the formula:
Code:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(C6,"/"," "),"%"," "),"!"," "),","," "),"*"," "),"-"," "),"("," "),")"," ")
It works thus far, but I would still like to add more symbols into the nest. Is there a way/formula to do this that replaces ANY symbol with a space?
I have the formula in cell A2 =average(A100:A1100). However the range is not always the same so need a formula that will allow me to enter '200' in cell B2 and that will make the formulae in cell A2 calculate =average(A200:A1100). Likewise, if '300' is entered in cell B2 the formula in A2 will calculate =average(A300:A1100).
View 8 Replies View Related