# Enter fixed data pattern bassed on user input

#### hugo15

##### New Member
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.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
Rich (BB code):
``=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,""))))``

Last edited:
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,""))))

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.

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.

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.

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

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.

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.

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.

Replies
12
Views
416
Replies
2
Views
247
Replies
1
Views
203
Replies
3
Views
182
Replies
19
Views
191

Threads
1,203,601
Messages
6,056,212
Members
444,850
Latest member
dancasta7

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

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