Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Excel Forum

Dragging A Formula, But Keeping Certain Values

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello from sunny Madrid

I have created a complex formula to help me calculating Golf handicaps for players.


The formula is in the first cell and I want to drag the formula across to the right to cover the 18 holes.

But ALL the values change and I want some to change and some to stay constant.



How do I tell excel to drag the formula across but keep certain parts of the formula the same..ie ....=D11.... in the formula when dragged across doesn't go D12,D13;D14 in each cells formula and should stay as D11 for all.

Many thanks for your help

Ole

View Answers     

Similar Excel Tutorials

Easy Way to Manage Names in Excel
How to manage Names in Excel so they are easy to view and change as needed. This is a little trick that I use to k ...
Count the Number of Unique Values in a List in Excel
Use a formula to count the number of unique values that are contained within a list in Excel. Steps to Count the Nu ...
View Separate Parts of a Worksheet at Once in Excel
View separate parts of a large worksheet while in the same Excel window. This allows you to scan and compare data w ...
Quickly See All Cells linked to a Formula or Function in Excel
Ill show you how to see the cells used in a formula/function in Excel and also how to tell which cells are using t ...
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Sum Values that Meet 1 of Multiple Conditions in Excel
How to sum values that equal one of many potential criteria; this is basically summing with an OR condition. This ...

Helpful Excel Macros

Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Similar Topics







Today I ran into an odd problem. I typed in values for column A rows 1 through 10 then values for column B rows 1 through 10. then in column C, I made the formula C1=A1/B1. The math was correct it showed 542 in the C1 cell. So I dragged that formula down and it showed 542 in all column C cells which is not correct. And when I went to check to see if the formula was correctly dragged it was. For instance, the formula in C2 is =A2/B2 however the value of that cell showed 542 which was not the correct math/value.

But it gets even more weird. When I click on the Column C cells and then it shows the formula up top in the formula bar and if I put my cursor anywhere in the formula bar and hit Return the formula does not change however the correct value then appears in the Column C cell. It is like the act of putting the cursor in the formula activates it to work properly but until it is activated that C cell shows the value of the cell which it was dragged down from.

This is quite bizarre. Has anyone ever seen this before? I have no idea what is going on. I ran a scan for viruses and none were found. I tried it on several new/different spreadsheets but it keeps happening.

Thanks for any tips on this.


Great tip! But one thing: On my machine (Excel 2008 Mac), the values in the formula cell are not correctly calculated simply by dragging. Instead, the values are equal to the cell above (the first entry formula value). To get the correct value, I need to click in the formula bar and then hit enter. (I only discovered this after an hour of tinkering, figuring I had botched the formula!)

Are there any ways around this so that it updates upon dragging the formula?


Column 1 has roughly 20 rows of information. Cell C1 has the formula =A1.

Is there a formula so that when I drag C1 horizontally into D1, E1, F1, ..., the values placed in each cell will be =A2, =A3, =A4, ...

I do not want to transpose the values from column 1 into C1, D1,.... I want these cells to have a formula that links them up to column 1's values

Thanks

Hi All,

If I drag a formula to the right it adapts the column letter in the formula from A to column B, which is standard Excel functionality.

However, if I drag the formula to the right I want Excel to skip a column each time.

In other words:

=A1 ; = B1 ; = C1

Should be

=A1 ; = C1 ; = E1 etc...

Is there an easy trick to do this?

Thanks,

Michiel


Hi,

How do I enter the value in a cell (say B4) into a formula as a constant so it doesn't change when I copy it down the column?

For example,

B4 = 2.4 (a constant)

This is the result I want:

F6 =C8 + B4*4.243

F7 =C9 + B4*4.243

F8 =C10 + B4*4.243

B4 remains constant as I copy F6 down.

What's the correct formula for F6 so I can copy it down and keep B4 fixed?

Thanks.


I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:
A1: 50
A2: 10
B1: 60
B2: 20

A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?


hi guys,

i have a simple spreadsheet where i'm totalling a few values thru a formula. it's similar to this:

=D13*F13

i'm trying to create a funcion that says if d13 * f13 >= 300, i want the value to stay at 300 max and at the same time, if d13 * f13 < 300, i want the total to remain whatever it is.

i have been able to create both of these formulas successfully by themselves but i can't combine them. is this possible?

any help would be greatly appreciated!!


Hi,

Try this...

There are some valid datas in the cells A2 (Eg. 100), B2(Eg. 200), C2 and D2.
Data validation is used in cell K2 to limit it to a set of values.

Can we implement this formula?

IF(K2="Approved")
{
A2=A3; B2=B3; C2=C3; D2=D3; K2=K3;
}
ELSE
{
no change to any values.
}

Thanks in advance...


Hello

I'm quite experienced Excel user. I've never come across this problem but tinkering in every conceivable way within Excel settings and the solution has eluded me.

I have added a worksheet created elsewhere (it is a form I need printing, with the data coming from 2 sheets I have created from scratch) which has pre formatted cells for Date and Client Name etc.

When I try to reference the cell in this added sheet from my 2 sheets, instead of the result, it always displays the formula, not the result.

I have tried doing it from one of my sheets to reference to this new, and that displays the result and not formula. I can't imagine why it's doing this and I've never seen it happen before.

