0

Having some trouble with off setting cells in order to use the auto fill option in excel rather than entering cell formula manually which is what I'm doing at the moment.

Here is an example of what Im trying to achieve

IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A25,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A26,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A27,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A28,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A29,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A30,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A31,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A32,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A33,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$19),'Item Breakdowns'!A34,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A42,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A43,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A44,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A45,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A46,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A47,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A48,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A49,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A50,"") IF(COUNTIF(Power!$A:$A,'Item Breakdowns'!$B$36),'Item Breakdowns'!A51,"")

This list of formulas starts in cell A1 and continues down to cell A20.

Basically I want to try and enter a formula in cell A1 that allows me to autofill down to cell A20 and beyond whilst keeping my layout and cell increments.

From the list above I hope it shows that what I need to achieve in the first part of the formula is to hold 'Item Breakdowns'!$B$19 for the first 10 cells then increase it by +17 cells to 'Item Breakdowns'!$B$36 then continue that for 10 more cells, then repeat this process.

The second part of the formula I need to start at 'Item Breakdowns'!A25 and increase by 1 cell each time through the first 10 cells. A25 A26 A27 A28 A29 A30 A31 A32 A33 A34 Then increase by +8 cells and continue again with the 1 cell increase A42 A43 A44 A45 A46 A47 A48 A49 A50 A51

I cant work this one out and Im not sure that it is achievable. Im able to use some auto fill but have to stop each time because of the increment jumps shown. I have a lot of data to enter through this method and normally I would live with this style if it was only a small amount of data.

Hopefully someone has an idea of what I can try hear. Thanks

0

1 Answer 1

1

This will iterate as you drag it down.

The first reference B19 will increase by 17 every ten rows.

The second reference A25 will iterate by one, but will jump 8 every ten rows.

=IF(COUNTIF(Power!A:A,INDEX('Item Breakdowns'!B:B,19+(QUOTIENT(ROW(1:1)-1,10)*17))),INDEX('Item Breakdowns'!A:A,ROW(25:25)+(QUOTIENT(ROW(1:1)-1,10)*7)))
1
  • Works perfectly. Thanks so much for you help! Jul 25, 2016 at 13:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .