Posts by Humperdinck

Re: Confidential data to be used in calculations in public workbook

Thanks John, makes perfect sense to me.

I guess what I will propose is:

• Store the table inside the file (very-hidden, protected, etc, for what is worth). I will encrypt it with some simple two-way logic.
• Put the calculation and the decryption step inside a pwd-protected UDF

This should discourage most smart users. If still too weak, I will mention the DLL option, but we are out of my zone here. Thanks very much for mentioning this.

Can I assume the only “correct” solution would be to have a server side layer, built with whatever tool?

Hello All,

we have a table with data like:

[TABLE="width: 500, align: left"]

[tr]

[TD="align: center"]PRODUCT
[/TD]
[TD="align: center"]PRODUCTION COST
[/TD]

[/tr]

[tr]

[TD="align: center"]abc[/TD]
[TD="align: center"]123[/TD]

[/tr]

[tr]

[TD="align: center"]xyz[/TD]
[TD="align: center"]456[/TD]

[/tr]

[/TABLE]

we need a tool to allow the users to:

• select the product (simple drop-down)
• input a proposed selling price

a simple formula then (v)looks up the cost, checks if the price is high enough to sell via some logic and provides the user with a simple "YES or NO".

The user must be unable to access the mentioned table. I'd like to understand if there's a reasonable secure way to do this in excel, or what solution we could go for.
I assume the easy way would be to hide the table somewhere in the file and protect with pwd, but it's something a good user (we have some) could violate in few minutes with some bits of VBA.

Thanks in advance.

Re: Summing only some rows of a matrix based on condition

Quote from Herbds7;540447

=SUMPRODUCT(data*(tags="a"))

Perfect, thanks.

Hi All,

nice to meet you, my first post here. I'd like to have your opinion:

Problem: I've one matrix of data and a tag for every row

[Blocked Image: http://img26.imageshack.us/img26/2500/tagsk.jpg]

The ranges are named Tags and Data. I need to get the total of "data" for the values in rows which have a selected value in Tags.

I've come up with this to sum for example, tag "a"

(array formula)

=SUMPRODUCT(data;(MMULT(--(tags="a");TRANSPOSE((1+0*ROW(INDIRECT("1:"&COLUMNS(data))))))))

It works but is basically not understood by most endusers, do you see other options to do this in a more "friendly" way?

Thanks in advance.