Good Afternoon

I have an excel spreadsheet which shows all the active vacancies for each
department.

December January
Detail (inc date) Forecast Detail (inc date) Forecast
Engineering Portfolio Lead 1 Mechanical Engineer (FRS) 1
Mechanical Engineer (G&A) 0
C&I Engineer 1


Total 1 2


Those that are active have a number in the column next to them. Those that
have been filled have a zero in the column next to them



Please can you tell me if there is any way to create a list of all those
vacancies that are active in another cell? (bearing in mind that we could
have as many as 15/20 vacancies in one department at any one time)

Many thanks

Concatenating all those values into a single cell will require you to install
some sort of additional functionality since string concatenation (concat
multiple cells based on matching criteria in other cells) is not a native
Excel function...yet.

But if you're OK creating a "list" of active jobs, then you can accomplish
this with a common (though hard to read) array formula.

As an example...let's say your setup is as follows:

Column A - list of job names
Column B - status (1 or 0 values)

D1 = the word "Active Jobs"
E1 =SUM(B:B)

In D2 we put the following Array-entered formula:

=IF(ROWS($1:1)$E$1, "", INDEX($A$1:$A$30, SMALL(IF($B$1:$B$30=1,
ROW($B$1:$B$30), ""), ROWS($1:1))))

You press CTRL-SHIFT-ENTER to confirm that formula, not just ENTER. This
will activate the array and you will see curly braces { } appear around your
formula in the formula bar. Also, the first Job name from column A should
appear.

Now copy D2 down about 20 rows to insure you have the array active in enough
cells.

Does this get you in the right direction?

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Hannah" wrote:

Good Afternoon

I have an excel spreadsheet which shows all the active vacancies for each
department.

December January
Detail (inc date) Forecast Detail (inc date) Forecast
Engineering Portfolio Lead 1 Mechanical Engineer (FRS) 1
Mechanical Engineer (G&A) 0
C&I Engineer 1


Total 1 2


Those that are active have a number in the column next to them. Those that
have been filled have a zero in the column next to them



Please can you tell me if there is any way to create a list of all those
vacancies that are active in another cell? (bearing in mind that we could
have as many as 15/20 vacancies in one department at any one time)

Many thanks

Good Morning

Many thanks for your assistance.

The list formula is great, but i'm not sure how to adapt it so that it works
in my spreadsheet as i have two columns for each month (and i'm looking at a
years worth of vacancies) as opposed to the vacancies being listed under one
another.

Any further suggestions gratefully received.

Thank you

Hannah

"JBeaucaire" wrote:

Concatenating all those values into a single cell will require you to install
some sort of additional functionality since string concatenation (concat
multiple cells based on matching criteria in other cells) is not a native
Excel function...yet.

But if you're OK creating a "list" of active jobs, then you can accomplish
this with a common (though hard to read) array formula.

As an example...let's say your setup is as follows:

Column A - list of job names
Column B - status (1 or 0 values)

D1 = the word "Active Jobs"
E1 =SUM(B:B)

In D2 we put the following Array-entered formula:

=IF(ROWS($1:1)$E$1, "", INDEX($A$1:$A$30, SMALL(IF($B$1:$B$30=1,
ROW($B$1:$B$30), ""), ROWS($1:1))))

You press CTRL-SHIFT-ENTER to confirm that formula, not just ENTER. This
will activate the array and you will see curly braces { } appear around your
formula in the formula bar. Also, the first Job name from column A should
appear.

Now copy D2 down about 20 rows to insure you have the array active in enough
cells.

Does this get you in the right direction?

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Hannah" wrote:

Good Afternoon

I have an excel spreadsheet which shows all the active vacancies for each
department.

December January
Detail (inc date) Forecast Detail (inc date) Forecast
Engineering Portfolio Lead 1 Mechanical Engineer (FRS) 1
Mechanical Engineer (G&A) 0
C&I Engineer 1


Total 1 2


Those that are active have a number in the column next to them. Those that
have been filled have a zero in the column next to them



Please can you tell me if there is any way to create a list of all those
vacancies that are active in another cell? (bearing in mind that we could
have as many as 15/20 vacancies in one department at any one time)

Many thanks