Creating a Line Graph based on total row count from a different tab.

TheyCallMeIke

New Member
Joined
Nov 4, 2010
Messages
34
Hi All,

I have a spreadsheet for yearly claims adjustment count with each month broken into its own tab(worksheet).

I am trying to create a Line graph based on the total row count (variable) for each month, and was wondering what the formula would be to accomplish this?

So I would need the formula to look at janruary tab, february tab, etc. and grab the total row count that has data entered, and put that count into a graph.

so if:

Jan tab has 587 rows of data
Feb tab has 600 rows of data
Mar has 300 rows of data

then the formula will grab those counts and display it into the chart format

What would be the formula I need to put into the graph to do this?

Thanks for anyone that can provide some feedback.:)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Let's assume that your chart is populated based on columns A and B.

Column A (starting in A2), should list the monthly tab names going down the column

column B will hold the associated claims counts. In B2 we write the following formula:

=COUNTA(INDIRECT("'"&A2&"'!"&"A:A"))-1

If you copy this formula down column B you should be sorted.

For clarity:
COUNTA... counts how many cells in column A (A:A) of the target tab contain a value. I've subtracted 1 because I assume that each of your target tabs has a header row.

INDIRECT helps you refer to specific tabs based on the values in col A.

Note:- the first and second set of double quotes contain a single quote also, e.g. " ' " (spaces added here for exaggeration).

Good link on indirect usage can be found here:
http://www.contextures.com/xlFunctions05.html

HTH
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,849
Members
452,948
Latest member
UsmanAli786

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
Back
Top