Prev: How do I change date that is in All Caps to proper format?
Next: a cell contain formula, how to extract value of that cell
From: wissam on 26 Jan 2010 19:24
I am very sorry if I may look very naive when it comes to cloudfiles (this is
very new to me). To get the new file with the code you mentioned, am I
supposed to do anything other than clicking the link in the post above and
save the file ? The only file version I get from clicking all the cloudfile
links in the above posts is one file (same file; it that has only one summary
sheet that shows Hemoglobin [H_Value] data in pivottable, in addition to 3
sheets [GroupA, GroupB, GroupC], and I cannot seem to find the CaxP formula
demo mentioned)
Do I have the correct file link in the posts?
Again, I am sorry if I sound naive about this and THANK YOU very much for
your patience.

"Herbert Seidenberg" wrote:

> The code snippet you appropriately quoted has been superseded
> by new code in the CloudFile link.
> The CaxP formula has been added as a demo.
> The code in the MediaFire link has not been edited.
> It still copies formulas.
>
> .
>
From: Herbert Seidenberg on 27 Jan 2010 11:59
I, and dozens of other people, downloaded the files from the above
links
and found the correct versions.
The version you mentioned (with GroupA)
has been deleted from the links days ago.
You probably have all 3 versions on your PC, but at different
directory locations.
Have some new blood at your facility try downloading.
From: wissam on 28 Jan 2010 12:02
I believe that the following snippet fixes my problem so that it would
pastspecial value:

..ListObjects(1).ListColumns(j).DataBodyRange.Copy

Sheets(ss).ListObjects(1).ListColumns(i).DataBodyRange.Cells(u).PasteSpecial
(xlPasteValuesAndNumberFormats)
Application.CutCopyMode = False

The current function to get the percentage of table10[hemoglobin] with
values between Table11[lo] and Table11[high]is as follows:

=COUNTIFS(Table10[Type],[Type],INDIRECT("Table10["&Table12[#Headers]&"]"),">="&INDEX(Table11[Lo],rown),INDIRECT("Table10["&Table12[#Headers]&"]"),"<="&INDEX(Table11[Hi],rown))/COUNTIFS(Table10[Type],[Type])

The denominator in the above function currently counts all cells in the
column (even empty ones). For instance, in table10 shown below, for month of
Jan, % of hemoglobin values between 10 and 12 is 50%. How do I change the
denominator in the above function so that it count cells that only have
numbers (i.e do not count cell into the denominator for that month if cell is
empty; as such, the percentage would become 100%)?

Table10
Type Jan Feb Mar Apr May
albumin 3.5 3.5 3.3 3.5 3.1
aluminum 3.6 3.5 3.3 3.3 3.5
Aluminum 5
hemoglobin 10.7 10.8 11.3
hemoglobin 10.5 9.8 11.0 11.2

Thank you very much.



"Herbert Seidenberg" wrote:

> The code snippet you appropriately quoted has been superseded
> by new code in the CloudFile link.
> The CaxP formula has been added as a demo.
> The code in the MediaFire link has not been edited.
> It still copies formulas.
>
> .
>
From: wissam on 28 Jan 2010 20:15
I just saw your post about trying another computrer and I opened the link
from another computer and got the correct file. Thank you.
Any help with how to change the percentage function so that not count empty
cells in denominator is very much appreciated.
Thanks a lot again.

"Herbert Seidenberg" wrote:

> I, and dozens of other people, downloaded the files from the above
> links
> and found the correct versions.
> The version you mentioned (with GroupA)
> has been deleted from the links days ago.
> You probably have all 3 versions on your PC, but at different
> directory locations.
> Have some new blood at your facility try downloading.
> .
>
From: Herbert Seidenberg on 30 Jan 2010 22:51
Excel 2007 Tables
Consolidate.
Revised % formula to accommodate unavailable data.
http://www.mediafire.com/file/qedjmjxzyz2/01_20_10b.xlsm
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: How do I change date that is in All Caps to proper format?
Next: a cell contain formula, how to extract value of that cell