i have 2 sheets in excel project: in "sheet1", have big data table, 4 columns: datatable[country], datatable[brand], datatable[parameter], datatable[calculated].
on "sheet 2" have dashboard. have there table called filteredtable presents data datatable. have there drop down command lets user select country, , table filtered accordingly.
i want filteredtable not show data original datatable, let user change [parameter] column. when changing it, want [calculated] column in both tables updated accordingly. if user change country, filteredtable show parameter stored in datatable combination of [country] , [brand]. , if user first country, displayed parameter last 1 user entered.
i bit confused how it. have done far is:
1) read filteredtable rows datafilter, using formula array. mirroing way [country],[brand] , [calculated]
2) in datatable[paramter], read filteredtable[parameter] same way, formula array.
it works fine, untill change country, , parameters in filteredtable not match new country, , in datatable, parameters old country changed 0.
i'm in logical loop. there way out?
thanks
have considered using 3 tables instead of 2? be: original data table, table pulls in values created through manual entry display table, , display table itself.
in vba, create function runs each time formula changed in display table. function should take new data user, , copy altered row onto 'manual entry' table. display table itself, either recalculate manually through vba every time change made, or have formula looks see whether there data in manual table. if there data in manual table, value pulled from. otherwise, data pulled original data table.
Comments
Post a Comment