Copying a Column based on List Value

[happymonody]

Hello I was wondering if you could help.

I am trying to copy the values in two columns based on the value selected from a list.

For example, when day of the month is selected in 'Daily Planner' in cell B2, I would like it to copy both columns from 'Edit This One' that correspond with that day of the month (when 3 is selected it copies columns I and J and pastes it it in Daily Planner Columns B and C). I have attached an example of my spreadsheet.

I have tried all sorts but not that confident with VBA and Macros and I've been unable to get my head around the other examples on the net.

Any help would be greatly appreciated.

Thanks

[Glenn Kennedy]

You may (or may not) be OK with a formula-based solution. One thing is for sure, your description (in your post) and your sheet layout (the attached file) didn't seem compatible; so I really don't know what output you want. If this is NOT what you want, please reattach your sheet showing EXACTLY what you desired result should look like.

I used this formula (after de-merging one totally un-needed merged cell (cell B2/C2) - don't merge cells widen them, deepen them - do anything, but don't merge them!!!.. they're the work of satan.

=INDEX('Edit this one'!$C$5:$BL$7,MATCH('Daily Planner'!$A5,'Edit this one'!$A$5:$A$7,0),MATCH('Daily Planner'!$B$2:$B$2,'Edit this one'!$C$3:$BL$3,0))

[Richard Buttrey]

Hi,

Your data layout does not easily lend itself to analysis and you are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, your layout is one such case.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality. You are also committing the sin of merging cells. Most of us avoid these like the plague since they just cause too many problems for reasons that needn't concern us at the moment.

How open are you to changing the layout of your data sheet 'Edit this one'

[xladept]

Hi Happy,

Here's some event code for your daily planner sheet:

Please Login or Register  to view this content.

And Here's your book with the code and macro-enabled:


*If you have more than 3 employees just change the red number to your number of employees!

[happymonody]

Thanks for the replies everybody.

The last solution works perfectly. I shall take your comments on board re merging cells etc.

I do love this site!

[happymonody]

Thanks for the replies everybody.

The last solution works perfectly. I shall take your comments on board re merging cells etc.

I do love this site!

[xladept]

Hi Happy,

You're welcome and thanks for the rep!