dslreports logo
 
    All Forums Hot Topics Gallery
spc
uniqs
27

compugeek0
Premium Member
join:2002-07-30
localhost

compugeek0 to Blackbird

Premium Member

to Blackbird

Re: [Excel] COUNTIF Counting Cells by Color

I didn't see that on the page sorry. This spreadsheet is on a remote file server and about 1-5 people at a time could be in it. How do you add a "recalc" to a web query? I don't know where it would store the data in excel? I see you can store a query locally, but that doesn't help the other people use it.

Geek

Blackbird
Built for Speed
Premium Member
join:2005-01-14
Fort Wayne, IN

3 edits

Blackbird

Premium Member

said by compugeek0:

...This spreadsheet is on a remote file server and about 1-5 people at a time could be in it. How do you add a "recalc" to a web query? I don't know where it would store the data in excel? I see you can store a query locally, but that doesn't help the other people use it.
I'm not sure that you can implement any kind of recalc command within a webquery .iqy file.

Usually the values accessed by a spreadsheet from a website via a webquery are transferred into a spreadsheet to some kind of target data table. If that transfer involves numerical or textual alterations to existing cell-values in the targeted storage range, that overwriting should of itself trigger an auto-recalculation of the sheet (assuming it's turned on), regardless of whether additional cell cosmetic attributes are involved. Sometimes, it may be necessary to have a simple dummy formula in a cell somewhere outside the data table refer directly to the values of all data-table cells in such a way that a change to any data table value forces the results of the dummy equation to change, and sheet recalculation can be forcibly auto-triggered in that way. (eg: =sum(first-table-cell-address.last-table-cell-address) Even if the dummy equation evaluated incorrectly or to an error, recalc should get triggered if the data in the table range changes.

Is the data being downloaded via web-query to the spreadsheet purely of a cosmetic nature, at the cell level of each cell?
edits: clarity... I hope!

compugeek0
Premium Member
join:2002-07-30
localhost

compugeek0

Premium Member

Click for full size
 
I think this is what you are asking... When I do the query it fills in individual cells.

Geek

Blackbird
Built for Speed
Premium Member
join:2005-01-14
Fort Wayne, IN

Blackbird

Premium Member

said by compugeek0:

I think this is what you are asking... When I do the query it fills in individual cells.
OK... once you select and accept it, the data selected in the webquery has to be transferred by Excel to a range of data values somewhere in the spreadsheet. That full target range is where the spreadsheet accesses the now-imported values... and that's the table that you'll be concerned with. Based on your screenie, try applying the dummy equation from my earlier post, which would now look something along the lines of:
=sum(b5.last-table-cell-address)
to some empty cell outside the data table, and see if a data-query import doesn't now trigger an auto-recalc of the sheet. (Assuming, of course, that b5 is where the first table value that would ever be downloaded actually resides.)

2kmaro
Think

join:2000-07-11
Oklahoma City, OK

2kmaro

I don't have time to dig into this in real detail right now - won't have until this weekend. But a couple of comments that might be of assistance.

First, the Chip Pearson site is probably one of the best regarding dealing with colors/formats of cells being used to provide a count. The code is solid.

Second, the 'Application.Volatile = True' statement makes sure that the function(s) are executed each time the sheet itself is recalculated by Excel for whatever reason.

Third, if you've got ranges that are constantly changing, you may want to take a look at Debra Dalgliesh's Contextures.com site »www.contextures.com/ and look for her assistance in dealing with dynamic ranges. A wealth of information about Excel to be found at her site also.
Table of Tips Contents page: »www.contextures.com/tiptech.html
Detailed instructions on creating and using a Named Dynamic Range:
»www.contextures.com/xlNa ··· #Dynamic

Good luck.