limit columns viewable between sheets

Tanya Brooks

Looks like Dynamic View may address my issue, but we cannot afford it and so I am looking for soultions using basic functionality.

We have an HR sheet that contains columns with basic non-sensitive information, such as name and title. HR sheet also contains columns with sensitive information such as salary.

When new employees are added to HR sheet we'd like the non-sensitive information to copy to operations sheet where we track additional columns for phone #'s, keys assigned, etc. Person maintaining operations sheet should be able to see all columns from HR sheet other than salary info. Using workflow to copy rows from HR sheet to Operations sheet when rows are added copies all information, sensitive and non-sensitive.

How can we poulate only some columns in Operations spreadsheet from data entered into HR spreadsheet?

EXAMPLE:

HR sheet has columns A through F

Operations sheet needs data only from columns A through D

Cell Linking only connects existing data EX: link HR Column A to Operations Column A.

I can hide the sensitive columns in the Operations sheet, but there is no way to "lock" the hide.

Suggestions?

Paul Newcome

One option would be to track ALL of the data on the same sheet and then use reports with specified columns shown.

Tanya Brooks

That would work for view only. In this case, the operations sheet is part columns from HR sheet and part columns that are not in HR sheet.

Paul Newcome

Reports can be edited.

I am aware some columns are on different sheets. That's why I suggested having one master sheet that has all columns. Then pulling reports from that one master sheet to limit which columns are shown.

I know it isn't perfect, but options are (unfortunately) pretty limited without Dynamic View.

Andrée Starå

@Tanya Brooks

Hi Tanya,

To add to Paul's excellent suggestion.

Another option could maybe be to use cross-sheet formulas and INDEX/MATCH to show specific information in other sheets as needed.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Tanya Brooks

Hi Andree -

Index and Match might work. Can INDEX/MATCH return values for a range of consectiove fields, or will it only return one value at a time (ie, requires a formula in each field)?

EXAMPLE: I would like to pull the following consecutive column values FROM HR Sheet TO OPERATIONS sheet. Best would be to match on EID.

Having trouble with syntax.

Thank,

Tanya

Andrée Starå

@Tanya Brooks

You could join the fields together or use a VLOOKUP formula instead. It's easier, but not as secure.

What do you think?

Tanya Brooks

Hi Andree -

IApologies, I am not clear on what you mean by "join fields together. Can you give me an example of the syntax for INDEX/MATCH and for VLOOKUP?

Thank you,

Tanya

Andrée Starå

@Tanya Brooks

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, and I'll share it with you.