Link to home
Start Free TrialLog in
Avatar of Mani Pazhana
Mani PazhanaFlag for United States of America

asked on

Create Excel formula on dynamic data

Hello Experts,
I have a excel spreadsheet attached.


1. Sheet: Line call Log --> will have the data and  it gets added on frequent basis. (Data grows)

2. Sheet: Definitions-color Code - will have the Count column (highlighted in yellow) that will show the count of issues from Line call Log sheet by Department Responsible (column D).

How to create excel formula that will get the count? (it as to be dynamic to accommodate growing data...)

Thanks
LINECALLTRACKING.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mani Pazhana

ASKER

Thanks
Shaun's formula based approach will certainly work. You may also want to consider using a PivotChart for this, as PivotTables and PivotCharts are very powerful tools for aggregating and analyzing data.  The easiest way to do this here would be:

1) Convert your raw data to a table.  Select A1 on 'Line Call Log', and select Insert / Tables / Table from the Ribbon.  Include your header row
2) Select Insert / Charts / PivotChart from the Ribbon.  Put Department Responsible into the Rows/Axis area, and (count of) Department Responsible into the data area

The table will automatically expand as you add new rows to your data set.  To refresh the PivotTable and/or PivotChart, simply right-click on either and select Refresh Data.  To force automatic updates to your PivotTable and/or PivotChart, please see my article here.
Thanks