Possible to keep a portion of formulas intact while dragging?

[Jennifre]

I know [basically/kind of] about using the $s to either keep rows or columns either intact or to change number as you drag.

However I need to keep whole portions of certain formulas intact as I drag -- is that possible using any characters or... something like that?

Example:
=SUMPRODUCT(--(($DG2:$DG1364=1111)),--(($DT2:$DT1364=5)+($DT2:$DT1364=6)))

I'd like to be able to drag this both ways, down and across, keeping various pieces of it intact as I drag in each direction.

Dragging down, I need to keep this intact/unchanging:
(($DT2:$DT1364=5)+($DT2:$DT1364=6))

Dragging across, I need to keep this intact/unchanging:
(($DG2:$DG1364=1111))

I've just been updating each row or column after dragging one way or the other, though I'm wondering if there is an easier way??

Thanks so much!!!!

 

[meldoc]

Hello

See the link.

http://www.youtube.com/user/ExcelIsFun#p/search/20/NuOfQSXx2gw

 

[Willmonbo]

Jen

it looks like you are only aware of locking a cell reference by using the dollar sign ahead of the column reference, that will lock the column reference and not the row reference, you can use the F4 key to toggle between different ways to lock your references, if you want the cell reference to be absolutely locked in all directions you should have the dollar sign in front of both the cell reference and the row reference as well.

I hope this helps.

 

[texasaswang]

You can write a script loop that will add your formula based on column or row data. With 2 loops, you would beable to name cell values based on the critera you describe.

Is that something you need assitance with?

 

[scottylad2]

Excel Workbook
	B	C	D	E	F	G	H	I	J
14						Lock in All Directions
15						pop1	pop1	pop1	pop1
16	pop1	goes1	the1	weasel1		pop1	pop1	pop1	pop1
17	pop2	goes2	the2	weasel2		Lock on the Row only
18	pop3	goes3	the3	weasel3		pop1	goes1	the1	weasel1
19	pop4	goes4	the4	weasel4		pop1	goes1	the1	weasel1
20	pop5	goes5	the5	weasel5		Locked on the column only
21	pop6	goes6	the6	weasel6		pop6	pop6	pop6	pop6
22	pop7	goes7	the7	weasel7		pop7	pop7	pop7	pop7
Sheet1

Excel Workbook
	G
18	pop1
Sheet1

Excel Workbook
	G
21	pop6
Sheet1