Column change where pull values from acording to the current month

Hi All,

Please tell me if the below are possible and how.

1. I want column "A" in worksheet "SUMMARY" to change the location where it get the values according to the current month. In same workbook I have worksheets with each month of the year. I want Column "A" in "SUMMARY" to get in MAY the values from worksheet "MAY" Column "A" and next month in Jun the same column "A" in "SUMMARY" to populate with values from column "A" in "JUN" worksheet and so on.

2. If new values (customer accounts) are found one month (but were not there previous month) to display in cell "B" in worksheet "SUMMARY" the word "NEW" and other way around if same value one month is not present anymore to display "NO DATA". The values are customer account numbers and each month are customers new and some that left.

I have Excel 2010. Thanks in advance

We can do this... Can you pls share the excel file with few dummy values.. to answer more specific

vinod said:

We can do this... Can you pls share the excel file with few dummy values.. to answer more specific

Click to expand...

Here it is. Thanks so much for looking into.View attachment GOLD Draft - Copy.xlsm

Any luck here?

This is possible, but I suspect that vinod has not come back because it would involve quite a lot of coding (I wouldn't like to do the coding either).
So this answer probably isn't the kind of answer you were expecting, nor even wanting.
One reason this is difficult is that your data is laid out in multiple sheets, depending on the month. At some point in the future you're going to want to change years as well as months and since the tabs contain no year info you either have to start deleting tabs or include year info in the tab names.

I'd strongly suggest you change the layout of your data so that all the data is in one sheet (one table in fact), but add a column for the date (being say the 1st of the month with year too (this'll be a true Excel date)). Now you'll be able to use this new table as a source for, horror of horrors, a Pivot table.

I know many people are unfamiliar with Pivot tables and recoil at the suggestion of their use, however, writing code to do what you want in your original question involves a lot more hard work than getting used to pivot tables.

Attached, is a version of your workbook with an extra sheet called 'ALL' which has your data copied (as well as a few more month's worth of invented data) from all the months, in one table along with the extra date column.
Next to that is a pivot table with the sort of information you're looking for.
It has a field which shows the %change in revenue from the previous visible month, and this in turn has been conditionally formatted to show directional change. In the date dropdown in cell K5, you can select 2 months (or all of them if you want) to compare changes. Additionally you can filter by account as well.

There's nothing to say New or No Data, but a blank cell means there was no value for that month, so it becomes easy to see. Accounts which don't have values for any of the months you select simply won't show up.

You can simplify the pivot table as in the pivot table starting at cell J60

I wouldn't bother to keep individual sheets of monthly data at all, but have the raw data in a table such as the one I created.

Thanks so much p45cal. If this is the way I could live with I guess. Just need to change it to accommodate this style. Anyway I would love to see if vinod will come back with something. Thanks you both of you for looking into.

Hi Buznac - only through VBA it can be done. Otherwise you need to change the data structure as correctly explained by P45cal.