Spreadsheet design guidance

[percy83]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’ve got a 250 row querytable that constantly expands/retracts. On this table I’ve applied the autofilter function to be able to filter all projects in the list based on supplier, sales area etc. <o></o>
<o> </o>
What I want to do now is adding a summary on what is shown on the screen (by using the sumproduct function). I was thinking of putting this summary table on the right on my list since the end-user shouldn’t have to scroll down to the bottom every time and also I can’t think of how to solve it practically as my list expands/retracts all the time. The problem is that when I put it on the right side it will also be filtered since I don’t know how to “lock” these cells from being filtered. <o></o>
<o> </o>
Does anyone have a clue how to solve this or have had a similar problem?

Thanks so much for this great forum.<o></o>
<o> </o>
Best regards<o></o>
<o> </o>
Per<o></o>

 

[Sal Paradise]

Create a new sheet. Create your summary sumproduct table. Select the cells. Edit -> Copy.

Go to your Query Sheet. Hold down shift, select the edit menu, and go to 'Paste Picture Link'

Ta-da. It will never disappear even if you hide rows.

 

[percy83]

Create a new sheet. Create your summary sumproduct table. Select the cells. Edit -> Copy.

Go to your Query Sheet. Hold down shift, select the edit menu, and go to 'Paste Picture Link'

Ta-da. It will never disappear even if you hide rows.

It looks as it works but my Excel freezes when I try to use it. Any idea why?

Best regards
Per

 

[percy83]

Picture link

I've done some testing and when I apply autofilter to my query table the subtotal funtions actually changes and then excel crashes. It dosent matter if paste the picture link in the same sheet or a brand new one. The result is still the same. Stupid Excel crashes!

Is there any other way to exclude rows or area from being filtered or does anyone have another solution?

Best regards
Per

 

[Norie]

Re: Picture link

Per

What are you trying to summarize?

Could you not use a pivot table rather than formulas?

 

[percy83]

Re: Picture link

Per

What are you trying to summarize?

Could you not use a pivot table rather than formulas?

The query table itself is a list currently ongoing projects and my idea was that the end-user could apply all filters wanted and then get an immediate answer on how many, what size and what category the project currently visible are.

I've got a few macros in this workbooks aswell and could that be the reason for not being able to paste picture link?

Best regards
P.

 

[Sal Paradise]

Re: Picture link

No idea why your Excel is crashing. You could add some msgboxes to your macros to see where it's crashing and help debug, or you could shrug and find another way to do it.