Formatting cells, giving cells names rather than the usually adequate of reference to the Cell Number doesn't change things. I wonder if I've picked up some legacy protection from the original form but can't see anywhere in the tools etc that's obvious.

There's about 50+ cells that need referencing and I got to get this done for work.

Please help me. This is my first need to post on a Excel Forum as I've always found help or answers from other peeps or internet but this one is making me scratch my head big time.



I have a spreadsheet that when I copy the formula, it copies correctly
(changes the cells it should subtract), however, the result stays the same.
It matches the formula I copied it from, even though the cells to calculate
are now different. I even did a paste special and said only formula, but
still, same result. If I actually type in the formula, it works fine, but I
have a lot I need to copy and paste. Is there a way I can fix this?

Thanks,



I have a formula in cell C1 (=A1/B1). I want to copy this formula down to row C20, however rows A2 to A20 are empty so C2 to C20 show (#DIVO!). Is there something I can put in the formula to make column C be blank until the data is enter into Columns A & B?


Hi there. I'm using Excel 2007 and often use filters to find inconsistent data (mis-spellings, etc.) and then clean up the data using the fill handle to fill in correctly formatted values. I'm finding that, with a simple filter on, dragging over cells hidden by the filter changes the values in the hidden cells too. This is something I don't remember from my last version of Excel. I'm wondering if I've mis-remembered how this control works, or if there is a setting I can't find.

My goal is to drag and fill (or paste) in a colum with a range of rows wiht some hidden (filtered out) rows, and have the filtered out data be unaffected, if that makes sense.

Thanks!

T


Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.


I have an excel worksheet that adds two other worksheets in a data
triangle. I copied it to create a new data set and used find &
replace to change the worksheet references to the new ones.

The cells still contain the result of the old formula referring to the
previous worksheets. The only way I can get the formula to return the
correct result is to edit (F2) each cell and press enter. Calc now
(F9) does nothing.

I've seen this before, but this time, I need to calculate many
thousands of cells and don't have time for this workaround.

Any ideas?

Thanks.

Don S



Hi all,

I'm looking for help in building a formula which will sort numbers into different "buckets". My spreadsheet has a range of values in column B. These values can range anywhere from -100,000,000 to +10,000,000. I'd like to be able to sort them into the following buckets:-


Is it possible to change the color of text using a formula, such as an IF
statement? What I have in mind is something like this (stated simply): If
cells B9 or N9 or Z9 or AL9 are blank, do nothing, else color text in cell S4
red.




Hi, hope someone can assist, i want to add some arrow syimbols in to a formula, so that when the formula works out it shows an up arrow for higher or a down arrow for lower, i am using this formula at the moment because i dont know any better but it works, putting in the word up or down:

I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?

Thanks


I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.

FORMULA: =sumif(range,criteria,sum_range)

I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15

because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?

All help is appreciated.

Thank you.



Hi Everyone,
I have searched online and in help but can't seem to find the best solution...

I have values like 00904BB303D6 that need to become: 00:90:4B:B3:03:D6 (the value is always 12 digits, and the : needs to appear every two digits.) Is there a formula that can easily do this for me? Your suggestions are greatly appreciated!

Thanks in advance!!!!


Ran into a problem I've never seen before. When dragging a cell across that contains a sum equation the result was the following cells with the same value. Inside the cell it showed what would be the correct new equation, but the value was still from the original cell. Any ideas on what's causing this?

If I double click the cell and then hit enter it will re-total the area and then give me the correct value, but I'd obviously rather is just work right the first time.


I have a spreadsheet with simple (addition/subtraction) formulas. The file is quite large and the formulas are too. All of a sudden, the formulas stopped working except when I double click in the cell containing the actual formula. For example, if I enter "2" in each cell, A2 and B2, cell B3 should reflect "4" because there is a formula in cell B3 which totals cells A2 and B2. Only by double clicking on cell B3 will the program actually calculate. I'm totally perplexed and so is my IT contact. Anyone?


I'm working on a speadsheet that has thousands of line items with lots of great information, but not in the fields I need them to be in. Is there a formula to move for example cell contents A26 to B25 and D26 to F27? Also I have contents in one cell that I need to break apart is there a formula for that too? I searced the help and the net an came up with nothing. Maybe I'm not wording it correctly, but I'd appreciate the help.


hi,

I would like a formula that would list all the items in row B that match the criteria in row A. the first cell with formula would list the first item, the second cell with the formula would list the next item, and so forth. Also, column B might have a duplicates that should be listed. Is this possible? I cannot manipulate the order of the original items (ie, filters) because this data is being used to derive other formulas.


From limited experience I know that excel calculates dates via serial numbers.

I have formulas to add a number of days to a cell containing an entered date and display the resulting new date (ie. 03/01/2011 (c34)+11 = 03/12/2011)

I am seeking to leave resulting formula cell blank until a date is entered in the input cell. Currently when the input cell is empty the formula cell obviously displays 1/11/1900 using the above example.

What conditional format would achieve leaving the formula cell blank until date data in entered into the source cell?

Hopefully a simpler question for your experience level than mine.


I have entered a vlookup formula in a cell and it returns the correct value from the other worksheet. However when I copy the formula down it keeps returning the exact same value as the first cell. If I enter a formula in each individual cell it returns the correct value. Its driving me crazy ! I woyld be glad of any advice

Many Thanks

Karamazov