Bench,
I think the following is a basis for a solution for you. I cannot test it 100% because I run 2007 which does not have the negative data bars.
The bars in each column are relative to the values in that column. I will describe re the positive column L and you should be able to apply similar logic to the negative column. So, data values in K...........
Sheet3
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 56px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD style="TEXT-ALIGN: right">55555</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">27345</TD><TD style="TEXT-ALIGN: right">27345</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">25</TD><TD style="TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">-55555</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>
Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>L1</TD><TD>{=MAX(ABS
(K2:K5))}</TD></TR><TR><TD>L2</TD><TD>=IF(K2>=0,K2,"")</TD></TR></TBODY></TABLE></TD></TR><TR><TD>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
You will need to insert an extra row at the head of your data. The extra cells in the bar columns must be included in your data bar formatting range. Just apply the normal data bar settings and show data bars only.
Enter the
ARRAY formula in the new top cell, L1 with
Ctrl Shift Enter.
NB you will need to edit the upper bound of the K range to suit your data.
Enter the existing formula in the rest of the data bar column.
The new cell will always equate to the largest absolute value in the data range and will show a full data bar.
The other data bars then size relative to the new cell!!!!
YOU CAN HIDE THE NEW ROW!!!!
In the part example above the positive 27345 is half the length of the reference in L1 that is the absolute of the -55555.
Let me know if that helps.
