One cell references current date

R

RodneyW

Active Member
Joined
Sep 24, 2010
Messages
475
Office Version
  1. 2013
Platform
  1. Windows
I need a formula so that the cell under "Current Numbers" is equal the number in the "Actual" cell and advance automatically based on whatever the current month is.

For ease of a formula, assume Current Numbers is Column C

<TABLE style="WIDTH: 379pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=504><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; WIDTH: 42pt; HEIGHT: 12pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 height=16 width=56>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; WIDTH: 111pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl75 width=148 colSpan=3>January</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 113pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl75 width=150 colSpan=3>February</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 113pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl75 width=150 colSpan=3>March</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 height=17>Numbers</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl73>% of Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl73>% of Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl73>% of Goal</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl74 height=16 align=right>5411</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>5481</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>5530</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 align=right>101%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>5656</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 align=right>99%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>6006</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 align=right>94%</TD></TR></TBODY></TABLE>



Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Try:

<b>Excel 2003</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 /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</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><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Current</td><td style="border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">January</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #CCCCFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #CCCCFF;;"></td><td style="border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">February</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #CCCCFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #CCCCFF;;"></td><td style="border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">March</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #CCCCFF;;"></td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #CCCCFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Numbers</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Goal</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Actual</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">% of Goal</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Goal</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Actual</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">% of Goal</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Goal</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">Actual</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCCCFF;;">% of Goal</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">5626</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">5481</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">5530</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">101%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">5656</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">5626</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">99%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">6006</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">5626</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">94%</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A3</th><td style="text-align:left">=OFFSET(<font color="Blue">INDEX(<font color="Red">B1:J1,MATCH(<font color="Green">TEXT(<font color="Purple">TODAY(<font color="Teal"></font>),"mmmm"</font>),B1:J1,0</font>)</font>),2,1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Or;

