Power query and protection

[Excelnewie]

Hi all

SITUATION:
I have several individual workbooks for data input. 1 formula and some data validation. These have been combined into one table through power query which forms the 'source data' for another worksheet where the user inputs a criteria and the result is all the relevant data rows based on that criteria. There are many formulas (INDEX/MATCH/SUMIFS/AGGREGATE etc).

PROBLEM:
I want to protect this this sheet so my formulas are not overwritten but since it is drawing from the query result, I can't.

Does anyone one have a suggestion for me to protect my formulas

thanks

 

[sandy666]

since it is drawing from the query result, I can't.

Right, you can't

you can convert QueryTable in the sheet to range so your "table" will not change and your formulas should work

 

[Excelnewie]

Hi thanks for your reply

i don't think I understand your workaround. Everything is working, I just want it protected so the formulas are not typed over.

I think ink you are saying this is not possible?

 

[sandy666]

I understood you've QueryTable then load it into the sheet, then enter to this table any formula(s) and you want protect these formulas ?

 

[Excelnewie]

No, the formula sheet is already there.

there are 5 teams each with their own workbook. All the player details are on these sheets. They play a game and the results are entered (opponant, location, score etc)

the query brings them together

The manager uses data validation to choose a criteria eg who played Team X during the season ( "team X" is the criteria and this brings up all the games against team X from all the 5 sheets since all teams played team X during the season. The formulas draw data about each individual game against team X - date/location/score/etc

then the manager selects team Y and gets those results. He uses this sheet to return information, not put anything in.

 

[sandy666]

PowerQuery refresh data from the source(s)
You consolidated few workbooks via PQ into eg. one QueryTable
then based on this QueryTable you've another formulas somewhere?
What PQ has to do with overwriting formulas?

if you want protect formulas lock the cells, set pass with criteria what there can be done or not

I think I really don't understand your logic

with source you can do what you want
With QueryTable you can refresh data from the source (values)

 

[Excelnewie]

Thanks sandy
pq doesn't have anything to do with overwriting formulas. I just wanted to protect a power query but I think (as you said ) I can't.

I will just keep a blank version safe and re-input any formulas that get written over (if they do)

 

[sandy666]

one protection for PQ is Protect Workbook (Structure) so you cannot access to PQ and cannot change there anything
.
You need to check if this option is ok for you, there is not too much choices for that, honestly ne

 

[Excelnewie]

Thanks sandy

 

[sandy666]

You are welcome

Have a nice day