Signature Lines Automatcally Show

[south0085]

It was hard to title this one. Thanks in advance for your help.

In cells A30 through A34, I have the following list:

Assistant Manager
Manager
Coordinator or Treasurer
Vice President
President

We have an "approval level" control that determines who needs to sign the requisition depending on the amount. Here is the approval key:

Up to $100 Assistant Manager
$101-$500 Manager
$501-$2000 Coordinator or Treasurer
$501-$2000 Vice President
Over $2000 President

Depending on the amount in cell J27, I would like the appropriate signature people to automatically pop up.

Is this possible?

 

[jtakw]

Hi,

Your approval key, $501-$2000 is duplicated for Coordinator or Treasurer, and Vice President, is that correct? So any of the three can sign?

 

[south0085]

Hi,

Your approval key, $505-$2000 is duplicated for Coordinator or Treasurer, and Vice President, is that correct? So any of the three can sign?

It means, if it's $501-$2000, then the (Coordinator or Treasure....whichever one is avaialbe) AND the (Vice President) must sign.

 

[jtakw]

Would something like this help?
I changed the Title list.

Book1
	A	B	J	K	L
27			99		Assistant Manager
28			100		Assistant Manager
29
30	Assistant Manager		0
31	Manager		101		Manager
32	Coordinator or Treasurer and Vice President		500		Manager
33	President		510		Coordinator or Treasurer and Vice President
34			2000		Coordinator or Treasurer and Vice President
35			5000		President
Sheet17

Cell Formulas
Range	Formula
L27	=IF(OR(J27={"",0}),"",LOOKUP(J27,{0,101,501,2001,9.9E+307},A$30:A$33))

 

[south0085]

Would something like this help?
I changed the Title list.

	A	B	J	K	L
Assistant Manager
Assistant Manager
Assistant Manager
Manager	Manager
Coordinator or Treasurer and Vice President	Manager
President	Coordinator or Treasurer and Vice President
Coordinator or Treasurer and Vice President
President

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]99[/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"][/TD]
[TD="align: right"]510[/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L27[/TH]
[TD="align: left"]=IF(OR(J27={"",0}),"",LOOKUP(J27,{0,101,501,2001,9.9E+307},A$30:A$33))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I think that's great. But the problem is "Coordinator or Treasure" is one person. And the "Vice President" is a separate person. I need there to be two signature lines. Like this:

Coordinator or Treasurer ________________________

Vice President_________________________________

 

[south0085]

Oh wait. I think I've made this confusing. My apologies. I want cells A30 through A34 to populate on top of each other. So let's say the requisition is $503, I want these to pop up:

Assistant Manager____________
Manager______________
Coordinator or Treasurer______________
Vice PResident___________

Let's assume it's $4, then it would pop up with this:

Assistant Manager___________

Let's assume it's $64,000

Assistant Manager____________
Manager______________
Coordinator or Treasurer______________
Vice PResident___________
President____________

 

[jtakw]

That would require a separate formula for each line, see if this works for you:

Book1
	A	B	J
27			2200
28
29
30	Assistant Manager______________________________
31	Manager______________________________
32	Coordinator or Treasurer______________________________
33	Vice President______________________________
34	President______________________________
Sheet17

Cell Formulas
Range	Formula
A30	=IF(J$27>0,"Assistant Manager"&REPT("_",30),"")
A31	=IF(J$27>100,"Manager"&REPT("_",30),"")
A32	=IF(J$27>500,"Coordinator or Treasurer"&REPT("_",30),"")
A33	=IF(J$27>500,"Vice President"&REPT("_",30),"")
A34	=IF(J$27>2000,"President"&REPT("_",30),"")

 

[south0085]

That would require a separate formula for each line, see if this works for you:

	A	B	J
Assistant Manager______________________________
Manager______________________________
Coordinator or Treasurer______________________________
Vice President______________________________
President______________________________

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2200[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A30[/TH]
[TD="align: left"]=IF(J$27>0,"Assistant Manager"&REPT("_",30),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A31[/TH]
[TD="align: left"]=IF(J$27>100,"Manager"&REPT("_",30),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A32[/TH]
[TD="align: left"]=IF(J$27>500,"Coordinator or Treasurer"&REPT("_",30),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A33[/TH]
[TD="align: left"]=IF(J$27>500,"Vice President"&REPT("_",30),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A34[/TH]
[TD="align: left"]=IF(J$27>2000,"President"&REPT("_",30),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Perfect! Thank you!

 

[jtakw]

You're welcome.

Play with the number 30 (increase or decrease) in each of my formulas to line up the signature line if you like.