Simple programming in excel?

[programmerz]

Hi all,

I have a few question about excel. Please refer to the table below.

<table x:str="" style="border-collapse: collapse; width: 144pt;" border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td class="xl66" style="border-left: medium none; width: 48pt;" width="64">Debit</td> <td class="xl67" style="border-left: medium none; width: 48pt;" width="64">Credit</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; border-top: medium none;" height="17">Item A</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="">23</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" x:num="">15</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; border-top: medium none;" height="17">Item B</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" x:num="">43</td> <td class="xl70" style="border-top: medium none; border-left: medium none;" x:num="">42</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl71" style="height: 13.5pt; border-top: medium none;" height="18">Item C</td> <td class="xl72" style="border-top: medium none; border-left: medium none;" x:num="">21</td> <td class="xl73" style="border-top: medium none; border-left: medium none;" x:num="">51</td> </tr> </tbody></table>
1. Is it possible to create simple rule like this?
If Column A contains Item C, then color that row (A3-AC) with red color.

2. If Column A contains Item C, then copy the Credit value of Item C in F4

Please let me know if you need more information.
Thanks in advance.

 

[xld]

Take a look at http://www.contextures.com/xlCondFormat01.html

 

[Sandeep Warrier]

Hi

Do you need code to do this?

This can also be done using Conditional Formatting and a formula

Select the 1st row of data. Go to Format | Conditional Formatting. Choose FormulaIs from the drop-down and type in =$A2="Item C" and choose any format by clicking on the Format button. Click on OK. Now copy the entire row. Select all subsequent rows and go to Edit | Paste Special and select Formats and click on OK. You will see that the row containing Item C is highlighted with the format you have selected.

For the value in F4... taking your sample data... try

Excel Workbook
	A	B	C	D	E	F
1		Debit	Credit
2	Item A	23	15
3	Item B	43	42
4	Item C	21	51			51
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F4	=IF(ISNUMBER(MATCH("Item C",$A$2:$A$4,0)),INDEX($C$2:$C$4,MATCH("Item C",$A$2:$A$4,0)),"")

 

[programmerz]

Hi

Do you need code to do this?

This can also be done using Conditional Formatting and a formula

Select the 1st row of data. Go to Format | Conditional Formatting. Choose FormulaIs from the drop-down and type in =$A2="Item C" and choose any format by clicking on the Format button. Click on OK. Now copy the entire row. Select all subsequent rows and go to Edit | Paste Special and select Formats and click on OK. You will see that the row containing Item C is highlighted with the format you have selected.

For the value in F4... taking your sample data... try

Excel Workbook
	A	B	C	D	E	F
1		Debit	Credit
2	Item A	23	15
3	Item B	43	42
4	Item C	21	51			51
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F4	=IF(ISNUMBER(MATCH("Item C",$A$2:$A$4,0)),INDEX($C$2:$C$4,MATCH("Item C",$A$2:$A$4,0)),"")

Thanks xld,

Thanks sandeep.warrier for your help, it works!
However, when I copy your formula for 2nd question and put it F2 & F3, I still get the same result. It will copy the result from "Item C" as well. Let say I have a lot of Item C & Item B and I want to calculate the total of them. Is there any other way to do it?

Thanks again

p/s - May I know how to you copy your excel sheet to this forum?

 

[Sandeep Warrier]

.....
However, when I copy your formula for 2nd question and put it F2 & F3, I still get the same result. It will copy the result from "Item C" as well. Let say I have a lot of Item C & Item B and I want to calculate the total of them. Is there any other way to do it?

When you say "put the formula in F2 & F3" what is the result you expect? Do you want to sum all credits for "Item C"?

If yes then use SUMIF

Excel Workbook
	A	B	C	D	E	F
1		Debit	Credit
2	Item A	23	15
3	Item B	43	42
4	Item C	21	51			204
5	Item A	23	15
6	Item B	43	42
7	Item C	21	51
8	Item A	23	15
9	Item B	43	42
10	Item C	21	51
11	Item A	23	15
12	Item B	43	42
13	Item C	21	51
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F4	=SUMIF($A$2:$A$13,"Item C",$C$2:$C$13)

p/s - May I know how to you copy your excel sheet to this forum?

You can use

1. Excel Jeanie
2. Richard Scholar's HTML Maker
3. If you use Internet Explorer then you can put borders around a small sample of your data, copy it and paste it here directly.