Range to populate other cells based on variables

Jim in STL · Mar 23, 2009

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

pbornemeier · Mar 24, 2009

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.

Jim in STL · Mar 24, 2009

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> 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>

pbornemeier · Mar 24, 2009

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.

Jim in STL · Mar 25, 2009

Phil,

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

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;
2. I would enter the appropriate information--client name, business and payment code, and effective date;

 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.
 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.
 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.

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

pbornemeier · Mar 25, 2009

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.

Range to populate other cells based on variables

Jim in STL

New Member

Excel Facts

pbornemeier

Well-known Member

Jim in STL

New Member

pbornemeier

Well-known Member

Jim in STL

New Member

pbornemeier

Well-known Member

Similar threads

Forum statistics

Share this page

Range to populate other cells based on variables

Jim in STL

New Member

Excel Facts

pbornemeier

Well-known Member

Jim in STL

New Member

pbornemeier

Well-known Member

Jim in STL

New Member

pbornemeier

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock