Enter fixed data pattern bassed on user input

hugo15

New Member
Joined
Feb 18, 2010
Messages
23
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.
 

Some videos you may like

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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 18, 2010
Messages
23
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 18, 2010
Messages
23
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Feb 18, 2010
Messages
23
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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
Top