Data labels &data table labels to be dynamic

[nmounir]

Hello Everyone,

I made a table where values are spilled using sortby function. The table expands and contracts based on the date selected. When earlier date is selected more data populates and so the tbale expands. The sum value and % values change accordingly. The total and % values cell positions also change as I am using sortby function.
I used conditional formatting to select % or currency sign based on finding the word "sum" and % and it works perfectly as the data expands or contracts. The graph is also dynamic using the offset function. My issue is the data labels and data table on the chart as they do not show the correct currency and % signs on the chart. It seems that data labels and data table take the signs from the hard coded values in the cells and not from the conditional formatted cells. Is there any way to get this fixed ?

Thanks in advance

 

[nmounir]

To better explain it, say the table is from A1:B4 and in A5 has got a formula for the sum value and the % value is shown in B5. When I select another date, the data increases and so the table increases. The sum and % values are anymore in cells A5 and B5. Their positions change to A10 and B10. I used conditional formatting to show the correct label ( currency and % signs) wheb the sum and % cells position change and all is OK. The issue I am experiencing is in the data labels and data tables on the chart as they do not show the correct sings because the sum cell and % cell position change in the original table.