Range to populate other cells based on variables

Jim in STL

New Member
Joined
Mar 23, 2009
Messages
10
Use Windows XP Home, and MS Office 2007. Also purchased your 2007 Special book published by Que. Wow!

Specifically, what I want to do is be able to enter a small amount of new data per new sale, and, based on the conditions/parameters of that sale have cells in a monthly date continuum self-populate.

Example:

<table style="border-collapse: collapse; width: 360pt;" width="480" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 24pt;" width="32"> <col style="width: 48pt;" width="64"> <col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt; width: 72pt;" width="96" height="17">Product
Name
</td> <td class="xl64" style="width: 48pt;" width="64">Prod. #</td> <td style="width: 48pt;" width="64">Eff. Date of Sale</td> <td class="xl65" style="width: 48pt;" width="64" align="right">Jan-09</td> <td class="xl65" style="width: 48pt;" width="64" align="right">Apr-09</td> <td class="xl65" style="width: 48pt;" width="64" align="right">May-10</td> <td class="xl65" style="width: 48pt;" width="64" align="right">Sep-15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Widget A</td> <td>
</td> <td class="xl64">1</td> <td class="xl63" align="right">09-Jan</td> <td>
</td> <td>
</td> <td colspan="2" style="">$ Comm. Due</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Widget B</td> <td>
</td> <td class="xl64">2</td> <td class="xl63" align="right">09-Apr</td> <td>
</td> <td>
</td> <td colspan="2" style="">$ Comm. Due</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Widget C</td> <td>
</td> <td class="xl64">3</td> <td class="xl63" align="right">09-May</td> <td>
</td> <td>
</td> <td colspan="2" style="">$ Comm. Due</td> </tr> </tbody></table>
Each Widget has a different commission schedule and length of time (# of months and years) for commissions to be received in the future. These commissions have been entered properly/monthly in a contiguous block/range of cells elsewhere.

What I want is for the appropriate month's commission from the data range located elsewhere to be entered in the correct month of the report (above). Each succeeding month's commission to be "automatically" entered in the adjacent cell to the right, following the correct initial month's entry being made.

I believe that I have too many IF/IF/AND conditions to simply do it that way (more than 7). The volume of lookups would (I think) be prohibitive for a VLOOKUP or HLOOKUP, as that is very processor intensive.

I suspect that the answer lies in some combination of range, array, offset, match/index, or macro, however, I cannot figure it out, or find a discussion on the web.

Your thoughts will be GREATLY APPRECIATED.
Jim
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
The distribution of months/years you displayed on your results sheet are irregular. Is there a pattern that would be more obvious with more data? What is the finest date granularity (day, week, month, quarterly)? Is there a quantity column? What is the layout of your commission schedule? (Col A=Wiget #; Col B=% paid immediately; Col C = % paid on the last day of the next month; Col D = % to be paid on the last day of the subsequent quarter. Col E = amount to be paid 24 weeks after sale date, if sales amount for the current quarter exceeded 125% of sales in previous quarter. ...) Does the % or amount paid change as the quanty sold increases? Does it matter if the quantity is distributed over many customers, or strictly by the amount sold to anyone? Do you have to track by person/company as well?

Is it likely that the Commission Due values will change after they are calculated? If not, I would use a range change event macro to write a LOOKUP formula to the appropriate cell then convert the LOOKUP to a value after it is calculated, then there would not be a lot of 'static' LOOKUP cells to delay calculation of new ones. There would only be one LOOKUP active for the cell the is just entered. If you will be pasting a block of data, the macro could be written to account for that as well.
 
Upvote 0
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CMYJACK%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CMYJACK%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CMYJACK%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:eek:ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:Tahoma; panose-1:2 11 6 4 3 5 4 4 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:1627400839 -2147483648 8 0 66047 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Tahoma","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Tahoma; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Tahoma; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;} </style> <![endif]--> Phil,
Thank you very much for your reply! You raise a number of good points.
What I show in the table below is the minimum of columns to give you the idea of what I am seeking to accomplish. Essentially, I want the cells H2:S2 (and years beyond that) to recognize the dates of column D (Feb to Feb, May to May, etc.) as the first cell to be populated and then I want each succeeding cell to the right to populate with the corresponding appropriate value (from a source table/range that I created elsewhere). Of course, I could simply cut and paste the row of months/years of renewal commission, however, I am trying for something more automated (and intellectually elegant!).
Column A (“New Business or Replacement Business”) currently has a dropdown list. The potential choices presently are about 10. I could expand it, by combining Column A numbers with the letters in Column B (“Policy Mode [A, SA, Q, M]). I could then construct in the data range/source an appropriate value for each dropdown choice with the applicable Policy Mode (Annually, Semi-Annually, Quarterly, Monthly), each value falling in the correct month of the data source.
It is this filled-in row of data (from the calculation source) that I want to populate the columns H:S, and beyond.
You created a distinction between commission values that might change in the future. In fact, some could. If I understand what you are saying, if I really want to accommodate potential changes, a different kind of LOOKUP or macro might be indicated.
If I can simply delete the source input (in the case of a policy cancellation), or make changes in certain cells as indicated (perhaps a person goes from Annual payment to Monthly payment), that would be fine with me.
I just want whatever value in each month’s Source table to post/be linked to the output cell in the table I show below.
Am I being clear? I know you are not a mind-reader, so if I need to clarify further . . . .
I will be thrilled to learn something valuable, as I have spent many hours trying to figure this out.
I do thank you for your interest. Table is below.

Jim
<table style="border-collapse: collapse; width: 1030pt;" width="1374" border="0" cellpadding="0" cellspacing="0"><col style="width: 68pt;" width="91"> <col style="width: 58pt;" width="77"> <col style="width: 68pt;" width="91"> <col style="width: 56pt;" width="74"> <col style="width: 68pt;" span="3" width="91"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 48pt;" width="64"> <col style="width: 48pt;" span="9" width="64"> <tbody><tr style="height: 47.25pt;" height="63"> <td class="xl72" style="height: 47.25pt; width: 68pt;" width="91" height="63">New Business or Replacement Business</td> <td class="xl72" style="width: 58pt;" width="77">Policy Mode (A, SA, Q, M)</td> <td class="xl73" style="width: 68pt;" width="91">Modal Premium</td> <td class="xl71" style="width: 56pt;" width="74">Effective Date</td> <td class="xl74" style="width: 68pt;" width="91">First Renewal Month</td> <td class="xl75" style="width: 68pt;" width="91">Months of Renewal Commission</td> <td class="xl74" style="width: 68pt;" width="91">Renewal Commissions Through Month/Year</td> <td class="xl68" style="width: 48pt;" width="64">Jan-09</td> <td class="xl66" style="width: 48pt;" width="64">Feb-09</td> <td class="xl66" style="width: 48pt;" width="64">Mar-09</td> <td class="xl68" style="width: 48pt;" width="64">Apr-09</td> <td class="xl66" style="width: 48pt;" width="64">May-09</td> <td class="xl66" style="width: 48pt;" width="64">Jun-09</td> <td class="xl66" style="width: 48pt;" width="64">Jul-09</td> <td class="xl66" style="width: 48pt;" width="64">Aug-09</td> <td class="xl66" style="width: 48pt;" width="64">Sep-09</td> <td class="xl66" style="width: 48pt;" width="64">Oct-09</td> <td class="xl66" style="width: 48pt;" width="64">Nov-09</td> <td class="xl66" style="width: 48pt;" width="64">Dec-09</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">1</td> <td class="xl67">M</td> <td class="xl69">135.00</td> <td class="xl77">Feb-09</td> <td class="xl66">Feb-10</td> <td class="xl65">36</td> <td class="xl66">Jan-12</td> <td>
</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> <td class="xl69">27.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" height="17">2</td> <td class="xl67">M</td> <td class="xl69">88.00</td> <td class="xl78">09-Mar</td> <td class="xl66">Mar-10</td> <td class="xl65">48</td> <td class="xl66">Feb-13</td> <td>
</td> <td>
</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> <td class="xl69">17.50</td> </tr><tr><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td></tr> </tbody></table>
 
Upvote 0
I assumed the amount to be propagated across the target months was equal to the modal premium (col C) /5.

The first renewal month (col E) based on the Effective Date (col D) using this formula for E2:
Rich (BB code):
=DATE(YEAR(D2),MONTH(D2),1)
this makes the values in col E equal to the first of the month

The last comission month date is calculated based on the first month and duration. For G2:
Rich (BB code):
=DATE(YEAR(D2),MONTH(D2)+F2,1)
This date also must be the first of the month.
In your example, you showed a 36 month range to start Feb 09 and end Feb 12, this is 37 months. I assumed the actual last month was Jan 09.

I inserted a column H to ensure that the formulas were copied far enough across the columns. If all is well, the value in col H should equal the value in col F. Add a conditional format to highlight col H if they are not equal. H2:
Rich (BB code):
=COUNTIF(I2:IV2,">0")

The dates in column I and over, row 1 must be the first of each month. An easy formula to copy across to make that happen is I1:
Rich (BB code):
=DATE(2009,COLUMN()-8,1)

The comission date values are based on the date in row 1 above it, the dates in column E and G in the same row and the amount in column C of the same row. For I2:
Rich (BB code):
=IF(AND(I$1>=$E2,I$1<$G2),$C2/5,"")

This is a formula based response. If it works for you, let me know and I will add a bit of code that will create the formulas for the data you have entered in columns A-G, then convert them to values so the spredsheet will not be recalculating static numbers each time something is added. If I guessed wrong on something, let me know.
 
Upvote 0
Phil,

Appreciate your response. I have studied it, and actually understand SOME of it.
In the following cell entries you provided, two things:
<!--[if !supportLists]--> 1. Cell H2 keeps showing “Jan-00”, no matter how I enter/paste from your text the formula “COUNTIF(I2:IV2,”>0”);<!--[endif]-->
<!--[if !supportLists]--> 2.Cannot find why the “-8” in the Date formula in cell I1.<!--[endif]-->

I may have made this more complicated than it really is. If so, I apologize to you for wasting your time.

What I have is about 10 different products, each with potential Annual, Semi-Annual, Quarterly, or Monthly (commission) payments, each having its own code (as in “1A”, “2SA”, “3A”, “4Q”, “5SA”, “4M”,etc.):
1. I can enter that pertinent information in a table elsewhere in the workbook. Each potential amount having its own row, and extending as far to the right as the commission would be paid. The commission would be calculated across each row (s)--say Worksheet2, F10:GC50;<!--[endif]-->
2. I would enter the appropriate information--client name, business and payment code, and effective date;

<!--[if !supportLists]--> 3. I then would want that row of commission entries to be triggered by the "Effective Date" I enter in cell D2:D4, etc. in the example I sent you, starting in the appropriate beginning month, and continuing as far to the right as commission will be paid in the future (in other words, the range of commission duration I entered on Worksheet2.<!--[endif]-->
<!--[if !supportLists]--> 4.The date for commissions to commence is the “Effective Date” column of Worksheet1, and I want nothing to appear until the corresponding month (ex: I1,J1,M1, etc.), and then I would want each succeeding cell to the right to self-populate, based on the range for that particular product/payment code I had entered on Worksheet2.<!--[endif]-->
<!--[if !supportLists]--> 5.As the future becomes the present, I anticipate having commissions entered 3 months from now, 1 year and 3 months, 5 years, etc. In other words, a work in progress.<!--[endif]-->

It may be that a simple VLOOKUP of the business/commission code would get me to the right row of commission entries I want transmitted to Worksheet1, however, I simply do not know how to have the range "self-populate" under the appropriate months of Worksheet1. Not sooner, not longer, and for the specific number of months I have entered commissions on Worksheet2.

Has this explanation helped clarify what I am seeking to do? As I mentioned in the first posting, I think there are too many variables for If/And, etc. I suspect that some kind of RANGE reference/formula may be my answer, but, I cannot find an example close enough for me to be able to run with it from there.

Thank you again!

Jim
 
Upvote 0
Format H2 as a number. It is counting the number of columns in I:IV that have a number in them. This number should match the the Months of Renewal Commission value in the same row. You should add a conditional format so that if the value of this H2 did not equal the value of F2, the cell would be highlighted.
The formula for the H2 conditional format would be =H2<>F2
And change the pattern to red.

Cell I2 is set equal to this:
=DATE(2009,COLUMN()-8,1)

I is the 9th column so COLUMN(I2) returns the value 9. The above formula would then be =DATE(2009,1,1) which would return the date Jan 01 2009.
Copying this column to cell J2 would evaluate as =DATE(2009,10-8,1) which would return Feb 01 2009.

I'll respond to your other questions in another email in the next day or so.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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