Spreadsheet design guidance

percy83

Active Member
Joined
Mar 11, 2009
Messages
278
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
Does anyone have a clue how to solve this or have had a similar problem?

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

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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

Active Member
Joined
Mar 11, 2009
Messages
278
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

Active Member
Joined
Mar 11, 2009
Messages
278
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,295
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Re: Picture link

Per

What are you trying to summarize?

Could you not use a pivot table rather than formulas?
 

percy83

Active Member
Joined
Mar 11, 2009
Messages
278
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

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,659
Members
418,146
Latest member
Shnn028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top