How to Format Excel Tables with Varying number of ROWS

LoraHut

New Member
Joined
Aug 3, 2010
Messages
1
Hello!
So I have to take data from multiple tables that all have a varying number of rows, but I want the copied and re-formatted table to all maintain the same layout.
For example, here is an example of one of the raw data tables:


Name of Refinery
Title of Report
Crude Average 08Q3 08Q2
Type A x x
Type B x x
Type C x x
Type D x x x
TOTAL CRUDE x x x

Other Feeds
IP 1 x x
IP 2 x x
TOTAL OTHER x x x

TOTAL INPUTS x x x

And I copy and paste the data into a separate worksheet and arrange it so it is easy to read (like I format using BOLDS for the TOTALs, and highlight each separate section, like CRUDE, with a different background color).
The problem is that I want to format many of these tables in the exact same manner (so that the CRUDES section of the table always has the same color background and border) but from one raw data table to the next, the number of crudes and/or feeds change (so the number of rows that need to be highlighted and colored accordingly change as well)
What code can I use to ensure that if I were to format ANY table (regardless of varying crude and/or feed row number) that they would all have the same general format?
So Row starting with Crude down to Row contained TOTAL CRUDE has a border around it and is a certain bacgrkound color (and totals are bold)
Row starting with OTHER FEEDS down to Row containing TOTAL FEEDS has a border around it and is another different bg color (And totals are bold)
Row containing TOTAL INPUTS has yet another border and another different bg color
(but the number of crude in the list and the number of feeds in the list always vary)


Thank you!
 
Last edited:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

hsk

Well-known Member
Joined
Oct 19, 2006
Messages
567
If i understand your data correctly ....
Ur 3rd row is a title row for a say Detail section which ends with "TOTAL CRUDE". Every row within these two, you want to format it with diff color / border etc ...... right ?

One way is conditional formatting .....
(I am assuming ur data starts from column A)
Select Cell A1, select the entilre sheet - Go to Format - Conditional Formatting - Select Formula from the dropdown ....
Paste this formula -
Code:
=AND(ROW(A1)>MATCH("Crude",$A:$A,0),ROW(A1) < MATCH("TOTAL CRUDE",$A:$A,0))

Select ur format , like say background color = yellow

This will paint all the rows below ur Title row (cell with value = Crude) and above the summary row (cell with value = TOTAL CRUDE) with background color = yellow

U can add similar conditions for other sctions aswel,

Hope this helps
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,718
Messages
5,833,289
Members
430,202
Latest member
Faizal5zl

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