Fill in a column based on another

[katiapro93]

I don't even know where to start. Can someone point me in the right direction?

I have a sheet similar to the example below and what I need is for excel to find the column to the far right that matches either the "Start Date" or "Expiration Date" and fill in the amount found on "Price per Issue" on the number of times indicated on the "#of Issues" column into each adjacent column. Catch is each issue is worth two months, so it has to skip a month.

Example below:
1st Issue 2nd Issue 3rd Issue 4th Issue 5th Issue 6 Issue

Company Name

Contract Amount

# of Issues

Price per Issue

Start Date

Expiration Date

18-Jan

18-Feb

18-Mar

18-Apr

18-May

18-Jun

18-Jul

18-Aug

18-Sep

18-Oct

18-Nov

18-Dec

ABC

9,000.00

3

3,000.00

Jan - 18

Jul - 18

3,000

3,000

3,000

<tbody>
</tbody>

Can this be done?

 

[OGCV1]

Hey, so not sure this is the most efficient way especially especially if you have a lot of data. But a nested IF formula; something like

=IF(E2=$D2,IF(COUNTIF($F2:F2,$D2)=$C2,"",$D2),IF(G$1=$E2,$D2,""))

might do the trick.
Was confused though what you want to happen if there was a match for the expiration date.

OGCV

 

[katiapro93]

Thank you so much for your answer. I put in the formula in the first column "18-Jan" and it did fill in the $3,000 I needed, but it didn't go on to fill in the next two issues with $3,000.00. I don't really need the expiration date if we are going to go off the Start date. It was either use one or the other to have a starting point.

Again, I see where the if formula finds and fills in 1 cell, but how can I have it fill in the next number of columns with the same amount?

 

[katiapro93]

Ok, I tried the formula, but it only fills in the column with the date that matches the start date. How do I get it to fill in the next number of columns called for in the "# of Issue" column. In this case "3". So I should see $3,000 fill in on the "18-Jan" column, the "18-Mar" column, and the "18-May" column.
How does the formula move the number of columns indicated by the # of issues? Is it with an offset? The other catch is that an issue is 2 month worth.
HELP PLEASE!!!

 

[OGCV1]

Hey, sorry for the late reply.
Formula needs to be in every column, which is why it isn't particularly efficient if you have a large quantity of data.

 

[RasGhul]

I achieved this by creating a Dates column for every 2nd month on Sheet2;

In Sheet2 Column A,

=EOMONTH([COLOR=#ff8c00]Sheet1!E2[/COLOR],-1)+1

Then add the following below to get every 2nd month after;

=EOMONTH(A1,1)+1

Drag this down at least 10 rows, you should now have a column of dates every 2nd month, which will dynamically update with your "Start Date" on Sheet1E2.

On Sheet1 use this under all of your Dates from Jan-18 - Dec-18

=IFERROR(IF(MATCH(G$1,Sheet2!$A$1:$A$10,0)<=$C$2,$D$2,""),"")

This will return your issue price under matching months, counting # Issues