test for existing values

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,777
I have a workbook with several tabs. the first tab is a data input sheet: subtotals from this sheet get posted to cells on the other sheets. at the top of the Data sheet is a drop down validation list that the user will select the next month from. On each of the other sheets, is a grid with calendar months down the left hand side and years across the top:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">Incoming</td><td style="text-align: right;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;">10</td><td style="text-align: center;;">09</td><td style="text-align: center;;">08</td><td style="text-align: center;;">07</td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="font-weight: bold;;">Month</td><td style="font-weight: bold;text-align: right;;">2012</td><td style="font-weight: bold;text-align: right;;">2011</td><td style="font-weight: bold;text-align: center;;">2010</td><td style="font-weight: bold;text-align: center;;">2009</td><td style="font-weight: bold;text-align: right;;">2008</td><td style="font-weight: bold;text-align: right;;">2007</td><td style="font-weight: bold;text-align: right;;">2006</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">JAN</td><td style="text-align: right;;"></td><td style="text-align: right;;">1201</td><td style="text-align: right;;">1353</td><td style="text-align: right;;">1752</td><td style="text-align: right;;">2050</td><td style="text-align: right;;">1940</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">FEB</td><td style="text-align: right;;"></td><td style="text-align: right;;">1392</td><td style="text-align: right;;">1508</td><td style="text-align: right;;">1708</td><td style="text-align: right;;">2130</td><td style="text-align: right;;">2172</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">MAR</td><td style="text-align: right;;"></td><td style="text-align: right;;">1538</td><td style="text-align: right;;">1641</td><td style="text-align: right;;">1783</td><td style="text-align: right;;">1823</td><td style="text-align: right;;">2468</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">APR</td><td style="text-align: right;;"></td><td style="text-align: right;;">1338</td><td style="text-align: right;;">1327</td><td style="text-align: right;;">1730</td><td style="text-align: right;;">1819</td><td style="text-align: right;;">2116</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">MAY</td><td style="text-align: right;;"></td><td style="text-align: right;;">1485</td><td style="text-align: right;;">1296</td><td style="text-align: right;;">1576</td><td style="text-align: right;;">2008</td><td style="text-align: right;;">2449</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">JUN</td><td style="text-align: right;;"></td><td style="text-align: right;;">1499</td><td style="text-align: right;;">1380</td><td style="text-align: right;;">1626</td><td style="text-align: right;;">1865</td><td style="text-align: right;;">2028</td><td style="text-align: right;;">1870</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">JUL</td><td style="text-align: right;;"></td><td style="text-align: right;;">1549</td><td style="text-align: right;;">1382</td><td style="text-align: right;;">1660</td><td style="text-align: right;;">2036</td><td style="text-align: right;;">2091</td><td style="text-align: right;;">1703</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">AUG</td><td style="text-align: right;;"></td><td style="text-align: right;;">1653</td><td style="text-align: right;;">1383</td><td style="text-align: right;;">1486</td><td style="text-align: right;;">1925</td><td style="text-align: right;;">2132</td><td style="text-align: right;;">1972</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">SEP</td><td style="text-align: right;;"></td><td style="text-align: right;;">1590</td><td style="text-align: right;;">1411</td><td style="text-align: right;;">1556</td><td style="text-align: right;;">2005</td><td style="text-align: right;;">1982</td><td style="text-align: right;;">2149</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">OCT</td><td style="text-align: right;;"></td><td style="text-align: right;;">2339</td><td style="text-align: right;;">1324</td><td style="text-align: right;;">1594</td><td style="text-align: right;;">2073</td><td style="text-align: right;;">2014</td><td style="text-align: right;;">2477</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">NOV</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1440</td><td style="text-align: right;;">1467</td><td style="text-align: right;;">1884</td><td style="text-align: right;;">2186</td><td style="text-align: right;;">2821</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">DEC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1563</td><td style="text-align: right;;">1602</td><td style="text-align: right;;">2037</td><td style="text-align: right;;">2147</td><td style="text-align: right;;">2197</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Incoming</p><br /><br />

I am trying to figure out a simple method to check whether there is data on any of the four other sheets in the spot corresponding to the month selected by the user on the Data tab. the format of the validation list dates are dd/mm/yyyy in cell B2 on the data tab.
 
Last edited:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Ajm
Yep, interesting, but do you have a question ?
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,777
are you a lurker MM? you seem to be there whenevr i stuff it up lately:eek:)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
No....just lucky I guess....I'd like the same chances of winning tonights Lotto !!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,572
Members
414,389
Latest member
MarkElla

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