# Dragging Formulas; Values Of The Formula To Change And The Other Not To Change

Jan 29, 2010

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.

## Change Date Formulas To Values

Feb 14, 2007

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).........................

## Change The Column When Dragging?

Apr 6, 2014

How can I have it so the column (letter) changes in succession rather than the row (number)? Here is my formula :

=SUMPRODUCT(('Web Queries Reference'!A\$2:A\$600<>-1)*('Web Queries Reference'!A\$2:A\$600))-0.01

I would like it to be this once I drag the cell formula down to the next row :

=SUMPRODUCT(('Web Queries Reference'!B\$2:B\$600<>-1)*('Web Queries Reference'!B\$2:B\$600))-0.01

## Change Column Letter When Dragging Down

Oct 28, 2013

I have a formula in cell C2, =Summary!B2

I want the formula in cell C3 to be =Summary!C2

Then the formula in cell C4 to be =Summary!D2

etc etc.I need to do this for 43 rows so just want to drag the formula and the column number rather than the row number change.

## How To Change Reference Cells Specific Amount When Dragging

Aug 14, 2014

So I'm dragging my formula down one cell and as expected the reference cell changes the same amount when I really want the reference to shift Down that column 11 cell so when I take my formula

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L5:L11)))

when I drag it down one cell the reference should look like this

=IF(ISERROR(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)),"",(LOOKUP(21474836472147483647,'Revenue Book'!L16:L22)))

## Change One Variable When Dragging Down A Column To Auto-complete?

Feb 25, 2013

How do you drag down a column to auto complete the values but do it so it only changes one variable.For example, I am dragging down =IF(ROW()<=\$B\$5,MOD(E2*B1,B2),"") down my column.

However, I only want the "E2" value to change, and I want B1 and B2 to stay that way. Whenever I drag down, it keeps changing all three values. Is there a way to make it just so that the E2 value is the only one that changes?

## Dragging Formulas: Replicate A Formula In Cells

Feb 11, 2009

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?

## Change Value Of Combobox And Automatically Change Values Of Other Comboboxes

Aug 13, 2012

I'm coding a userform where there are some comboxes which are popolated by values coming from Sheet2. Up to now I work it out (maybe its not elegant but it works).

Now I would like that when the user selects one combobox the values of the other comboboxes are set accordingly to the grid in Sheet2.

## Worksheet Change Event Triggered By Formula Change?

Dec 6, 2011

Basically the situation I have is Sheet2 has many references to cells in Sheet1. Sheet2 is for all intents and purposes a kind of nicely formatted report form, and Sheet1 is the input form.

My ultimate goal is to automatically resize row heights on Sheet2 when cell contents change on Sheet2.

Using a worksheet_change event isn't working I presume because it doesn't see the formula output change as a worksheet change, the worksheet_change is firing only when the input is changed in Sheet1.

how can I capture these formula output changes on Sheet2 (triggered from input on Sheet1) OR is there a way of making a particular sheets rows always adjust in height to best fit?

## Weekday Formula: When I Change The Month, It Automatically Will Change My Formula

May 9, 2006

=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

## Leaving Blank Cells Blank In Dragging Formula Combining Different Formulas In One Cell?

Aug 2, 2014

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

## Automatically Copy Formula Range On Change & Paste Values

Oct 27, 2008

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.

## Change Cell Value And Not Change Formula

Jan 27, 2007

I want to edit a cell's value without changing the formula the cell contains.

## Hide The Formulas & Change The Attributes

Sep 4, 2009

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)

## Change Font For Cells With Formulas?

May 20, 2012

I am looking for the best way to change the font of cells in a Worksheet that contain a formula. I have used Go To-Special-Formulas but have one slight problem with this method. It highlights all cells that contain an "=" sign. Some cells may be linked (ie cell A2 may say =cell A1). How would I change the font only for cells with a Function such as Sum or Vlookup and not for cells that simply link to another cell?

## How To Skip Columns When Dragging Formulas

Dec 3, 2013

I have 2 sheets in my excel document. The first sheet (sheet1) has student data (grade they got on a test) from C5:C35 all in 1 column. On the 'second' sheet (sheet3), I have made a profile for each student (there are 30 of them, spread out 1 per printable page). I want to take Sheet1!C5 result and put it in sheet3!D7. The formula in sheet3!D7 would just be =sheet1!C5. I want to 'drag' that formula to L7, then to T7 then to AB7 etc etc (they are all 8 columns apart), obviously not affecting any of the cells between them.

There are 12 pieces of data in sheet1 that I want to auto update in sheet3, for 30 students, so copy pasting, and adjusting the formula would take a long time.

## Dragging Down Formulas With Inconsistent Cells

Jun 11, 2008

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?

## Dragging Down Formulas To Last Row In Reference To Next Column

