VBA obtain the breakdown of the total value of a cell

[gubertu]

Hi all,

I have the following doubt. Hope you could help me.

I have two sheets: 1) "Data" and 2) "Fixed assets".

Tab "Data"

	A	B
1	Accounts	Values
2	210	2.000
3	220	3.000

Tab "Fixed assets"

	A	B	C
1	Titles	Values	Formula
2	Buldings	5.000	SUMIF('Data:A:A;210;Data:B:B)+SUMIF('Data:A:A;220;Data:B:B)
3	Machinery	0

The value of cell B2 of tab "Fixed assets" is 5.000. I would like to press that cell and know exactly what is the breakdown of those 5.000. For example, to be redirected to tab "Data" and have the cells B2:B3 highlighted or something.

Is there any code or any way in Excel to do this?

Hope you could help me.

Thanks in advance

 

[WarPigl3t]

Using your current table, that is impossible. If you create a helper column where you can put the search value 210, then it is possible. You could even put 210 and 220 in the same cell with a comma as a delimeter and it could be possible. For example say "Fixed assets" C2 you entered 210|220. You used the pipe as the delimeter. On selection change, make it check which cell you selected. Then it would run code. The code would see what row the celected cell is in. You selected B2, so it would use row 2 and it would check your helper column C to see what is in there. It would check if there is a | pipe and if there is not, it would just run the code one time. If there is 1 or more pipes, it would run the code enough times to check if each value exists in your "Data" sheet. First it would check 210, then 220. It would highlight when results are found. It would clear any highlights from previous results before running the code.

I don't have time to write that code for you, as it would take me an hour. Just wanted to provide you with an idea for a solution because your current table makes it impossible to accomplish your task.

 

[gubertu]

Hi and thanks for the reply.

I see your approach, but for me is quite complicated.

What if I build the charts based on a Pivot table? I can configure it and if I double click on a cell, I would know the breakdown of every cell.

Thanks