Search By ID

M

Masam

New Member
Joined
Apr 21, 2011
Messages
7
Hello Everyone, :)

Just need your help as I need a searching (filter) technique in the form of macro or any other best solution. Below I have excel sheet which contains ID, now I need that I will enter only ID number in a cell and it will automatically show me the result only that ID -- row. :confused:

Below is the picture of only 2Rows data. in a sheet I have almost 2000 to 3000 records (by ID).

Excel 2007<table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><colgroup><col style="BACKGROUND-COLOR: #e0e0f0" width="25"><col><col><col><col></colgroup><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="TEXT-ALIGN: center; COLOR: #161120">1</td><td>ID</td><td>MaxDate</td><td>Last Date</td><td>TOTALSUM</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">2</td><td style="TEXT-ALIGN: right">1543</td><td style="TEXT-ALIGN: right">4/3/2011</td><td style="TEXT-ALIGN: right">3/3/2011</td><td style="TEXT-ALIGN: right">17.3</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">3</td><td style="TEXT-ALIGN: right">1544</td><td style="TEXT-ALIGN: right">4/3/2011</td><td style="TEXT-ALIGN: right">1/3/2011</td><td style="TEXT-ALIGN: right">28.1</td></tr></tbody></table><table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>Sheet1</th></tr></thead><tbody></tbody></table>
I will enter 1543 ID.

Excel 2007<table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><colgroup><col style="BACKGROUND-COLOR: #e0e0f0" width="25"><col><col><col><col></colgroup><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="TEXT-ALIGN: center; COLOR: #161120">1</td><td>1543
</td><td>
</td><td>
</td><td>
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">2</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">3</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td></tr></tbody></table><table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>Sheet1</th></tr></thead><tbody></tbody></table>
It will automatically filter the data and show me only ID record data from 2000 records.

Excel 2007<table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><colgroup><col style="BACKGROUND-COLOR: #e0e0f0" width="25"><col><col><col><col></colgroup><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="TEXT-ALIGN: center; COLOR: #161120">1</td><td>ID</td><td>MaxDate</td><td>Last Date</td><td>TOTALSUM</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">2</td><td style="TEXT-ALIGN: right">1543</td><td style="TEXT-ALIGN: right">4/3/2011</td><td style="TEXT-ALIGN: right">3/3/2011</td><td style="TEXT-ALIGN: right">17.3</td></tr><tr><td style="TEXT-ALIGN: center; COLOR: #161120">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td><td style="TEXT-ALIGN: right">
</td></tr></tbody></table><table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>Sheet1</th></tr></thead><tbody></tbody></table>

Best Regards
Masam<table style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules="all" cellpadding="2"><thead><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><th>
</th></tr></thead><tbody></tbody></table>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Aladin Akyurek said:
Data|AutoFilter would do just that.
Click to expand...


<table border="0" cellpadding="0" cellspacing="0" width="828"><col style="width: 27pt;" width="36"> <col style="width: 198pt;" width="264" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; width: 27pt;" width="36" height="17">ID</td> <td class="xl69" style="width: 198pt;" width="264">MAX DATE</td> <td class="xl69" style="width: 198pt;" width="264">LAST DATE</td> <td class="xl69" style="width: 198pt;" width="264">TOTAL SUM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1543</td> <td class="xl70">=+VLOOKUP($A$9,$A$1:$D$3,2,FALSE)</td> <td class="xl70">=+VLOOKUP($A$9,$A$1:$D$3,3,FALSE)</td> <td class="xl71">=+VLOOKUP($A$9,$A$1:$D$3,4,FALSE)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1544</td> <td class="xl70">=+VLOOKUP($A$10,$A$1:$D$3,2,FALSE)</td> <td class="xl70">=+VLOOKUP($A$10,$A$1:$D$3,3,FALSE)</td> <td class="xl71">=+VLOOKUP($A$10,$A$1:$D$3,4,FALSE)</td> </tr> </tbody></table>
 
Upvote 0
sathish_arkay said:
<table border="0" cellpadding="0" cellspacing="0" width="828"><colgroup><col style="width: 27pt;" width="36"> <col style="width: 198pt;" span="3" width="264"> </colgroup><tbody><tr style="height: 12.75pt;" height="17"> <td class="xl69" style="height: 12.75pt; width: 27pt;" height="17" width="36">ID</td> <td class="xl69" style="width: 198pt;" width="264">MAX DATE</td> <td class="xl69" style="width: 198pt;" width="264">LAST DATE</td> <td class="xl69" style="width: 198pt;" width="264">TOTAL SUM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1543</td> <td class="xl70">=+VLOOKUP($A$9,$A$1:$D$3,2,FALSE)</td> <td class="xl70">=+VLOOKUP($A$9,$A$1:$D$3,3,FALSE)</td> <td class="xl71">=+VLOOKUP($A$9,$A$1:$D$3,4,FALSE)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">1544</td> <td class="xl70">=+VLOOKUP($A$10,$A$1:$D$3,2,FALSE)</td> <td class="xl70">=+VLOOKUP($A$10,$A$1:$D$3,3,FALSE)</td> <td class="xl71">=+VLOOKUP($A$10,$A$1:$D$3,4,FALSE)</td> </tr> </tbody></table>
Click to expand...