<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 /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Current</td><td style=";">January</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">February</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">March</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Numbers</td><td style=";">Goal</td><td style=";">Actual</td><td style=";">% of Goal</td><td style=";">Goal</td><td style=";">Actual</td><td style=";">% of Goal</td><td style=";">Goal</td><td style=";">Actual</td><td style=";">% of Goal</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5626</td><td style="text-align: right;;">5481</td><td style="text-align: right;;">5530</td><td style="text-align: right;;">1.01</td><td style="text-align: right;;">5656</td><td style="text-align: right;;">5626</td><td style="text-align: right;;">0.99</td><td style="text-align: right;;">6006</td><td style="text-align: right;;">5226</td><td style="text-align: right;;">0.94</td></tr></tbody></table><p style="width:3.6em;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">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=INDEX(<font color="Blue">D3:IV3,MATCH(<font color="Red">TEXT(<font color="Green">TODAY(<font color="Purple"></font>),"mmmm"</font>),D$1:IV$1,0</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Mr Kowz and Haseeb - -Thanks for the reply. I started working with Mr Kowz first and it seems to work. Here is the formmula adjusted to my sheet:

=OFFSET(INDEX(D3:AM3,MATCH(TEXT(TODAY(),"mmmm"),D3:AM3,0)),2,1)

This pulls the data from cell H5 which is the number for February I want to display. :) Now I need to drag the formula down, or copy/paste it into the cells below.

For example, the formula whcih currently sits in cell C5 I need it to also sit in each cell C6 through C51. Copy/paste into cell C6 doesn't work, it simply references the number in H5. Draging doesn't work because it then references cells outside of Row 3 where the dates are displayed.

<TABLE style="WIDTH: 435pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=579><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; WIDTH: 56pt; HEIGHT: 11.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=15 width=75>12/31/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; WIDTH: 111pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl93 width=148 colSpan=3>
January​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 113pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl93 width=150 colSpan=3>
February​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 113pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl93 width=150 colSpan=3>
March​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75 height=17>LLC Members</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76>Numbers</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79>% of Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79>% of Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79>% of Goal</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 height=16 align=right>5411</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl81 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>5481</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5530</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 align=right>101%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>5656</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 align=right>99%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>6006</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 align=right>94%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ebf1de; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl83 height=16 align=right>5649</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl90 align=right>5626</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>5749</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>5705</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>99%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>5889</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>5705</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>97%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>5964</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>5705</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>96%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=16 align=right>2229</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl91 align=right>2229</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2326</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>2264</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 align=right>97%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2423</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>2341</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 align=right>97%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2520</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>2341</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 align=right>93%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ebf1de; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl83 height=16 align=right>1576</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl84 align=right>1576</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl92 align=right>1606</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>1605</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>100%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>1686</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>1605</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>95%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>1766</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>1605</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>91%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ebf1de; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl83 height=16 align=right>873</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87 align=right>873</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>900</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 align=right>887</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl89 align=right>99%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>927</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 align=right>913</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl89 align=right>98%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>954</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>913</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl89 align=right>96%</TD></TR></TBODY></TABLE>

the number 5411 appears in cell B5 if this helps for reference purposes. "January" sits in cells D, E and F3.

Thanks
 
Upvote 0
Haseeb, I've tried working with your formula, modified it to this:

=INDEX(D3:AM3,MATCH(TEXT(TODAY(),"mmmm"),E7:AL7,0)+1)

<TABLE style="WIDTH: 435pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=579><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=46><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; WIDTH: 56pt; HEIGHT: 11.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=15 width=75>12/31/2010</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl74 width=56>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; WIDTH: 111pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl93 width=148 colSpan=3>
January​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 113pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl93 width=150 colSpan=3>
February​
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; WIDTH: 113pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl93 width=150 colSpan=3>
March​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75 height=17>LLC Members</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl76>Numbers</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79>% of Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79>% of Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77>Goal</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>Actual</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #e4dfec; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79>% of Goal</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80 height=16 align=right>5411</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl81 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>5481</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5530</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 align=right>101%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>5656</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 align=right>99%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 align=right>6006</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5626</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 align=right>94%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ebf1de; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl83 height=16 align=right>5649</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl90 align=right>5626</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>5749</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>5705</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>99%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>5889</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>5705</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>97%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>5964</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>5705</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>96%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8e4bc; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=16 align=right>2229</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl91 align=middle>#N/A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2326</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>2264</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 align=right>97%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2423</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>2341</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 align=right>97%</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>2520</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>2341</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8e4bc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 align=right>93%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ebf1de; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl83 height=16 align=right>1576</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl84 align=right>1576</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl92 align=right>1606</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>1605</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>100%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>1686</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>1605</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>95%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>1766</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>1605</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl86 align=right>91%</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ebf1de; HEIGHT: 12pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl83 height=16 align=right>873</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl87 align=right>873</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>900</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 align=right>887</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl89 align=right>99%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>927</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl88 align=right>913</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl89 align=right>98%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 align=right>954</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85 align=right>913</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ebf1de; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl89 align=right>96%</TD></TR></TBODY></TABLE>

The cell that reads #N/A is the destination cell with the formula you see above. It whould dislpay 2341 from the February cell five cells to the left which is cell H7. In my formula above, E7 is the number for January, AL7 is the number for December.

Thanks
 
Upvote 0
You need to lock the cell reference of heading with dollar signs. ie;

=INDEX(D3:AL3,MATCH(TEXT(TODAY(),"mmmm"),D$1:AL$1,0)+1)

hope D$1:AL$1 contains month names.

<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 /><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><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">40543</td><td style=";">Current</td><td style=";">January</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">February</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">March</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">LLC Members</td><td style=";">Numbers</td><td style=";">Goal</td><td style=";">Actual</td><td style=";">% of Goal</td><td style=";">Goal</td><td style=";">Actual</td><td style=";">% of Goal</td><td style=";">Goal</td><td style=";">Actual</td><td style=";">% of Goal</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5411</td><td style="text-align: right;;">5626</td><td style="text-align: right;;">5481</td><td style="text-align: right;;">5530</td><td style="text-align: right;;">1.01</td><td style="text-align: right;;">5656</td><td style="text-align: right;;">5626</td><td style="text-align: right;;">0.99</td><td style="text-align: right;;">6006</td><td style="text-align: right;;">5626</td><td style="text-align: right;;">0.94</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">5649</td><td style="text-align: right;;">5705</td><td style="text-align: right;;">5749</td><td style="text-align: right;;">5705</td><td style="text-align: right;;">0.99</td><td style="text-align: right;;">5889</td><td style="text-align: right;;">5705</td><td style="text-align: right;;">0.97</td><td style="text-align: right;;">5964</td><td style="text-align: right;;">5705</td><td style="text-align: right;;">0.96</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2229</td><td style="text-align: right;;">2341</td><td style="text-align: right;;">2326</td><td style="text-align: right;;">2264</td><td style="text-align: right;;">0.97</td><td style="text-align: right;;">2423</td><td style="text-align: right;;">2341</td><td style="text-align: right;;">0.97</td><td style="text-align: right;;">2520</td><td style="text-align: right;;">2341</td><td style="text-align: right;;">0.93</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">1576</td><td style="text-align: right;;">1605</td><td style="text-align: right;;">1606</td><td style="text-align: right;;">1605</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1686</td><td style="text-align: right;;">1605</td><td style="text-align: right;;">0.95</td><td style="text-align: right;;">1766</td><td style="text-align: right;;">1605</td><td style="text-align: right;;">0.91</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">873</td><td style="text-align: right;;">913</td><td style="text-align: right;;">900</td><td style="text-align: right;;">887</td><td style="text-align: right;;">0.99</td><td style="text-align: right;;">927</td><td style="text-align: right;;">913</td><td style="text-align: right;;">0.98</td><td style="text-align: right;;">954</td><td style="text-align: right;;">913</td><td style="text-align: right;;">0.96</td></tr></tbody></table><p style="width:3.6em;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">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=INDEX(<font color="Blue">D3:AL3,MATCH(<font color="Red">TEXT(<font color="Green">TODAY(<font color="Purple"></font>),"mmmm"</font>),D$1:AL$1,0</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=INDEX(<font color="Blue">D4:AL4,MATCH(<font color="Red">TEXT(<font color="Green">TODAY(<font color="Purple"></font>),"mmmm"</font>),D$1:AL$1,0</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=INDEX(<font color="Blue">D5:AL5,MATCH(<font color="Red">TEXT(<font color="Green">TODAY(<font color="Purple"></font>),"mmmm"</font>),D$1:AL$1,0</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=INDEX(<font color="Blue">D6:AL6,MATCH(<font color="Red">TEXT(<font color="Green">TODAY(<font color="Purple"></font>),"mmmm"</font>),D$1:AL$1,0</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=INDEX(<font color="Blue">D7:AL7,MATCH(<font color="Red">TEXT(<font color="Green">TODAY(<font color="Purple"></font>),"mmmm"</font>),D$1:AL$1,0</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 
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
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
692
John_w
J

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

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