Formula for Vacancies

Question

Hello,&nbsp;I am trying to create a formula that will link a total number of "vacancies" per position onto a separate excel sheet.&nbsp;&nbsp;Sheet 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sheet 2secretary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; filled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;secretary&nbsp; &nbsp; &nbsp; 2secretary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vacantsecretary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; filledsecretary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vacant&nbsp;laborer&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; filled&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;laborer&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1laborer&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; filledlaborer&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vacant&nbsp;&nbsp;&nbsp;Can I have a formula that will add the vacancies for an updating total on a different sheet like my example for sheet 2?&nbsp;

smitty smith · Answer

Have you tried a PivotTable? Put Position &amp; Status in the Row field, then drag Status from the Field List into the Values field to get your summary.
&nbsp;
As your status changes, just refresh the PT to update.

null null · Answer

Thank you for the thought.&nbsp; I have been working with it this morning and it will not separate the information correctly.&nbsp; There are spaces throughout the document to separate departments and positions and it wants to create a sum and I want the positions listed separately to see how many vacancies per position.&nbsp;Thanks

smitty smith · Answer

If your data is set up with spaces in between departments, then there's not much you'll be able to do other than manually.&nbsp;
&nbsp;
You'd be better off by removing the gaps, then Excel will see it as one, continuous data set. Much like a database.

Forum Discussion

Formula for Vacancies

3 Replies

Resources