Power query and protection

Excelnewie

New Member
Joined
Nov 13, 2017
Messages
20
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,829
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
 
Last edited:

Excelnewie

New Member
Joined
Nov 13, 2017
Messages
20
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,829
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

New Member
Joined
Nov 13, 2017
Messages
20
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,829
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)
 
Last edited:

Excelnewie

New Member
Joined
Nov 13, 2017
Messages
20
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,829
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 :eek:ne
 

Forum statistics

Threads
1,082,360
Messages
5,364,927
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top