Subtract value from cell numbers referenced in formulas

**m.g.williams** · 07-10-2013, 11:16 AM

Hi everyone,

I'm hoping that I might be able to get a bit of help with a problem I'm having in excel. I'm using a workbook that has a lot of formulas, with one section in particular containing a very large table full of formulas. The formulas in the table are supposed to pull values from several worksheets within the workbook so that the aggregate data can be filtered. I was hoping to simply do up the formulas once for the cells referring to the first worksheet, drag the formulas down to apply them to the other cells referring to the other worksheets, make a few replacements and be done. However, when I drag or copy the formulas to apply them to the cells below, the cell numbers referenced in each formula automatically change. I want the formulas to refer to the same set of cells each time as each worksheet being referenced is identical. I know that I can make the necessary corrections manually, but I'm dealing with 2000 lines of data. Is there a quick way that I can fix this?

In the attached workbook I have an example just to explain what I'm trying to do. I have data on Sheet1 and Sheet2, with a table in Sheet3 pulling it all together. I wrote the formulas to pull data from cells A3,A4 and A5 in Sheet1, but when I drag or copy it down, the automatic numbering kicks in and now tries to pull data from cells A6, A7 and A8. I still need the formulas to reference cells A3, A4 and A5. It's easy enough for me to use Find/Replace to reference the right sheet number, but manually changing the individual numbers would be a bit of a headache. Is there any way I could subtract a set value from the cell numbers referenced in the formulas (i.e., subtract 3 from each so that instead of referring to A6, A7 and A8, they refer to A3, A4 and A5)? Or can the automatic numbering be disabled?

Thanks in advance!

**MrShorty** · 07-10-2013, 11:52 AM

It looks to me like you simply need to learn to make these absolute references before the copy/fill step http://office.microsoft.com/en-us/ex...323.aspx?CTT=1 Put a $ in front of the row number (or use F4 to cycle through the relative/absolute/mixed references), then copy or fill.

**ARGK** · 07-10-2013, 11:53 AM

Hi
If you don't want the cell numbers to change then add a $ in front of the Col and Row numbers.
Hope this helps.
Tony

**m.g.williams** · 07-10-2013, 12:42 PM

This is exactly the information I needed. Instead of taking me the rest of the week, I should be able to get my work done in the next few hours! Thanks!