Replace VLOOKUP & SUMIFS (with SUMPRODUCT?)

[MJBVLC]

Hi everyone,

I am sure this has been asked before, but I couldn't find it in the forum.

I have
- some timesheet data showing how many hours an employee worked on a project under various task codes
- a table of the hourly rate per person (sensitive, hidden)
- a summary table to show the labour cost for each project, sub divided by task code

I can make the summary table work by adding a VLOOKUP column to the timesheet data to work out the cost of each entry. Then I can use SUMIFS to summarise this cost in the summary table.

However, I need to make the timesheet data available to all users, but keep the hourly rate data hidden. I can't work out a formula to directly summarise the cost without going via the VLOOKUP column. I have a feeling that SUMPRODUCT might be the answer, but I can't get my head around it. If possible, I would prefer to avoid using array formulas as I have to pass this over to someone who is not familiar with these or the CSE method of entering them.

I have uploaded a file showing a simplified format of the data with the VLOOKUP / SUMIFS calculation in place.

Thank you in advance for your help.

 

[Luke M]

Hi MJCVLC, and welcome to the forum!

How hidden does the info need to be? If highly sensitive, then you'll need to have two separate workbooks, as XL isn't meant to protect information that well.
If not, could just take current sheet, hide columns F:I.

Since this looks to be sensitive pay information, I'd probably leave it in the current form, and when I need to distribute info, copy K:L, paste as values to new workbook, and send out that workbook.

 

[MJBVLC]

Hi MJCVLC, and welcome to the forum!

How hidden does the info need to be? If highly sensitive, then you'll need to have two separate workbooks, as XL isn't meant to protect information that well.
If not, could just take current sheet, hide columns F:I.

Since this looks to be sensitive pay information, I'd probably leave it in the current form, and when I need to distribute info, copy K:L, paste as values to new workbook, and send out that workbook.

Hiding columns was my initial approach, and it it's my best solution so far. But in order to prevent a user from unhiding the VLOOKUP column I have to lock the columns either side and protect the sheet with a password. This then prevents any formatting of any column in the sheet and the user can't insert rows. It removes too much functionality from the sheet. The wage data can go on another sheet and I can protect that as much as I need, but it's the VLOOKUP column that's causing the problems.

This sheet will be in daily use, building up the data for the year. I have to hand it over for use "as is". Also, I can't apply any VBA (Max Excel 2008!!!).

Any ideas? I'd love to learn a new trick!

 

[Luke M]

Can we put all of A:I in a hidden sheet?

Just to be sure, anyone with intermediate level of XL ability will be able to get the info, no matter how you hide/protect sheets. How sensitive is the data, is the real question.

 

[MJBVLC]

Can we put all of A:I in a hidden sheet?

Just to be sure, anyone with intermediate level of XL ability will be able to get the info, no matter how you hide/protect sheets. How sensitive is the data, is the real question.

I'd like to get away from the "hiding" solution; I can't go in to the full usage of the file, but hiding information that is in daily use is not an option. I'd really like to explore the idea of a formula to solve the problem. Thanks.

 

[Chihiro]

In that case I'd highly recommend going with solution Luke suggested in post#2.

Only secure data (no matter the medium) is the data that's not present (on the report file).

I only publish interactive reports and/or full reports to those with sufficient privileges (within the company). All external clients and internal client with insufficient privileges get raw data and/or report with pertinent info only.