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