Hi all,
I work for Acme Inc.
Acme Inc. is a box filled with the type of people who live and breath excel and use it like a swiss army knife.
I'm a vba developer.
Swiss army knife is fine, except lots of the sheets these folks work in are presented to clients and crossed referenced to other data.
The best way to describe it is that Acme Inc. loves to do data entry in excel sheets that resemble reports.
Maintaining the code that generates half a monster and a user entry form, but considered as data thing involves a lot of overhead. There can be anything anywhere, headers about different sections, column after column associated to each year, but side by side. Tables next to other tables that are difficult to grab with each other. Tables that check and validate other tables. Checks that are added to the same worksheet but way out in right field because changing the order of other checks that sometimes appear and sometimes don't causes errors.
Or to boil it all down to it's essence. Acme Inc Excel users enjoy inputting data into sheets formatted like a report that would be generated from data instead of into a place where one would enter data.
Acme Inc, has managed to maintained this madness fairly well, at the cost of the sanity of several developers I'm sure.
Is there a way to trick users into thinking they are updating their precious excel report data sheets, when in reality, their updates are stored into a sane table, and their reports are simply generated on the fly. Without too much rehashing of data.
For you see, there are 100s if not 1000s of these excel programs spread out everywhere, each doing things and formatted differently.
Acme users are very particular and don't take kindly to any suggestions of normalizing data entry, many a fangs have been presented to their inability to have control over their excel sheet, despite their control, controlling other users lock and key.
I would love to include an example, but no one example would cover all cases, I see no solution with these handcuffs to having any normalized data present.
If there is a way however, to have their updates seem seamless to some back-end, whether it's a separate worksheet or what not, without having to regenerate the view of the report that would be amazing.
My lousy brain can only come up with the following.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]field[/TD]
[TD]hard_pasted_value[/TD]
[TD]formula_xl[/TD]
[TD]formula_cust[/TD]
[TD]location (read: worksheet)[/TD]
[TD]linked_cell_value[/TD]
[/TR]
[TR]
[TD]product_price[/TD]
[TD]12.22[/TD]
[TD]"=A4*C4"[/TD]
[TD]=[product_manu_cost]*[profitability_quot][/TD]
[TD]Product Inventory[/TD]
[TD]=[Product Inventory]!D4[/TD]
[/TR]
[TR]
[TD]user_input[/TD]
[TD]Talked to custoemr[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]Product Inventory[/TD]
[TD]=[Product Inventory]!E4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Some sort of table that would hold the hard values, and the values shown on the sheet as well as how to derive those values. Whenever some major change was made to the sheet by some macro getting or formatting data, it would first hard paste the value from the linked cell field into the hard_pasted_value, and then do it's action, but here's the kicker, for each piece of data it would have to find where it now newly resides.
Seems like it would be better if the report was referencing this table as the truth rather than this table referencing the worksheet that gets formatted and manipulated and altered constantly. Solution seems very inadequate, but that's all I got.
Thank you for all your thoughts, I know this is possibly an impossible requirement, unless there is an excel feature I am completely unaware about.
I work for Acme Inc.
Acme Inc. is a box filled with the type of people who live and breath excel and use it like a swiss army knife.
I'm a vba developer.
Swiss army knife is fine, except lots of the sheets these folks work in are presented to clients and crossed referenced to other data.
The best way to describe it is that Acme Inc. loves to do data entry in excel sheets that resemble reports.
Maintaining the code that generates half a monster and a user entry form, but considered as data thing involves a lot of overhead. There can be anything anywhere, headers about different sections, column after column associated to each year, but side by side. Tables next to other tables that are difficult to grab with each other. Tables that check and validate other tables. Checks that are added to the same worksheet but way out in right field because changing the order of other checks that sometimes appear and sometimes don't causes errors.
Or to boil it all down to it's essence. Acme Inc Excel users enjoy inputting data into sheets formatted like a report that would be generated from data instead of into a place where one would enter data.
Acme Inc, has managed to maintained this madness fairly well, at the cost of the sanity of several developers I'm sure.
Is there a way to trick users into thinking they are updating their precious excel report data sheets, when in reality, their updates are stored into a sane table, and their reports are simply generated on the fly. Without too much rehashing of data.
For you see, there are 100s if not 1000s of these excel programs spread out everywhere, each doing things and formatted differently.
Acme users are very particular and don't take kindly to any suggestions of normalizing data entry, many a fangs have been presented to their inability to have control over their excel sheet, despite their control, controlling other users lock and key.
I would love to include an example, but no one example would cover all cases, I see no solution with these handcuffs to having any normalized data present.
If there is a way however, to have their updates seem seamless to some back-end, whether it's a separate worksheet or what not, without having to regenerate the view of the report that would be amazing.
My lousy brain can only come up with the following.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]field[/TD]
[TD]hard_pasted_value[/TD]
[TD]formula_xl[/TD]
[TD]formula_cust[/TD]
[TD]location (read: worksheet)[/TD]
[TD]linked_cell_value[/TD]
[/TR]
[TR]
[TD]product_price[/TD]
[TD]12.22[/TD]
[TD]"=A4*C4"[/TD]
[TD]=[product_manu_cost]*[profitability_quot][/TD]
[TD]Product Inventory[/TD]
[TD]=[Product Inventory]!D4[/TD]
[/TR]
[TR]
[TD]user_input[/TD]
[TD]Talked to custoemr[/TD]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]Product Inventory[/TD]
[TD]=[Product Inventory]!E4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Some sort of table that would hold the hard values, and the values shown on the sheet as well as how to derive those values. Whenever some major change was made to the sheet by some macro getting or formatting data, it would first hard paste the value from the linked cell field into the hard_pasted_value, and then do it's action, but here's the kicker, for each piece of data it would have to find where it now newly resides.
Seems like it would be better if the report was referencing this table as the truth rather than this table referencing the worksheet that gets formatted and manipulated and altered constantly. Solution seems very inadequate, but that's all I got.
Thank you for all your thoughts, I know this is possibly an impossible requirement, unless there is an excel feature I am completely unaware about.
