is there any option for dedcting same like consolidate

[raveepoojari]

in excel wecan sum datas of two sheets in Consolidate. is there any option for deducting the two sheets data?

plz check the attached files sheet (.xls]dif ) diffrence between both sheets. in sheet i want fill color based on - value and + value. if c3 value is equal to and more than 0 fill green, otherwise fill red. plz tell me formula...

[NBVC]

Select C3:C26 and go to Format|Conditional Formatting

Cell Value is >> Greater than or equal to >> =0

Click Format... choose Green... click Ok.

Click Add

Cell Value Is >> Less than >> =0

Click Format..choose Red.. click Ok

Click Ok.

[raveepoojari]

thank u ..

btw can we deduct datas of sheet1 with sheet2? is there any option same like consolidate for sum...

[NBVC]

thank u ..

btw can we deduct datas of sheet1 with sheet2? is there any option same like consolidate for sum...

Do you mean like =SUM(Sheet1:Sheet2!C3) ?

No, there isn't an equivalent for DIFFERENCE() .. you can probably create a UDF though...

[martindwilson]

you could probably multiply the whole sheet you want deducted by -1 then consolidate with sum.

[raveepoojari]

sry...

in this attached excel workbook

in sheet3 C3:c26 i want diffrence between the sheet2 c3:26 and sheet1 c3:c26)


but now i put in sheet3 c3==Sheet2!C3-Sheet1!C3

is there any other way instead putting this formula?

if there is any way..plz let me knw through this attached files...

[martindwilson]

what attached file?

[raveepoojari]

dear sir try to check my message in this thread i attached a file.

if u have other formula this plz let me knw through this file only..it will helps me..alot..

thank u in advance..

[martindwilson]

you know how to consolidate i take it?
first things first
stop merging cells,it makes life awkward!
step 1 select sheet 1
in a spare cell type -1
copy it
select col C paste special multiply all values will be changed to negative
step 2
insert a new sheet
select cell A1
now do consolidation as you normaly would using sum
see attached

[raveepoojari]

Thank u..but as per ur instrucations...it will affect to sheet1 and all columns of Sheet3.

really i want contents in column C of sheet-3 only. i thin there is no options like tat.

i think in sheet3 c3==Sheet2!C3-Sheet1!C3 is good and best..

[martindwilson]

no!. first thing would be to copy sheet 1 then do the multiply /consolidate using that. sheet 3 is only the result of the consolidation and is not affected by anything else.

[raveepoojari]

thank u...really nice idea

but sheet3 c3 value will not automatically updateing when shee1 and sheet 2 value is changeing becoz c3 is value have link with copy of sheet1.

am i right?

is there any other for automatically updateing?

sheet3 c3==Sheet2!C3-Sheet1!C3 is automatically updateing becoz this is linked with origenal data.

[martindwilson]

yep ok. but i assumed sheet 1 was historical and youd' only be updating sheet 2., whatever you feel best with.

[raveepoojari]

thank u for ur support..btw dont think me wrong..