Replace A Formula With It's Value - Do I Need To Macro?
Oct 10, 2008
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?
I 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 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.
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......
This worksheet has around 40,000 records, and I need to create a Macro that will ask the user for a date, and when the user enters a date the macro goes through column A and finds all of the matching dates and then replaces the numbers (along with the formulas) in the corresponding Add Returns cells (column E) with 0's.
I have only been able to create an inputbox, but I am completely lost regarding the criteria due to being very new to VBA.
Sub ClearData() Dim EnterDate As Date Dim msg As String
The example Spreadsheet shows the current method of Ascending or Descending Data according to Macro. Is there a way to replace the current Ascending or Descending macro code with a formula for the required cell groups only?
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 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.
*****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 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.
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 "".
I 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.
So 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:
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
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.
'=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?
