Simple programming in excel?

programmerz

New Member
Joined
Apr 27, 2010
Messages
6
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
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
ABCDEF
1DebitCredit
2Item A2315
3Item B4342
4Item C215151
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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

New Member
Joined
Apr 27, 2010
Messages
6
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
ABCDEF
1DebitCredit
2Item A2315
3Item B4342
4Item C215151
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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

Well-known Member
Joined
Oct 31, 2008
Messages
2,672
.....
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
ABCDEF
1DebitCredit
2Item A2315
3Item B4342
4Item C2151204
5Item A2315
6Item B4342
7Item C2151
8Item A2315
9Item B4342
10Item C2151
11Item A2315
12Item B4342
13Item C2151
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,986
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top