i have a string "251423,a" in cell A2. The formula in cell C2=A2. except for in C2 i want the value to read "251423.a" instead of the ",." the string is not always the same length. so really what i need to replace the ",' with a ".' in a formula.
View 5 RepliesHere is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.
i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6
this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads
I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
I'm looking for a formula or criteria within a formula to prevent a cell from recalculating or just causing it to revert to a value if another cell becomes populated. Something like =IF(NOT(ISBLANK(xx)),STOPCALC(xx),...
I'm trying to determine some weighted week to week projections and I want to be able to determine the accuracy of the projections without them being influenced by the actuals once they show up (the projections use historical actuals and related data which are all in the same range where the futures will come in). Also hoping for a shortcut to do what I described without having to manually go in and stop the (thousands of) cells from recalculating since the actuals for a given periodicity arrive staggered and not at one specific time.
i am already using a formula in which i need to add a condition.
Existing formula - =IF(ISNUMBER(MATCH(K4,{"Cancelled","On Hold","Onboarded","Open","Profile Available","Profile shared","Rebadge"},0)),IF($AG$1+X4>T4,$AG$1+X4,T4),R4)
i need to replace R4 (at the end of the formula) with this condition - if (R4>T4),R4,T4
I want to replace a part of a formula with its value.
eg say I have a formula =vlookup($a55,$a$2:$e$50,column(),false).
I want to replace column() with its value. There will be plenty of cells with this formula What can be the best way of achieving this.
I have a spreadsheet with 2 columns (let's pretend), the first column uses Data Validation for a dropdown list. The second column contains a formula that references the 1st column and uses lookup:
=IF(A35<>0;LOOKUP(A35;NewCons;NewGroups))
So as soon as a value is selected in column A, column B changes to display a value.
Rather than just displaying this value, I would like the cell in column B to be completely replaced by this value, so goodbye to the formula.
Is there a way that I can do this? Will I need to go and study how to use macros?
He works at state tax administration. He had this formula at work to calculate income tax.
View 3 Replies View RelatedI have been having a problem with the worksheet (attached) and was wondering if anyone could give me any help.
I will explain the purpose of the sheet etc first and what it does at the moment:
This work sheet is to record peoples projects, hours related to those projects, and calculate their availability 37.5-(project hours+pto) for Belfast and dublin and 40-(project hours+pto) for chicago, and also their utilization which is =((37.5-Availability)/37.5)*100 for belfast and Dublin and ((40-Availability)/40)*100 for Chicago.
When a project is added I am currently going in and updating the formula for that person to incorporate the new row which their project is on, this is proving to be quite time consuming, and I was wondering if there were any macro’s that could be used so that it would search for a name on the first column and use each of the hours that are in the column for the given week to calculate the availability?
The utilization is not a big problem as it just uses the availability so as long as the availability is correct then the utilization is correct..
If there is not a macro do you know of any way this can be automated so that I do not manually have to add to formula for each cell when a new row is added with a project and hours?
I am trying to modify a cell formula with the Replace function.
Code:
The Formula in the target cell =
='C:Documents and SettingsHoliday macro[Holiday2011.xls]Year 2011'!C5
I am using the below code but it returns just the end portion ='Year 2012'!C5
where I expect just the year to change
='C:Documents and SettingsHoliday macro[Holiday2012.xls]Year 2012'!C5
Where last year = 2011
Yr=2012
Range("C3").Formula = Replace(Range("C3").Formula, LastYr, Yr)
I really would like to alter about 1000 lines of formulas. Here's a sample of the code.
Code:
=IF(COUNTBLANK($AE$6:$AE$7),"",MIN(U7,AD289*$AE$37))
=IF(COUNTBLANK($AE$6:$AE$7),"",MIN(U8,AD290*$AE$37))
=IF(COUNTBLANK($AE$6:$AE$7),"",MIN(U9,AD291*$AE$37))
=IF(COUNTBLANK($AE$6:$AE$7),"",MIN(U10,AD292*$AE$37))
[Code] ....
*****The last line of code is below*****
=IF(COUNTBLANK($AE$6:$AE$7),"",MIN(U966,AD1248*$AE$37))
What I'm really trying to do is replace all the numbers (well, max of 3) after the U part to U6.
So,, for example ,, if in the code it shows MIN(U16,,,,,,,, I want it to show MIN(U6
So as an example
Code:
=IF(COUNTBLANK($AE$6:$AE$7),"",MIN(U966,AD1248*$AE$37))
WILL NOW BE
=IF(COUNTBLANK($AE$6:$AE$7),"",MIN(U6,AD1248*$AE$37))
So U6 is in all the formulas.
I've tried searching on google,, saw a video here on UTube;
Excel Magic Trick 519: SUBSTITUTE & REPLACE Functions
[URL] ........
Showed some great stuff,, but I just didn't quite get it for the above.
I would like to know if it is possible to complete a "Find and Replace" but replace with a formula as opposed to a value?
View 9 Replies View RelatedI am looking for the macro which can replace cell value with his first row. e.g. Column D contains the cell value "X" and i want to replace that value with D1. The same i am looking for all field.
View 5 Replies View RelatedI have a formula copied across my worksheet to reference relative cells as below:
Code:
{=(SUM(IF('Std Days'!$D$3:$BO$3=Days!P$5,IF('Std Days'!$B$4:$B$65=$N7,IF(P$6>($L7-DAY(DATE(YEAR(P$5),MONTH(P$5)+1,))),IF(P$5
I need a formula in i.e F3 ... that will check cell G3 to see if there is any values in this cell... If yes... I need the values in G3 to be replaced with "1year" if false .. then "".
View 9 Replies View RelatedI have text in column 1 and need a formula that will replace it with text in column 2, as shown in (fig 2).
FIG 112X Jan 2009 7.5000 put X Jan 2010 5.0000 callAA Jan 2010 17.5000 call AA Jan 2009 9.0000 putIBM Jan 2010 130.0000 call IBM Jan 2009 17.5000 put PLCE Jan 2009 30.0000 call PLCE Feb 2010 7.5000 put
FIG 212X Jan 2009 7.5000 put X Jan 09 7.50 putX Jan 2010 5.0000 callX Jan 10 5 callAA Jan 2010 17.5000 call AA Jan 10 17.50 call AA Jan 2009 9.0000 putAA Jan 09 9 putIBM Jan 2010 130.0000 call IBM Jan 10 130 call IBM Jan 2009 17.5000 put IBM Jan 09 17.50 putPLCE Jan 2009 30.0000 call PLCE Jan 09 30 call PLCE Feb 2010 7.5000 putPLCE Feb 10 7.50 put
how i can search #N/A , #values error in a work sheet and replace with 0. i tried with find and replace dialague but it seems not recognizing error values.
View 2 Replies View RelatedSo I have this formula to basically lookup the letters in column L and if certain letters exist, then change those letters to another letter. Here is my Formula:
=IF(L1="A",IF(ISNUMBER(MATCH("B",$L$1:$L$200,0)),"B",L1),IF(L1="D",IF(ISNUMBER(MATCH("E",$L$1:$L$200,0)),"E",L1),
IF(L1="L",IF(ISNUMBER(MATCH("L",$L$1:$L$200,0)),"O",L2),IF(L2="F",IF(ISNUMBER(MATCH("K",$L$1:$L$200,0)),"K",L1),L1))))
For example: if column L looks like
A
B
A
E
D
Then it would convert the A's to B's since there is a B present and D's to E's, but if the B and E hadn't been in the equation then it would leave the A's and D's as they are. WIth this formula the output looks like:
B
B
B
E
E
My problem with this formula is that it only recognizes the first letter in each column. Sometimes I have multiple letters in a column and I need it to recognize them and change accordingly.
For example:
A
D_B
E
I would like to have it change to
B
E_B
E
I have attached a sample workbook that may make it more clear... If a VBA code would work better then I would be open to that as well. Combining question.xlsx
I need to replace A1*FX! with A1/FX! within thousands of formulas (and many variants of A1)
Unfortunately whenever I attempt this is deletes the A1 (or whatever cell is being referenced)
How to stop find replace doing this?
In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.
View 10 Replies View RelatedI need to replace part of a formula. I need to delete the workbook reference: "[Combined Flow2.xlsm]". I need to do this for range A1:Z100 and this is what I record when deleting them one at a time:
[Code] ........
How can I do this to delete it from all worksheets for the range A1:Z100 besides one at a time.
How do you replace a string in a Formula within a cell or several cells?
For example:
=SUM(Sales[Monthly])
find and replace Monthly with Quarterly
I have a formula like
'=vlookup(Test,A$2:B$10,2)' where "Test" is a defined name based on a formula to a data connection (PowerPivot).
I need a macro to do something like f9 on just "Test" everywhere "Test" exists or convert "Test" to something like "test".value with out affecting the rest of the Vlookup formula. In some areas I am also using index match combos.
I basically want to find and replace certain characters in a formula, but the formula is quite intricate so I will give some background to the problem first:
I have a complex spread sheet which is used to check if a data array matches another data array (both data arrays are different sizes). One array has a set of parameters for a variable and the other array has the variables and the actual values for each variable. I have a section with the following formulas to check if the actual value (C121&D121) matches the parameters ($A$9:$A$108, $B$9:$B$108) & ($C$9:$C$108, $D$9:$D$108)
I have the following formula in cell AB121:
=(C121=INDEX($B$9:$B$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
In cell AC121:
=(D121=INDEX($D$9:$D$108,ROW(101:101)-INT(ROW(100:100)/$D$1)*$D$1))
And have similar formulas from AD121 to AY121. I mention this to point out that excel regards this as an inconsistent formula, I haven't copied and pasted it over, I have had to re-enter it in each cell adjusting the parameter sections.
The problem I have is that currently this is set up to check through a data set and wrap around every 100 rows ('100' Entered in $D$1) but I want to adjust the formula when there is a different amount of rows to check. So basically the only way I can think to be able to do this is to have a formula or something in VBA (which I have very low level experience with) to be able to go into the formula and change certain characters within the formula. For example, if I wanted to check 95 rows instead of 100, it would need to have 95 in $D$1, which I enter in manually, but then how to go in and replace (101:101)-INT(ROW(100:100) with (96:96)-INT(ROW(95:95) in 2 sections in the 1 formula then do the same with the inconsistent formula from AC121:AY121?
I'm trying to create a macro to replace a link on a formula.
The formula is as below
='[Players Report 31121999.xls]Player1'!$AK$23
Where 31121999 is the date. there's a workbook everyday with different sheets for every player
I want to have a macro that gets the date I inputed in another cell and changes the date in the formula I displayed above (just changing the date).
I am looking for a formula for a cell that will do the following:
from :
robert johnson hot dog
To this
robert+johnson+hot+dog
I would like it only to change the space between the words to a + sign.
I've got a problem with my workbook.
It gets datas from another workbooks and contains cells with formulas like these:
='C:YEAR[2006-ab.xls]'!A12
='C:YEAR[2006-bc.xls]'!C18
et.c.
Is it possible to write a code in VBA to replace a part of formulas above?
I have created a workbook that generates new worksheets with unique identifiers as tab names. I.e. On a "Create New Sheet" worksheet, input three pieces of data, click "Create New Sheet" and this data is applied to a template sheet, macro continues running to copy the template and rename is with a unique SKU.
My problem is as follows: I have yet to figure out how to generate an Index page that is updated as each new sheet is added with the following:
a) Cell containing the SKU and a hyperlink (or some other link) to that particular tab, and
b) Beside this cell, pull the contents of certain cells from that SKU's tab (such as description, but also the contents of calculated fields on that SKU's tab like current quantity and current price).
I would like the Index to be live so that if data is changed on the SKU's tab, it flows through to the Index......
Wrote this snippet to clean a sheet but if Chr(34) or Chr(60) are in sequence or before the others it errors out (Runtime 1004 "The formula contains an error"). However, it they are last as in the example code, it runs fine, finds them and replaces them.
BTW: Chr(34) is " and Chr(60) is <
Special characters ?, ~ and * are handled as proscribed by Excel help, ie: ~?, ~~. and ~*
So I also tried Chr(34) ,"~"" and """", Chr(60), "~<" and "<"
All other chars seem fine. Just curious...
Option Explicit
Sub MrKlean()
Dim r As Long
Dim ws As Worksheet
Set ws = ActiveSheet
With ws. Cells
'keep <sp> Chr(32)
'Chr(33)......................
I am using the command =vlookup(a1,Sheet1!a1:z50,2) but if the cell is blank it return 0 how do i change the formula so it return the text "none" instead of 0?
View 3 Replies View Related