Displaying and updating data from different sheets

[kubazabran]

Hi! I'm creating Excel File with data of sales that should be updated every month. Ideally, we'd like to achieve the situation, where the editor fills in just one number for each item (= sales for the last month) and all the data and graphs in other sheets will be updated automatically.

I'll try to explain the situation as best as I can.

How it currently works
We have one file with multiple sheets. On the sheet "src_sheet" there are data for each month and items. The idea is, that every month the editor adds new column with the current month and fill in the actual number for each item. Looks something like this. In the end of March, we'd add new column to the left and fill in the numbers.

So the last month is still in column "B".

What we want to achieve (and I still can't figure it how) is this:

Pushing data (- 12 columns) from the same column
The other sheet should display data from the current month and compared them with data with the same month in last years. So basically it should always take the data from col "B" and then from "N" and "Z"). If I use "=src_data!B3," the column is updated every time I added another column to src_data. How can I achieve the situation where it will be always from B3 column, no matter how much cols I add to source sheet?

Automatically adding next month
On another sheet there should be comparison of all months of specific item, e. g. like on the screen. When I add March to src_data, I'd need automatically to add March to this sheet. Now I need to manually add "=src_data!B10" each time I add new month.

Always highlight the last column in a chart
It would be nice to be able to always highlight the last column in the chart.

I'm not sure if anything like this is possible, but it would help me a lot.

Thanks!

 

[kubazabran]

ok, got different idea and we'll try different approach. This can be deleted for now, thanks

 

[smozgur]

Questions are not deleted on the board.
If you have a solution then you can post it to help others who might have a similar problem and then mark it as your own solution. Otherwise, please do not mark a post that is not an answer.