Posts by gc052

    Re: Contiguous Weighted Average Excluding Zeroes

    Sorry, I forgot to add in that the rows in between aren't actually blank, I meant to say that the rows with values will be subtotals. I've already got a method to filter out the intermediate rows (sumproduct, A:A, B:B, C:C)/(Sumproduct, B:B, C:C) where C has 1s and 0's that cancel out the 0 values in B

    If I use that method again it will throw off the grand total, so I was wondering if there was a way to model the formula that I mentioned above.


    Is there a way to calculate a contiguous Weighted Average for rows, omitting/subtracting weights that have zero values? Something to the effect of

    W=Weight, V=Value

    [(W1*V1)+(W2*V2)+(W3*V3)+(W4*V4)]/(Wtotal-Wn) , where Wn corresponds to a Vn that = 0

    The only method that's popping up in my head right now is a ton of IF's for each scenario where Vn=0, since its about 5 contiguous rows that I'm doing this for, and I've already implemented a 3 array sumproduct that uses 1's and 0's for significance in a third column. These 5 rows are subtotals of the 1's and 0's rows, so doing the same thing would include all the intermediate rows.

    I'm attaching a scrap book for reference.

    Please let me know if there is anything I need to elaborate on.


    Hi all,

    How would I be able to restrict a countif function to data validated cells only?

    Lets say I'm trying to get the count for the word "Yes" in all data validated cells.

    Only thing I've got so far :(



    Re: List Same Cell From Multiple Worksheets

    The first one you posted worked great Skywriter - it even pasted as values only, cleaner than the original code, so thank you very much, I appreciate it. Thanks for your help as well pike!

    Do I have to mark this thread as solved or do I just let it float to the next pages of the forum?

    Re: List Same Cell From Multiple Worksheets

    Hi Skywriter, thanks for responding

    For where it should start - same as the original code: a2, b2 etc. to accommodate for headers

    For part 2: Is there a way to set it to all sheets before the activesheet? Then if I have to add sheets afterward I won't have to change the loop sequence(?)

    Hope this is enough


    Hi all,

    I was wondering if someone would be able to help modify the code that AAE posted for whitegoldf150's problem ( - I'm trying to do the same thing, except I have blank cells that are being skipped when I run the script. I'd like to return even the blank cells so that every column is filled to the same last cell (as many as there are pages). (I've already replicated it to return other values in other columns so the spacing is off). Also is there is a way to fill values for a specific set of sheets (lets say all sheets before the summary sheet) instead of inserting excluded sheet names after the inequality "If ws.Name <> "Sheet1""?