Thanks. But I don't want to use the Vlook formula as I already aware of this. I need a proper search technique so the user can enter only ID and it will automatically filter that record in a given parameters (whole sheet). Means display only the mentioned ID record.:confused:

Thanks
Masam
 
Upvote 0
I'm not sure of the reason why you wouldn't want to use vlookup, when that will work fine. But personally, I would add a new tab for the purpose of inputting the ID and returning the results.

That way, no filtering needs to happen to the actual DATA sheet. You could even hide the DATA tab. I don't like the user messing with the actual data sheet.

Set up a tab, have an input cell, and the other 3 or 4 cells conducting the vlookup. They enter the ID and the unique results are returned.
 
Upvote 0
AndrewMB said:
I'm not sure of the reason why you wouldn't want to use vlookup, when that will work fine. But personally, I would add a new tab for the purpose of inputting the ID and returning the results.

That way, no filtering needs to happen to the actual DATA sheet. You could even hide the DATA tab. I don't like the user messing with the actual data sheet.

Set up a tab, have an input cell, and the other 3 or 4 cells conducting the vlookup. They enter the ID and the unique results are returned.
Click to expand...

Please can you provide me example and attached any excel sheet. Very informative for me please (newbie). ;)
 
Upvote 0
Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #c0c0c0">Enter ID</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">MaxDate</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Last Date</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">TOTALSUM</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">4</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">24/03/04</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">04/03/11</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">38.7</TD></TR></TBODY></TABLE>
Input​


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E8</TH><TD style="TEXT-ALIGN: left">=IF($C$8=0," ",VLOOKUP($C$8,DATA!$C$4:$F$22,2,FALSE))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F8</TH><TD style="TEXT-ALIGN: left">=IF($C$8=0," ",VLOOKUP($C$8,DATA!$C$4:$F$22,3,FALSE))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G8</TH><TD style="TEXT-ALIGN: left">=IF($C$8=0," ",VLOOKUP($C$8,DATA!$C$4:$F$22,4,FALSE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">ID</TD><TD style="TEXT-ALIGN: center">MaxDate</TD><TD style="TEXT-ALIGN: center">Last Date</TD><TD style="TEXT-ALIGN: center">TOTALSUM</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">22/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">17.3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">23/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">28</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">24/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">38.7</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">25/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">49.4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">26/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">60.1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">27/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">70.8</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">28/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">81.5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right">29/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">92.2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">30/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">102.9</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right">31/03/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">113.6</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right">01/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">124.3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right">02/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">135</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">03/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">145.7</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">04/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">156.4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: right">16</TD><TD style="TEXT-ALIGN: right">05/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">167.1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: right">06/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">177.8</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">07/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">188.5</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: right">08/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">199.2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">09/04/04</TD><TD style="TEXT-ALIGN: right">04/03/11</TD><TD style="TEXT-ALIGN: right">209.9</TD></TR></TBODY></TABLE>
DATA​


OK, so on the DATA sheet are your values. ID, Max Date, Last Date and TOTALSUM.

Then on the Input tab, the Input cell, and 3 vlookup cells. The user types the ID number into C8 and E8, F8 and G8 return the values.

Obviously, the lookup range's need to change to suit your cells.
 
Upvote 0
Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #c0c0c0">Enter ID</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">MaxDate</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">Last Date</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">TOTALSUM</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">21</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Does not exist</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0; BORDER-TOP: black 1px solid"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #c0c0c0"></TD></TR></TBODY></TABLE>
Input​


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E8</TH><TD style="TEXT-ALIGN: left">=IF($C$8=0," ",IF(ISNA(VLOOKUP($C$8,DATA!$C$4:$F$22,2,FALSE)),"Does not exist",VLOOKUP($C$8,DATA!$C$4:$F$22,2,FALSE)))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>F8</TH><TD style="TEXT-ALIGN: left">=IF($C$8=0," ",IF(ISNA(VLOOKUP($C$8,DATA!$C$4:$F$22,2,FALSE)),"-",VLOOKUP($C$8,DATA!$C$4:$F$22,3,FALSE)))</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G8</TH><TD style="TEXT-ALIGN: left">=IF($C$8=0," ",IF(ISNA(VLOOKUP($C$8,DATA!$C$4:$F$22,2,FALSE)),"-",VLOOKUP($C$8,DATA!$C$4:$F$22,4,FALSE)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
You could also amend the vlookup formula, so that if the user enters an ID number that doesn't exist it will tell the user as much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
574
mumps
mumps
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
E
  • Question Question
Adding a custom message.
Replies
1
Views
660
Micron
M
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
692
John_w
J

Forum statistics

Threads
1,224,550
Messages
6,179,462
Members
452,915
Latest member
hannnahheileen

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