tag:blogger.com,1999:blog-44026916470771725792011-04-21T14:39:16.141-07:00mjr_havochttp://www.blogger.com/profile/01052001605542968789noreply@blogger.comBlogger21100tag:blogger.com,1999:blog-4402691647077172579.post-15645336323703936122008-07-01T02:59:00.001-07:002008-07-01T02:59:49.654-07:00Excel labels can be used to perform a quick lookup on a table.<br /><br />They are similar to names / named ranges but provide different functionality.<br /><br />Names can refer to a range (A1:B3) or number of ranges(A1:B3, G5:J10), or a formula($A$1*52*7) or an array({0;1;2;3;4}).<br /><br />Before I explain how to use labels, we must first enable them: Tools>Options...>Calculation tab>Enable: 'Accept labels in formulas' checkbox.<br /><br />Click OK and then in cell B1 Type: 'Jan'. Fill right horizontally Until 'Jul'.<br />In cell A2 type 'North'. In cell A3 type 'East', A4 'South', A5 'West'.<br /><br />You should now be able to see a table format. Now we need some data. So we will use the random function to generate this. In cell B2 type in the formula (without quotes): '=Int(Rand()*1000)'. Fill right until Jul, and then down to West. Now copy the cells B2:H5 and paste values to remove the formulas.<br /><br />Assuming this data refers to some sales figures for the Months Jan-Jul for the four regions shown, we can perform a lookup on the table and find the relevant sales figure at the intersection of a particular combination of Month/Region.<br /><br />To perform the lookup, we can enter the following formula in any blank cell:<br /><br />To lookup the sales figure in May for the South region, all we have to do in enter (without quotes) '=May South' or '=South May'.<br /><br />You can check that this value is correct.<br /><br />Before i discovered this jem, I used to use a nested match/offset function...<br /><br />This simple example can be extended to any number of applications, and make your job as an excel user much easier.<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/4402691647077172579-1564533632370393612?l=mjrhavoc.blogspot.com' alt='' /></div>mjr_havochttp://www.blogger.com/profile/01052001605542968789noreply@blogger.com0tag:blogger.com,1999:blog-4402691647077172579.post-80839131956411602082007-09-29T06:38:00.001-07:002007-09-29T06:38:26.433-07:00Well, this is my first blog...<br /><br />more later...<div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/4402691647077172579-8083913195641160208?l=mjrhavoc.blogspot.com' alt='' /></div>mjr_havochttp://www.blogger.com/profile/01052001605542968789noreply@blogger.com0