Filter-proof equations

Welcome to the forum.

1. Unprotect the sheet and make your changes.
2. Impossible to answer without knowing what the formulae are.

OK - we are going to need to see the workbook.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Explain these results:

Excel 2016 (Windows) 32 bit

	A	B	C
1	Reference	Weight installed	% complete
2	HZX456	2.7	47
3	HRK367	4.15	56

Sheet: Output

What's the logic here?

Your data layout on the source sheet is poor - are you prepared to rearrange it to make your work easier?

Originally Posted by HRTTW

However, any time anyone filters either the source sheet or the output sheet all the sums break.

Also have tried turning the filters off, but the option is greyed out.



TIA

Please explain how filtering either sheet breaks the formulae on the output sheet

In that case, you need to use PowerQuery to remodel the source data for you. Easily done!

You need to answer Richard's question.

You are going to need to come up with a sample workbook that demonstrates this with a sort done to show the effect. I am struggling to visualise what you are doing here.

Originally Posted by HRTTW

The output sheet no longer references the correct cell.

For example ( if you imagine there are way more lines in the source sheet) say you sorted the fruit into alphabetical order to make it easier to find the fruit you wanted to record, then the output file no longer references the right cell

You started off by indicating your problem was cause by using the Filtering Functionality. Now you are talking about Sorting. You need to be quite clear.
In any case sice your formulae refer to a total row, whether you filter or sort the data that total row will always be in the same place.

In any case this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore as Ali has asked, please upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results and the steps you have taken when adding new or deleting old data.

To get rid of the filter arrows, click on the data filter (funnel) icon on the Data ribbon to deselect it.

If the original question was about the filter arrows, why did you distract us with a thread title about filter-proof equations (or formulae)?

Good luck with your project.

Originally Posted by HRTTW

For security reasons I cannot upload my spreadsheet. Strings are not relevant as it's only the number's involved in the equations.

Everyone seems to have got distracted from the original question: the little drop down arrows that exist on the header cells are locked on despite it being an unprotected document and I can't get rid of them to stop people trying to filter/sort/etc

For the cell reference issue I'm just going to go through every cell and add $$

No, we were not losing sight of the question. You'd specifically mentioned formula breaking when someone filtered the source or output sheet.
No. The formula DON'T change when you do that. That's why we asked for clarification. Formulae changing was a complete red herring and with respect you were causing a distraction not us.

When we ask you to upload a workbook we expect you to desensitise any data first. I mentioned strings merely as an example of stuff that's often relevant when someone uploads a trivial workbook that isn't representative.

I've no idea what you mean by go through every cell and add $$.
We're quite happy to help further should you wish but need to understand precisely what you want.

We're quite happy to help further should you wish but need to understand precisely what you want.

I'm not on this occasion, I'm afraid.