Dragging Formulas; Values Of The Formula To Change And The Other Not To Change
I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Change Date Formulas To Values
I have what I thought was a very simple task; Change all date formulas in the workbook to values. I've accomplished this, but I was wondering if someone had a better way of doing without so many loops (I used two). NOTE - Dates are on different sheets within the workbook. I've tried setting each date on each sheet to one named range. I then tried doing this range("dateranges").values = range("datranges").value this obviously didn't work. Anyhow, this is what I used that works, but I'm convinced that there is a better way. Sub FinishOU() Dim sh As Worksheet, wb1 As Workbook, r As Range, TestRange As Range With Application . ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False Set wb1 = ActiveWorkbook With wb1 .Save For Each sh In Worksheets Set TestRange = sh.Range("A1:S4") For Each r In TestRange.SpecialCells(xlCellTypeFormulas).........................
View Replies!
View Related
Dragging Formulas: Replicate A Formula In Cells
If I need to replicate a formula in some cells, I usually drag it down or sideways. Is there any easier way to do this? I have large numbers of cells into which to drag the formula. Can I specify which cell range the formula is applicable to (i.e. A1:A10,000 or something) instead of dragging? If so how?
View Replies!
View Related
Weekday Formula: When I Change The Month, It Automatically Will Change My Formula
=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") B1 = Jan C1 = 2006
View Replies!
View Related
Automatically Copy Formula Range On Change & Paste Values
I want to automate the following steps when cell A8:A11 changes in sheet "InfoAA": (1) clear contents and formats of cells A1:A4 in sheet "InfoBB" (2) copy cells A8:A11 of sheet "InfoAA" (which are formulas) and past it as text in cells A1:A4 of sheet "InfoBB". (3) then automatically run a recorded macro named "BoldFirstName" See attachment.
View Replies!
View Related
Dragging Down Formulas With Inconsistent Cells
How figure out how to drag down a formula that looks like this for the first three cells (C3:C5)? =(SUMPRODUCT(Polls!D2:D5,Polls!B2:B5))/(SUM(Polls!B2:B5)) =(SUMPRODUCT(Polls!D8:D11,Polls!B8:B11))/(SUM(Polls!B8:B11)) =(SUMPRODUCT(Polls!D14:D17,Polls!B14:B17))/(SUM(Polls!B14:B17)) ...etc. Is there a way to let Excel know that I'm adding six to each cell, rather than it automatically adding one?
View Replies!
View Related
Inserting Column And Dragging Down Formulas
I have a long list of inventory parts and there can be additions or deletions at any time. Currently the inventory is determined using formulas from other worksheets and this uses formulas. If I have a user insert a row to create a part in inventory, is there a way to make the formulas automatically be created in the cells within the row?
View Replies!
View Related
Formulas To Not Change If Cells Where Deleted From Sheet
I have a sheet (sheet2) that has cells in column L, M and N referencing cells in another sheet (sheet1)(same workbook). If I delete a row out of Sheet1 it causes the formula in Sheet2 to go to #REF. Normally the formula would be something like ='Sheet1'!L13 How can I get these formulas to not change just because cells where deleted from Sheet1?
View Replies!
View Related
Hide The Formulas & Change The Attributes
First, to i change the attributes, select the appropriate cell or range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden (or both). Unlock cells that accept user input, and lock formula and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells: The results of the formulas will be visible, but the formulas will not. Now, to protect the worksheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog box. but its hideing the formulas. once i protect the cells it wont let me to edit the worsheet (eg- cell height, cell width)
View Replies!
View Related
Formulas Change When Column/row Is Deleted. How Can I Stop That
I have a spreadsheet that holds the NHL schedule for the season. (See thumbnail) In the picture I've highlighted the formula I use to determine how many games each team has over the next 7, 14, 21 and 28 days. What I've been doing is deleting the column for each day after that day has passed. (So tomorrow, I'll delete column K, and all the other days will move up one. Sunday will become column K) My problem is that the formula =7-COUNTIF($K3:$Q3,"—") in cell G3 will read =7-COUNTIF($K3:$P3,"—") after I have deleted Saturday, and thus will only count 6 days worth. (The 14, 21 and 28 columns will only count 13, 20 and 27 as well.) Is there any way I can write this formula so that it stays as K3:Q3? Right now each day I modify the 4 formulas for the 1st team and then copy/paste then over the other 30, but this is rather tedious.
View Replies!
View Related
Change File Path In Formulas For Every Cell In A Sheet
I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another. e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.
View Replies!
View Related
How Do I Copy Formulas And Automatically Change Reference Cells
I have a 2 columns (A and B) next to each other that is using a Sum formula and other multiplying formulas. They reference 3 cells in a different column (C) to come up up with the answer for A and B. Therefore A1 and B1 reference C1 C2 and C3 (and possibly if want to use in future column D with D1 D2 D3). Then in A2 and B2 I want to reference from C4, C5, C6 Then in A3 and A4 I want to reference from C7, C78, C9 etc.... and so on... Instead of retyping formulas for each row in A and B, I want to copy down, but I getting the wrong answer when I do that. I am therefore assuming I typing the formula wrong. How do I type it correctly so the cell reference changes automatically when I pull down columns A and B? Formula for Column A: =SUM($G28:$G30) Forumla for Column B: =($G28*$H28+$G29*$H29+$G30*$H30)/$B17 Therefore next row should be referenced from G31 to G33 and H31 to H33 But when I copy it only adjusts it for 1 reference down.
View Replies!
View Related
Change The Cell Color On Drop Down Change
I have a drop down sub pasted to worksheet: Private Sub ComboBox1_Change() ComboBox1.List = Array(100, 200, 300, 400) If Range("I11").Value < Range("N11").Value Then If Sheets("Profile").Range("K18").Value < ComboBox1.Value Then Range("I11").Interior.ColorIndex = 2 Else Range("I11").Interior.ColorIndex = 3 End If End If End Sub I want it to change the cell color on drop down change. How can I modify things to have the change in drop down selection?
View Replies!
View Related
Change Event To Detect Cell Change
I have a simple bit of code that fires some code when it detects a change in cell $P$5 but it doesnt work and I cannot understand why - can anyone assist with this one? I am very green but keen: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$p$5" Then Range("D9:D81"). AutoFilter Field:=1, Criteria1:="<>" End If End Sub
View Replies!
View Related
Change All Values To Minus
I have a spreadsheet which contains values and text. Would I be able to change selected values to a minus value by clicking one button? A2 = 200 A3 = 300 A4 = 1000 B3 = 50 C5 = 600 Changes to A2 = -200 A3 = -300 A4 = -1000 B3 = -50 C5 = -600 I would also like the option to change them back again as well. I have about 40 values so do not want to do this manually.
View Replies!
View Related
Change Of Cell Values To 0
In column A I have a unique number. In Columns B,C,D,E F and G I have various data What I would like is to select a Number from Column A and the Row that contains that number Columns B,C,D,E F and G I want to change these Cell Value to 0 For example Unique number in Column A is 1052 This appears in Cell A452 The cells Values of B452,C452.D452,E452 Etc to G452 change to 0
View Replies!
View Related
Find And Change Certain Values In A List
I would like to find and change certain values in a list. For example I would like to change TG1050 to 1 from a list that contains the following values TG1050,TG10500, TG420, TG350. When I run my VBA the 2nd Item in the list changes to 1. I also need to keep the commas seperating the value.
View Replies!
View Related
Change Font Colour With Different Values
I'm creating a simple spreadsheet totalling units of alcohol up. Is it possible to change the colour of the fornt when different values are reached? For example if a total of under 8 units is reached this is displayed in green, if over 8 the figure is shown in red.
View Replies!
View Related
Permanently Change Cell Values
I have a column of values that I want to convert. The are all generally along the same lines as the below example: Values is 500000 I want to change it to 5000.00 does anyone have any idea how I can put in a decimal point to two digits before the end of the value.
View Replies!
View Related
Change Textbox Values With Scrollbar
I have a userform created with 27 textboxes that corrispond to columns A through AA. What I am trying to do is when the form is loaded, I want to have all the textboxes populate with the information accross row 2. Using a scroll bar, I want the user to be able to scroll through the entries so that when the user scrolls down, all textboxes change to the information in row 3, 4, 5 etc. Ultimatley, this is a data entry form, allowing the user to lookup and edit entries on the page. I can figure out how to do everything I need, witht he exception of the scrollbar.
View Replies!
View Related
Adjusting Percentage Values On Cell Change
Good afternoon Gentlemen, I have a column of data, with a varying number of percentage values that add up to 100%, separated by "NA", i.e. 33% 33% 33% NA 25% 25% 25% 25% NA 100% NA 20% 20% 20% 20% 20% NA Now... when I change one of the values I would like the others to even up, i.e. in the last example if I change a 20% to 50% I would like the others to change to 10%... any ideas?
View Replies!
View Related
How To Change Values By Increment In Data Table
I have built a model which aims to calculate various information/statistics based upon 2 inputs which I can change (see 'Mov_Avg_Chart' tab cells C6 and C8). In cell C6 I have identified the moving average period that I would like, and in cell C8 the period for moving average of the gradient/standard deviation of the gradient. I have created 4 data tables (one for annualized return, sharpe ratio,max drawdown, and annualized risk/reward). The first data table can be seen in cells AE15:AH18. In cells AE16:AE18, there are the moving average period inputs (as in cell C6) and in cells AF 16,AG16 and AH 16 are the moving average gradient inputs (as in cell C8). What I am trying to do is create a table that will allow me to state a minimum moving average period (as in C6) and a maximum moving average period (as in C6), and an increment which I would like to look at the data in. Thus, in this example, the minimum moving average period would be 20, the maximum would be 40 and the increment would be 10. Also, I want to create a minimum moving average gradient (as in C8) and a maximum moving average gradient (as in C8) and an increment figure. Thus, in this example, it would be minimum of 75, maximum of 125 and an increment of 25. The point in the example is to see which outcomes based on the inputs are the most favorable. I am having difficulty with setting up a minimum and maximum range and the incremental period. The trouble is, I don't know how to put these as additional inputs and have them drop into the data table (in place of cells AE16 to AE18 for example). Would anyone have any ideas? Also, the automated recalculation of the data table is needlessly slowing down the spreadsheet; is there a way that the data table can be recalculated only when there are changes to the input functions? I have set the calculation method to 'automatic except data tables' because otherwise it takes 15 minutes for the file to open. Thus, the data table isn't calculating automatically. That is, cells C6, C8 and the minimum and maximum range cells that I am seeking to create? Would someone be able to help me with this?
View Replies!
View Related
Validation List To Change Columns Values
So I've got a drop down list in cell B73 That when I change the selection I want it to copy the cells below it (B74:B94) from one of the charts above it. Through the power of Google I found this: http://www.eggheadcafe.com/conversat...eadid=29484871, someone who had the same need as me, and edited it accordingly to my needs. It however, is not working. I started with Case 1-7 and changed them thinking they need to be the same as the list but that hasn't fixed it either. File is attached, and below quote is what the VBA coding currently says.
View Replies!
View Related
Insert New Rows When Column Values Change
I've posted this thread in http://www.excelforum.com/excel-prog...ml#post2038238 but since the thread has been solved, I think nobody would look into my problem, so I'm creating a new thread for the purpose. My previous thread: Hi there, I'm looking for the same idea only that I wanted the macro to select the data in the cells selected. Based on the code given by StephenR above, I tried to do some modification but I don't know how to make it to start from the first cell of my selection, not from row 15 as R_S_6 wanted. The range selection can be in any column. Here are the code that I used:
View Replies!
View Related
Change Event Based On Two Other Cell Values
myColumnOne = Range("NPN").Column 'this is column B, NPN is a Named Range of B1 myColumnTwo = Range("NPCH").Column 'this is column E, NPCH is a Named Range of E1 using these variables I want to say When data is entered into any cell in myColumnOne first check to see if this same data already exists in myColumnOne if it does then check to see if in the row where the data already exists, if the corresponding cell in myColumnTwo ISBLANK then MsgBox if the corresponding cell in myColumnTwo is not blank, allow the data to be entered. Example: Col B….Col E ABC…..xxx XZY….........
View Replies!
View Related
3 Way Switch Or Change Values From Mulitple Places
On sheet 1 there is a dropdown box that indicates a condition. For example the choices are 1-4. On Sheet 2 calculations are made based on the condition in the drop down box. I also show the condition # on sheet 2 with a simple =sheet1!a1. What I would like is the ability to have the drop down box on both sheet 1 and sheet 2 and that if you change the value in either place then is changes in the other. Same way that you can operate a light with 2 different switches.
View Replies!
View Related
Change Events - Depends On Cell Values
I receive real time data ( Last Trade Price ) of soybean commodity futures through DDE in to excel ( cell A1 ). During market hours A1 will keep updating every milliseconds or seconds. My cell B1 ( =A1 ) will have same value as A1 and will update at the same time. What I'm looking for - A macro code so Whenever value in B1 is between 9.5000 and 9.5050, I want run a subroutine ( similar to calling a macro ). Since B1 would change dynamically so everytime B1 comes between range 9.5000 and 9.5050 a subroutine is fired. I'm planning to CALL following subroutine - Sub BuyBeanst() Range("N9").Activate ActiveCell.Value = "Y" Range("T10").Activate ActiveCell.Value = "Y" Range("T11").Activate ActiveCell.Value = "Y" End Sub
View Replies!
View Related
Insert 2 New Rows When Column A Values Change
Column A of my spreadsheet consists of Subcase 1000 to Subcase 1010 for example, with up to as many as 100 repetitions of each subcase in consecutive order. See Below: Subcase 1000 Subcase 1000 . . . Subcase 1001 Subcase 1001 . . . Subcase 1002 Subcase 1002 . . etc I would like a macro to find where Subcase 1000 finishes and Subcase 1001 begins and then insert 2 new rows between them. These 2 new rows have to be inserted between any change found in the column A field. The searching of Column A should start at Row 15. I have uploaded an example file. This file contains 2 sheets, 'Before Macro' and 'After Macro'. Hopefully it outlines what the outcome of the macro should look like.
View Replies!
View Related
Macro: Change Positive Values To Negative
I use data on a text file and import this into Excel and use a macro to format. The data lists invoices and credit notes and their dollar value. Due to a fault in the program that creates the text file all dollar amounts are shown as positive figures. Credit note amounts should correctly be shown as negative figures. The invoice number format is 8 characters beginning with a letter and credit notes begin with a number. Is there macro code that will change credit note values to a negative figure?
View Replies!
View Related
Input Boxes To Change Cell Values
I need some code to use text boxes to change the data in certain cells. Basically I want 3 text boxes to appear one after another. The first box should add text to cell T1, then 2nd to cell T2 and the 3rd will add text to cell T3. All these values should be added at numbers.
View Replies!
View Related
Change Vlookup Table Array Values
I am trying to do a vlookup where the values in the Table_Array have a string appended to them. For example: I want to look up the string "Example1" and the lookup range is Example1 - monthly Example2 - weekly Example3 - weekly Example4 - monthly So essentially I want vlookup to only look at the string before the hyphen. My initial thought was to do something like this: vlookup("Example1", left(A:A, 8), 1, FALSE)
View Replies!
View Related
Compare 2 Sets Of Data To Change The Values
I am a absolute newbie to VBA, I've got 2 timetables, they show deliveries for different countries. I need to make my code as DYNAMIC as I can. TableCustomer shows the customer's name, country and their opening times, Saturday - Friday, it uses 1s and 0s to determine if they are open or closed, 1 = open, 0 = closed, so the headers are: Customer Name, Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc. TableCompany shows the days of deliveries made to each country by the company, using 1s and 0s, 1 = Deliver, 0 = Don't deliver. The headers are Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc. An example here is that, if in TableCustomer, the customer is opened(1) on a Saturday, but in TableCompany, the company does not deliver(0) to that country on the Saturday, change that 1 into a 0 in TableCustomer. But if the Customer is closed(0) on the Saturday, then don't change the value 0, because the company will not be able to deliver to them anyway, even if they can deliver to that country on that day.
View Replies!
View Related
Change Cell Colour Based On 3 Values
My question is i want a cell to have the colours: Green=if within the max min range [as specified by user] Amber=if the current value is less than the allowable slack level[again specified] red=if the current value is less than or more than the min or max respectively.
View Replies!
View Related
Find Where Values Change From Positive To Negative
I have a series that comprises of two rows: Row 1 is date Row 2 are values I am trying to find a formula where I can identify where in row two the value chages from positive to negative, and where it changes from positive to negative it should return the date the value changes from positive to negative ie: ROW 1 08-Jan 09-Jan 10-Jan ROW 2 1111 22222 -33333 The formula should identify that in row 2 the negative value occurs at -33333 and at the 10th of January.
View Replies!
View Related
Dragging Down Formula
i have a sheet with alot of data, its a contacts list. colum A is Company name, B contact name, C address, D postcode. One row = one contact. On the next sheet i want to have on column with each contact set out as if it wer on an envelope, so i did the formula:............ if i select all of this space and try to drap the formula down the next cell fills with =sheet1!A11......I want it to say =sheet1!A3. If i cant drag it down then I am going to have to manually write this formula 100000000000 times.....anyoning.
View Replies!
View Related
Changing Values Of Cells Based On Change Of One Cell Value
I am trying to do is to write a code that will change the values of cells B17:B25 to "false" when the user selects "true" from the drop-down box in cell B16. Here 's my Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "B16" Then If Target.Value = "TRUE" Then Range("B17:B25").FormulaR1C1 = "FALSE" End If End If End Sub This is not working! Nothing happens when I select "TRUE" in cell B16!
View Replies!
View Related
Change The Row Colors Contigent Upon Cell Values
I know this can be done, as I have seen it before, but can't seem to figure out how to replicate it. I have a speadsheet in which I have a drop-down box for a certain column's values. How do I automatically have each independent row's background color change dependant upon the value selected in that row's drop-down cell box (and update color automatically if a different drop-down selection is made later)? For example: Row 1: Cell C Drop-Down Value = "Yes"...change row color to GREEN Row 2: Cell C Drop-Down Value = "No"...change row color to RED Row 3: Cell C Drop-Down Value = "Maybe"...change row color to YELLOW
View Replies!
View Related
Stale Pivot Item Values After Query Change
I have a spreadsheet with a pivot table that is based on a table within an SQL Server database. Every month a stored procedure is run creating a new table. Using the pivot table wizard I can modify the underlying query to access the new table. The pivot table functions properly with the new data. I also have a macro that I created that will cycle through all the combinations of page field pivot item values, setting the page field currentpage, do some calculations, and save the results. This is bombing because it seems that some old date values from the previous month's table are still in the pivot items and when my macro attempts to set the page field's currentpage to one of these values it generates Run-time error '1004' Unable to set the _Default property of the PivotItem class. I have tried putting a pivotcache.refresh in but that doesn't seem to help.
View Replies!
View Related
Change Worksheet Change To Macro
Is there a way to either change this so that it lets me to select the whole area or a way to make a macro to do what this does to one cell? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("M13:IR458")) Is Nothing Then Select Case Target.Value Case "1" Target.Font.ColorIndex = 20 Target.Interior.ColorIndex = 10 Case "Good" Target.Font.ColorIndex = 2 Target.Interior.ColorIndex = 35 Case "Stable" Target.Font.ColorIndex = 2 Target.Interior.ColorIndex = 27......................
View Replies!
View Related
|
TRACKER