Apr 10, 2014

I would like to drag down the formula from the last cell in Column A in reference to the last row in column B.

E.g My last data in Column A104 (code to select the last cell and this can vary from time to time the report is updated)
Column B has data till B142. I want to drag A104(contains formulas) down to A142.

Here is what I have but Selection Autofill doesn't work.

Range("A2").End(xlToRight).Select
Range("A2").End(xlDown).Select
Selection.AutoFill Destination:=Range("A3:A" & Cells(Rows.Count, "B").End(xlUp).Row), Type:=xlFillDefault

## Inserting Column And Dragging Down Formulas

Jul 5, 2006

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?

## How To Change Conditional Formatting With Formulas Concerning Dates

Jan 19, 2014

In my workbook I have several column with dates, these are benchmarks that I need to follow and have my patient's follow. They concern the dates of treatments and of lab work. My last column is Contact Needed and does not contain any formulas or code. Is there a way to change the color of the row to adjust for how close we are to the treatment date or the lab draw date.

I was thinking if I were +/- 7 days from each blood draw the row would be yellow, so I could contact the patient and remind them to get the labs done. If it was over 7 days past scheduled blood draw, the row would be light red, as the urgency to get labs and continue treatment has increased as they are past due. In like manner, if treatment are within 14 days, the row would be yellow so i could call them and set the appointment. If past the treatment date, the row goes red and I have 14 days to get them in or we have to start treatment s all over again.

Book0.5 template.xlsx

## Formulas Change When Column/row Is Deleted. How Can I Stop That

Nov 11, 2006

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.

## Change Formulas To Reflect New Sheet Title?

Apr 7, 2014

I have a workbook, everyday 2 new worksheets get added to reflect the previous day's work. How can I reflect in a formula to go to next sheet instead of the conventional naming 'Sheet1' G7?

## Formulas To Not Change If Cells Where Deleted From Sheet

Sep 18, 2007

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?

## How Do I Copy Formulas And Automatically Change Reference Cells

Oct 18, 2008

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.

## Change File Path In Formulas For Every Cell In A Sheet

Oct 9, 2009

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.

## How To Change Source File Without Affecting VLookup Formulas

Jun 6, 2013

I have an excel file - central database - that holds a lot of raw data organized in tables. There are more than 15 files (dependent files) that access this data using VLOOKUP formulas.

I now need to add a few columns to the central database and these columns can't be after the columns already existing, i.e. they have to be on the left of some of the existing columns. Inserting those columns is going to mess up all the vlookup formulas in the dependent files.

Is there a way change the central database file without affecting the vlookup formulas in the dependent files?

## Dragging Formulas On Same Sheet (automatic Calculations Turned On)

Dec 10, 2012

I have a huge spreadsheet requiring me to manually enter alot of fields with formulas. I am using the formula

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

The full formula is quite long as this pick up the data off over 300 work sheets. (there's probably an easier way to do this aswell) When I copy or drag this to another cell with on the same worksheet it gives me the correct formular with the correct cells changes as I want. However this formular does not work. I have auto calculate on and I manually press enter on the cells and still nothing. If I change the following for the entire formula (about 8min to do each cell) it works

=COUNTIFS('A1'!C10:F11,Statistics!K5)+(COUNTIFS('A2'!C10:F11,Statistics!K5))+(COUNTIFS('A3'!C10:F11,Statistics!K5))

Is there an easier alternative then manually changing each number?

## Excel 2007 :: Formulas Do Not Automatically Refresh When Change Cells

Jul 11, 2012

I am using excel 2007 and when I change cells my formulas do not automatically refresh. I have set it to automatic and the calculations will not refresh. How I can force a calculation. I did the control alt f9 and nothing seems to work. Any macro that can force all calculations.

## Select Row Based On Cell Criteria Remove All Formulas From Row And Change Fill Color

Apr 23, 2014

I have a worksheet that when a row changes based on the value of column B, I want to remove all of the formulas found in the row but keep the existing values, and then change the color of the row.

In the sample file attached, when the value is "Closed", that row will keep the existing values and then it gets grayed out. Rows that are still marked "Open" need to retain the formulas in case other information changes.

I have tried copy/paste special using autofiltering but that doesn't work because of the hidden lines. This file changes on a daily basis and I need a quick way to update the file.

SampleFile.xlsx

## Change Cell Address In Formula On Cell Value Change

Mar 25, 2013

I have below formula.i need to change the cell address AK in the below formula on the value change in the A3.

=SUMIF(Working!\$D\$6:\$D\$3000,Report!B3,Working!\$AK\$6:\$AK\$3000)

For example if A3 is having value AZ i need the formula changed automatically as below

=SUMIF(Working!\$D\$6:\$D\$3000,Report!B3,Working!\$AZ\$6:\$AZ\$3000)