Formula Increment

B

bullcreel

Board Regular
Joined
Aug 25, 2016
Messages
157
Office Version
  1. 2021
Platform
  1. Windows
=IF(BatNum!$P3=0,2,IF(BatNum!$P3=1,1,IF(BatNum!$P3=2,1,IF(BatNum!$P3>=3,0))))
I have this formula in cell "H2". What i want it to do is when I copy it down i want it to check $P10 next, $P17, then $P24 and so on every 7 cells. Is this possible?

Thanks in advance

Jim
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
Instead of having to use the volatile INDIRECT function three times, I suggest using it once with the SWITCH function:
Book1
DEFGHIP
1
2$P30-3
3$P100-3
4$P170-3
5$P240-3
6$P312-3
7$P381-3
8$P450-3
9-2
10-2
11-2
12-2
13-2
14-2
15-2
16-1
17-1
18-1
19-1
20-1
21-1
22-1
230
240
250
260
270
280
290
301
311
321
331
341
351
361
372
382
392
402
412
422
432
443
453
463
473
483
493
503
Sheet2
Cell Formulas
RangeFormula
G2:G8G2="$P"&3+(ROW(P2)-2)*7
H2:H8H2=SWITCH(INDIRECT("$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)
P9:P50P9=P2+1
 
Upvote 0
I am looking at it and not quite figuring out how they are used inside my formula.
 
Upvote 0
what do you mean? "They are used inside my formula"?
 
Upvote 0
See if this works for you. Replace your current formula with.
Excel Formula: 
=SWITCH(OFFSET(BatNum!$P$3,MAX((ROW(BatNum!P3)-ROW(BatNum!$P$3))*7,0),0),0,2,1,1,0)
 
Upvote 0
I forgot I took out the sheet name. This is a correction to post in #2. Put this in cell H2, and drag down:

Excel Formula: 
=SWITCH(INDIRECT("BatNum!$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)
 
Upvote 0
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Cubist said:
See if this works for you. Replace your current formula with.
Excel Formula: 
=SWITCH(OFFSET(BatNum!$P$3,MAX((ROW(BatNum!P3)-ROW(BatNum!$P$3))*7,0),0),0,2,1,1,0)
Click to expand...
It worked for cell H2, but not changing to row P10 and then P17, etc. like i need it to for the next cells.
 
Upvote 0
awoohaw said:
I forgot I took out the sheet name. This is a correction to post in #2. Put this in cell H2, and drag down:

Excel Formula: 
=SWITCH(INDIRECT("BatNum!$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)
Click to expand...
Its not checking the values for the right row in BatNum. After checking the values on BatNum!P3, it should then check values on P10, then P17 and so on. Thanks for the help so far
 
Upvote 0
Fluff said:
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
I am using Excel 2021.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Copy addresses in multiple places provided
Replies
1
Views
334
sofas
S
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
372
Peter_SSs
Peter_SSs
U
  • Solved
Copy/Paste Rows on select criteria
Replies
4
Views
973
UTB
U
U
  • Question
Information overwriting when transferring
Replies
7
Views
333
UTB
U
S
  • Question
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
80
Alex Blakenburg
A

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top