GTO
07-22-2012, 03:22 PM
Greetings All,
For those familiar with me, you already know that I may just well have titled the thread, "Oh dear! Mark's trying formulas!"
Now I personally detest extra 'paperwork' and especially forms that have the same data that exists in other forms, but there are occasions when it seems to make some sense. Let us say that while I find org charts to be generally "pretty", but not particularly useful - if we are adapting to a new payroll system, showing who reports to whom, may be useful.
Anyways, here are the basics and an attached small wb. Two sheets (thus far at least).
The first sheet has very basic employees' info; name, employee ID, days off and who they report to (or conversely, how many
subordinates one is responsible for).
The only formulas are some basic COUNTIF's and a search for the supervisor's name via:
=IF(ISERROR(LEFT(INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),SEARCH(",",INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2)
,1)-1 )),"",LEFT(INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),SEARCH(",",INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),1)-1
))
Now, other than (as I have done) running the entire formula and seeing if anything blows up (the ISERROR()), is there a more
efficient way?
Now, on the second sheet, named 'Formulated Chart', I am extracting/coercing the data so there are more formulas.
Again, I simply try and blow stuff up, and upon failing any KABOOM! happening, use the formula to return the supervisor's
info:
=IF(ISERROR(LEFT(INDEX(Personnel!$A$13:$B$20,MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),SEARCH(",",INDEX(Personnel!$A$13:$B$20,
MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),1)-1)),"",LEFT(INDEX(Personnel!$A$13:$B
$20,MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),SEARCH(",",INDEX(Personnel!$A$13:$B$20,
MATCH(MATCH('Formulated Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),1)-1))
In this particular formula, I have already realized that I probably should have just hard-coded in a number for the 'look
for', rather than nesting the MATCH.
Still on the second sheet, I think my biggest interest is in where I want to continue searching for matches. Taking a look
at the 'table' at AD14:AK33, and concentrating on the first column (AD), you will see that I use two formulas:
=IF(ISERROR(MATCH(Personnel!$A$13,Personnel!$G$23:$G$142,0)),"",MATCH(Personnel!$A$13,Personnel!$G$23:$G$142,0))
...in the first row, and thereafter:
=IF(ISERROR(MATCH(Personnel!$A$13,INDIRECT("Personnel!$G$"&23+SUM($AD$14:$AD14)&":$G$142"),0)),"",MATCH(Personnel!$A$13
,INDIRECT("Personnel!$G$"&23+SUM($AD$14:$AD14)&":$G$142"),0))
...which is then dragged down. You will see that the return values are then used is the various supervisors' "branches"
if-you-will. For example, in B14:D14, we enter:
=IF(AD14<>"",INDEX(Personnel!$B$23:$D$142,SUM('Formulated Chart'!$AD$14:$AD14),0),"")
...confirming via CSE.
So... back to our 'table' of helper formulas, is there a better way than INDIRECT() to keep searching? If not, that is fine
of course, I was actually quite happy to figure out how to get all the returns without example. I just wanted to ask, as if
the answer is, "Nice try Mark, but SOMEBETTERFUNCTION() would be wayyyy more efficient in continuing search.", then it is a
chance to learn :-)
Thank you so very much,
Mark
For those familiar with me, you already know that I may just well have titled the thread, "Oh dear! Mark's trying formulas!"
Now I personally detest extra 'paperwork' and especially forms that have the same data that exists in other forms, but there are occasions when it seems to make some sense. Let us say that while I find org charts to be generally "pretty", but not particularly useful - if we are adapting to a new payroll system, showing who reports to whom, may be useful.
Anyways, here are the basics and an attached small wb. Two sheets (thus far at least).
The first sheet has very basic employees' info; name, employee ID, days off and who they report to (or conversely, how many
subordinates one is responsible for).
The only formulas are some basic COUNTIF's and a search for the supervisor's name via:
=IF(ISERROR(LEFT(INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),SEARCH(",",INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2)
,1)-1 )),"",LEFT(INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),SEARCH(",",INDEX($A$13:$B$20,MATCH(G24,$A$13:$A$20,0),2),1)-1
))
Now, other than (as I have done) running the entire formula and seeing if anything blows up (the ISERROR()), is there a more
efficient way?
Now, on the second sheet, named 'Formulated Chart', I am extracting/coercing the data so there are more formulas.
Again, I simply try and blow stuff up, and upon failing any KABOOM! happening, use the formula to return the supervisor's
info:
=IF(ISERROR(LEFT(INDEX(Personnel!$A$13:$B$20,MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),SEARCH(",",INDEX(Personnel!$A$13:$B$20,
MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),1)-1)),"",LEFT(INDEX(Personnel!$A$13:$B
$20,MATCH(MATCH('Formulated
Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),SEARCH(",",INDEX(Personnel!$A$13:$B$20,
MATCH(MATCH('Formulated Chart'!C12,{"I","II","III","IV","V","VI","VII","VIII"},0),Personnel!$A$13:$A$20,0),2),1)-1))
In this particular formula, I have already realized that I probably should have just hard-coded in a number for the 'look
for', rather than nesting the MATCH.
Still on the second sheet, I think my biggest interest is in where I want to continue searching for matches. Taking a look
at the 'table' at AD14:AK33, and concentrating on the first column (AD), you will see that I use two formulas:
=IF(ISERROR(MATCH(Personnel!$A$13,Personnel!$G$23:$G$142,0)),"",MATCH(Personnel!$A$13,Personnel!$G$23:$G$142,0))
...in the first row, and thereafter:
=IF(ISERROR(MATCH(Personnel!$A$13,INDIRECT("Personnel!$G$"&23+SUM($AD$14:$AD14)&":$G$142"),0)),"",MATCH(Personnel!$A$13
,INDIRECT("Personnel!$G$"&23+SUM($AD$14:$AD14)&":$G$142"),0))
...which is then dragged down. You will see that the return values are then used is the various supervisors' "branches"
if-you-will. For example, in B14:D14, we enter:
=IF(AD14<>"",INDEX(Personnel!$B$23:$D$142,SUM('Formulated Chart'!$AD$14:$AD14),0),"")
...confirming via CSE.
So... back to our 'table' of helper formulas, is there a better way than INDIRECT() to keep searching? If not, that is fine
of course, I was actually quite happy to figure out how to get all the returns without example. I just wanted to ask, as if
the answer is, "Nice try Mark, but SOMEBETTERFUNCTION() would be wayyyy more efficient in continuing search.", then it is a
chance to learn :-)
Thank you so very much,
Mark