Excel Drag autofill only part of formula

[WORKSHOP]

Hi All,

This is my first post in any forum, so please be gentle.

I have a formula that references cells from other sheets within the same workbook. Its a workbook that calculates times that workers spend on a job with a unique job number over a number of days from individual workers time sheets and then converts to a cost in a single cell on another costing sheet.

Most of it I have worked out, but for every job number created, I am having to manually type in a formula which looks for the unique job number on the timesheets. I have been copy and pasting most of the formula and manually punching in the unique job number to look for.

Please see formula below:

=SUMIFS('KRIS LAB'!L:L,'KRIS LAB'!B:B,"=3424")

I have that formula in 6 adjacent columns, with only the workers name changing in each (Saul, Nick, Billy, Tom, Cameron)

How can I drag that row, but increment the numerical part only so the next row keeps everything the same but ends in 3425, and then the row after 3256 etc...?

I have tried selecting one row and when I drag, it make duplicates of the same formula with same job number. If I select 3 or 4 rows, when I drag, it repeats those same 3 or 4 job numbers over and over.

Job numbers are formatted as 'number'. Not sure if this matters, but I'm trying to give as much info as I can to aid a speedy solution if possible.

Any help would be greatly appreciated and would save me lots of time.

Thanks

 

[Becky]

Excel references are 'relative' by default, meaning that if you copy a formula then the references will change. This is often very useful, but if you want to fix them - ie make them absolute - a dollar sign will help you here. All you need to do is place it before either the row or column (or both),

For example, $A1 will always a fixed column even when copied across horizontally, and A$1 will always have a fixed row even if copied vertically. Likewise, if $A$1 is used in a formula, then when you copy that formula into other cells it will always reference A1.

You can also use F4 to toggle the reference between relative, absolute and partial absolute. You just need to make sure that the cursor is on the reference.

Depending on what you're doing, it might also be worth taking a look at the VLOOKUP formula - from what you've said it might be useful.

 

[WORKSHOP]

Hi Becky,

Thanks for the reply. The things I need to stay constant are staying constant, so there's not a problem with cells being relative when copied. Its more that I want only part of the formula to increment as I drag down.

I can do it with numbers, and dates and probably lots of other things, but I want to single out part of the formula as being the bit to increase as I drag. Please see below how I would like the results

=SUMIFS('KRIS LAB'!L:L,'KRIS LAB'!B:B,"=3424")
=SUMIFS('KRIS LAB'!L:L,'KRIS LAB'!B:B,"=3425")
=SUMIFS('KRIS LAB'!L:L,'KRIS LAB'!B:B,"=3426")
=SUMIFS('KRIS LAB'!L:L,'KRIS LAB'!B:B,"=3427")
=SUMIFS('KRIS LAB'!L:L,'KRIS LAB'!B:B,"=3428")

Every time I drag the row, or cell if that's easier, down, I want only the number at the end of the formula to increase. Is this possible?

Thanks again if anyone can solve my problem.

Kind Regards

Andy

 

[Becky]

Ah OK, I see what you mean now. The way that I would probably do it would be to have a separate column for the job number and then have the reference to that instead of a number. You could always hide the column if you don't want it visible.