Help ! Newbie Comparing Tables

  • Thread starter Thread starter danman
  • Start date Start date
D

danman

i HAVE 3 INDENTICALLY FORMATTED TABLES OF DATA,( ROWS X COLUMNS)

ONE TABLE FOR EACH "PARTS SUPPLIER"

THE TABLES ARE FILLED WITH NUMERICAL DATA (E.G. 1.009)

THE TABLES ARE EACH A DIFFERENT FONT COLOR, RED, GREEN, BLUE.

I WISH TO:

1.COMPARE THE NUMERICAL VALUE IN CELL C5 OF EACH TABLE
2.CHOOSE THE SMALLEST OF THE THREE VALUES
3.COPY THE VALUE (E.G 1.009) TO A BRAND NEW TABLE, CELL C5
4. KEEP THE SAME FONT COLOR IN THE NEW TABLE CELL C5 OF THE SMALLES
OF THE COMPARED THREE VALUES
5. IN CASE OF "TIES", MAKE THE NEW CELL C5 FONT COLOR PURPLE

ANY HELPS? CAN IT BE DONE
 
Hi

(firstly, please don't use ALL CAPS - it tends to mean that you're
shouting!)

i'm assuming by "tables" you mean worksheets.

Not sure how to do 4 & 5 yet ... still thinking about it - but 1 - 3 can be
achieved by

in cell C5 of the worksheet where you want the lowest value copied
type the following
=MIN(Sheet1:Sheet3!C5)
where sheet1 is the name of your first worksheet
and sheet3 is the name of the last worksheet if they are all next to each
other down the bottom of the screen
(if your worksheet names have spaces in them you need to put ' around them
e.g. 'Bills Parts' )

let us know how you go
Cheers
JulieD
 
thanks..i got the MIN statement to work

actually all" tables "were on the same worksheet
first table rows 1-100
second table rows 101-200
third table rows 201-300

new table rows 301-400

now i need to figger how move the "font color" along with the MIN value
selected

the font color will indicate if it is the red, green or blue supplier

or i will have to find another way to show supplier "code" in the final
table along with the MIn value
 
OK, you have me confused - If all 3 tables are in the same sheet, how do you
'compare the value in C5 of each table', and then take the smallest of those
values and put it in cell C5 of a new table?
 
Hi

okay, well if they're all on the same worksheet (not sure how you have 4 x
cell reference C5 - but i'll leave that for now) you can use conditional
formatting to give you your colours.

click where your Min statement is and choose
Format / Conditional formatting from the menu
choose
cell value is equal to =$C$5 and click on the format button and set the
colour - click OK
then click on the ADD button to get the next criteria up
cell value is equal to =$C$106 (or what ever cell it is) and click on the
format button and set the colour - click OK
then click on the ADD button to get the next criteria up
cell value is equal to =$C$206 (or what ever cell it is) and click on the
format button and set the colour - click OK
then click on Ok

this should give you the colour that matches who has the min amount.

However, conditional formatting only works for 3 conditions and will take
the "first true" statement it comes across and applies that so if two of the
suppliers are equal you will get the one that is at the top of the sheet
showing. There maybe a way to edit the conditional formatting statement ...
but i can't figure it at the moment.

Cheers
JulieD
 
Back
Top