Enter fixed data pattern bassed on user input

[hugo15]

I've been going round in circles with this for hours and have got no-where. Can anyone help? I 'm trying to get Excel to paste in a set pattern of data based on an input cell.

In row 1 I have months starting in cell B1. B1 = April 12, C1 = May 12, D1 = June 12, and so on

The user would enter the data in column A, starting in row 2

If the date in A2 = May 12 then Excel would enter 10 in cell C2, 20 in cell D2 and 30 in cell E2. If the date in A2 is changed to June 12, then the previous data would be cleared and Excel would enter 10 in E2, 20 in F2 and 30 in G2.

Date would be entered in A3, A4, A5 etc and I would want it to post the same fixed data depending on the date the user enters in teh relevant row.

I've tried IF statements and also some code but can't even get near it working. If anyone can help it would be greatly appreciated.

 

[JoeMo]

Dates in Row 1 and column A are entered as the first day of the month (so May-12 is 5/1/2012). Place formula in B2 and copy across and down.

=IF($A2="","",IF(MATCH($A2,$B$1:$P$1,0)=COLUMN(B1)-COLUMN($A$1),10,IF(MATCH($A2,$B$1:$P$1,0)=COLUMN(B1)-COLUMN($A$1)-1,20,IF(MATCH($A2,$B$1:$P$1,0)=COLUMN(B1)-COLUMN($A$1)-2,30,""))))

 

[Rick Rothstein]

I think this formula placed in B2 and copied across for as many columns as you have dates in Row 1, then copied down as far as you think you will ever have data, will do what you asked for...

=IF($A2="","",IF(TEXT($A2,"mm yyyy")=TEXT(B$1,"mm yyyy"),10,IF(A2=10,20,IF(A2=20,30,""))))

 

[hugo15]

Thanks for your replies. I cut the example data down to simplify the explanation. There is actually 36 data points to enter starting in the month the user enters and continuing for the next 36 months. Sorry, I should have said this originally. Guess this complicates the above solutions somewhat.

 

[Rick Rothstein]

Thanks for your replies. I cut the example data down to simplify the explanation. There is actually 36 data points to enter starting in the month the user enters and continuing for the next 36 months. Sorry, I should have said this originally. Guess this complicates the above solutions somewhat.

If I understand what you are describing correctly, that should not matter to my formula solution at all. As a matter-of-fact, my formula does an extra test that is unnecessary. This formula should work as well as the one I posted originally...

=IF(TEXT($A2,"mm yyyy")=TEXT(B$1,"mm yyyy"),10,IF(A2=10,20,IF(A2=20,30,"")))

Give it a try and see if it works or not.

 

[hugo15]

The actual data pattern is governed by the length of the programme the contracted time points, but an example would look something like this.

10, 0, 0, 10, 0, 0, 30, 0, 0, 10, 10, 0, 0,

I'll have a look at your suggestion Rick when I get to work tomorrow.

 

[Rick Rothstein]

The actual data pattern is governed by the length of the programme the contracted time points, but an example would look something like this.

10, 0, 0, 10, 0, 0, 30, 0, 0, 10, 10, 0, 0,

I'll have a look at your suggestion Rick when I get to work tomorrow.

Uh, after seeing your latest message, I don't think my formula is going to work for you at all. I designed my formula to match what you described in your initial message (the 10,20,30 would be in adjacent cells), but the above quoted pattern is different from what you described initially. As a matter-of-fact, I don't understand how this pattern relates to what you described initially at all. What is governing whether 10, 20 or 30 is outputted???

 

[JoeMo]

Uh, after seeing your latest message, I don't think my formula is going to work for you at all. I designed my formula to match what you described in your initial message (the 10,20,30 would be in adjacent cells), but the above quoted pattern is different from what you described initially. As a matter-of-fact, I don't understand how this pattern relates to what you described initially at all. What is governing whether 10, 20 or 30 is outputted???

Ditto Rick's response for the formula I posted.

 

[hugo15]

Sorry Rick, I tried to cut down the example in the hope that someone could get me started and I'd be able to adapt it for what I need. I just managed to confuse matters!! The actual pattern will be link the one I posted in post #6.

The other way I thought of doing it was to use macro code to do the following:-

Based on the date entered into cell A2, go to the relevant column and paste in the fixed pattern of data into the current cell and the next 35 cells to the right of it.

I can get it to paste in the data but I can't get it to go to the correct start column based on the date entered in cell A2 by the user.

 

[Rick Rothstein]

Quote from Message #9

Sorry Rick, I tried to cut down the example in the hope that someone could get me started and I'd be able to adapt it for what I need. I just managed to confuse matters!!

Simplifying data is almost always a bad idea. Doing so usually gets you a great answer that cannot be applied to your actual situation. Formulas (and VB Code as well) are crafted to be (hopefully) efficient for the data we have been given... usually such solutions are not scalable upward to more complex setups.

Quote from Message #6

The actual data pattern is governed by the length of the programme the contracted time points, but an example would look something like this.

10, 0, 0, 10, 0, 0, 30, 0, 0, 10, 10, 0, 0,

I'm sure the section highlighted in red means something to you, but for those of us here, it means nothing. You have to remember, we have zero familiarity with your data setup, or what you want to do with it, except for what you tell us. That means you have to "teach" us what you already know... referring to something only you know using vague-sounding words is not likely to get you a solution to your question. Sorry for being so blunt, but I know of no other way to drive this point home to you. As for the example pattern you posted... I'm sensing there are other patterns possible... if you don't tell us what they are, or what underlying rule is used to construct them, I'm not sure how we will be able to help you.