If Date matches selection, double points assigned -

sugardaddy095

New Member
Joined
Dec 28, 2016
Messages
7
I'm trying to have A102 (Date Entered by user) if it matched any of the cells below, then it doubles the point value from the list. The values are not properly doubling, however, when stepping through the equation it works. Help!! ive been working on this for forever.




=IF(A102=OR($A$21,$J$24,$P$27,$K$30,$O$27,$J$30,$P$44,$J$46,$O$50,$P$50,$J$53,$K$53,$S$50,$Y$47,$X$53,$G$61,$A$64,$B$64,$C$64,$G$64,$A$67,$B$67,$C$67,$X$61,$Y$61,$S$64,$T$64,$N$90,$O$90,$P$90,$X$90,$Y$90,$S$93,$T$93,$U$93,$V$93,$W$93,$X$93,$Y$93,$S$96),(VLOOKUP($C102,$AR$7:$AT$30,3,FALSE)*2),(IFERROR(VLOOKUP($C102,$AR$7:$AT$30,3,FALSE),"")))
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
as long as you are not trying to update A102
 

sugardaddy095

New Member
Joined
Dec 28, 2016
Messages
7
as long as you are not trying to update A102
A102 Never changes when inputted. For example, 1/1/2017 = a102 = A21 so it should return double the point values. When it does work, it also doubles A103 which is not a double point day... If that makes any sense.
 

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
I'm not visualising where the doubles appear
 

sugardaddy095

New Member
Joined
Dec 28, 2016
Messages
7

ADVERTISEMENT

Under the below table, for the date 01/01/2017, it should double the .5 value and assign it 1.00... 01/02/2017, its not a double point so .5 would be correct.

The date, 1/1/17 or A102 in the discussed example is reference to a calendar in the workbook.


Summary of 2017 Occurrences:
DateDescriptionValueNotes
1/1/17D0.50 Early Departure
1/2/17D0.50 Early Departure

<colgroup><col span="3"><col><col span="4"><col><col><col span="3"></colgroup><tbody>
</tbody>
 

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
What are yo trying to tell Excel to do with this?

IF(A102=OR($A$21,$J$24,$P$27,$K$30,$O$27,$J$30,$P$44,$J$46,$O$50,$P$50,$J$53,$K$53,$S$50,$Y$47,$X$53,$G$61,$A$64,$B$64, $C$64,$G$64,$A$67,$B$67,$C$67,$X$61,$Y$61,$S$64,$T$64,$N$90,$O$90,$P$90,$X$90,$Y$90,$S$93,$T$93,$U$93,$V$93,$W$93,$X$93, $Y$93,$S$96),

The syntax is not correct.

What are the dates that will force the doubling to occur? Is there a pattern?
 
Last edited:

sugardaddy095

New Member
Joined
Dec 28, 2016
Messages
7

ADVERTISEMENT

All the cells listed above are blackout dates. Meaning if A102 (inputted by user) matches any of those dates of the year, their infraction points will be doubled in value that is pulled from the vlookup.
 

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
Yes, I get that, but what are the dates? Are they particular to that user, or are they specific dates (e.g. the first of every month)? Is there any pattern? Does each cell need to be referenced individually? What makes them special?
 
Last edited:

sugardaddy095

New Member
Joined
Dec 28, 2016
Messages
7
There is no pattern in the dates. They are mostly holidays or the days surrounding the holidays. So they are all set for 2017 and are specific to those cells that will not change.
 

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
You enter a date in A102
You compare to the cells that you have identified and if any of those cells have the same date as A102 you double something

what is the something that gets doubled?

are you using english or american dates 1st Feb or Jan 2nd
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,423
Messages
5,837,152
Members
430,479
Latest member
mamush200

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
Top