#### can

##### New Member
Hi,

=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=\$A\$6), RAW_inapp!C150, "Wrong")

I use this formula.

My problem is that i have to increment this by 4 so next formulas will be ;

=IF(AND(RAW_inapp!A154=B1, RAW_inapp!D154=\$A\$6), RAW_inapp!C154, "Wrong")
=IF(AND(RAW_inapp!A158=B1, RAW_inapp!D158=\$A\$6), RAW_inapp!C158, "Wrong")
=IF(AND(RAW_inapp!A162=B1, RAW_inapp!D162=\$A\$6), RAW_inapp!C162, "Wrong")

Is there a way to do this?

I attached the screenshots so basically, i need to chech the rawinapp sheet and if the date and the name match, i will write it to the data sheet on the correct date. The only way i found is to do it like this by incrementing by 4 the reference value since the data is autogenerated and updating every day, it will always be in the 4th place.

Please let me know if there is a better way too. Thank you.

#### Attachments

• data.JPG
22.2 KB · Views: 0
• Rawinapp sheet.JPG
49.9 KB · Views: 1

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### AlanY

##### Well-known Member
this will only check Row150 and every 4th row thereafter

=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=\$A\$6,MOD(ROW(RAW_inapp!A150),4)=2), RAW_inapp!C150, "Wrong")

#### can

##### New Member
this will only check Row150 and every 4th row thereafter

=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=\$A\$6,MOD(ROW(RAW_inapp!A150),4)=2), RAW_inapp!C150, "Wrong")
=IF(AND(RAW_inapp!A150=B1, RAW_inapp!D150=\$A\$6), RAW_inapp!C150, "Wrong")
It gives me the correct number however when i try to get the second one to the right it gives me error.

I put an image that show what i am trying to do. I need to fill starting with B7 to C7, D7, E7 etc. It works on B7 but when i try to autofill in C7 it wont work.

Can you help?

#### Attachments

• Untitled-1.jpg
157.6 KB · Views: 4

#### AlanY

##### Well-known Member
ok, difference approach. try this in B7, copy across (adjust ranges to suit)

=SUMPRODUCT(RAW_inapp!\$C\$1:\$C\$10000,--((RAW_inapp!\$A\$1:\$A\$10000=Sheet2!B\$1)*(RAW_inapp!\$D\$1:\$D\$10000=Sheet2!\$A\$6)))

#### can

##### New Member

ok, difference approach. try this in B7, copy across (adjust ranges to suit)

=SUMPRODUCT(RAW_inapp!\$C\$1:\$C\$10000,--((RAW_inapp!\$A\$1:\$A\$10000=Sheet2!B\$1)*(RAW_inapp!\$D\$1:\$D\$10000=Sheet2!\$A\$6)))
it gave me #REF!

#### can

##### New Member
ok, difference approach. try this in B7, copy across (adjust ranges to suit)

=SUMPRODUCT(RAW_inapp!\$C\$1:\$C\$10000,--((RAW_inapp!\$A\$1:\$A\$10000=Sheet2!B\$1)*(RAW_inapp!\$D\$1:\$D\$10000=Sheet2!\$A\$6)))
Can you reach this file? Maybe you can try this is a simple file for this try out

#### can

##### New Member

Can you reach this file? Maybe you can try this is a simple file for this try out

#### Fluff

##### MrExcel MVP, Moderator
Try removing the ref to Sheet2 in Alan's formula
Excel Formula:
``=SUMPRODUCT(RAW_inapp!\$C\$1:\$C\$10000,--((RAW_inapp!\$A\$1:\$A\$10000=B\$1)*(RAW_inapp!\$D\$1:\$D\$10000=\$A\$6)))``

can

#### AlanY

##### Well-known Member
it works on your test file

Test.xlsx
ABCDEFGHI
101/12/202002/12/202003/12/202004/12/202005/12/202006/12/202007/12/202008/12/2020
2
3
4
5
7Conversions10798947575111107103
Cell Formulas
RangeFormula
B7:I7B7=SUMPRODUCT(RAW_inapp!\$C:\$C,--((RAW_inapp!\$A:\$A=B\$1)*(RAW_inapp!\$D:\$D=\$A\$6)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:AF2Cell Value>=500textNO

#### AlanY

##### Well-known Member
Try removing the ref to Sheet2 in Alan's formula
Excel Formula:
``=SUMPRODUCT(RAW_inapp!\$C\$1:\$C\$10000,--((RAW_inapp!\$A\$1:\$A\$10000=B\$1)*(RAW_inapp!\$D\$1:\$D\$10000=\$A\$6)))``
thanks, just noticed that

can

Replies
1
Views
42
Replies
7
Views
398
Replies
9
Views
183
Replies
2
Views
209
Replies
3
Views
322

1,128,150
Messages
5,628,992
Members
416,358
Latest member
grsaltzman

### 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.

### Which adblocker are you using?

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

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