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),"")))
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

mole999

Moderator
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

Moderator
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

.
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

.
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

Moderator
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,462
Members
414,069
Latest member
StudExcel

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