Formula or VBA for typing a number then showing a signature

Olimt

New Member
Joined
Feb 18, 2016
Messages
4
Hi there!

I need help with a formula or something like that in Excel 2010.


What I want to be able to do is:
When I choose a name from a dropdown list in a random cell in one coloumn, I want that name to be coupled up with that name's employee number etc. So when one employee opens this shared document, he/her will be able to put in his/her number in a (random) cell adjacent to his/hers name, and the third cell will then automatically show their signature (their name in a different colour etc)
This code must be able to be used in many cells, in many different sheets. And there has to be one sheet with all the names and numbers in that I can hide.

Here's what I have tried:

Malformed Images Removed

The names in Sheet 1, (Module 01) is names that is taken by using dropdown list from sheet 2 (Instructors).

When I chose "Tom" from dropdown list and enter his code 222 in adjacent cell his signature comes up whit this code. When I enter "Hans" and his code in adjacent cell his signature comes up. But it stops there.. I want to be able to chose whomever from the dropdown list, write that persons number and magically show their signatures. If the wrong number is entered, there will be shown a blank cell.


Hope someone can help me! :)
 
Last edited by a moderator:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
I can't see an image here, just a massive amount of text - not sure if its my machine or your post that did it

From your description, I absolutely would not do this in Excel. You simply can't protect it well enough, no matter what you do to try and safeguard things I guarantee someone with a reasonable amount of Excel knowledge could easily bypass your "PIN code" approach. It would not take long to pull everyone's codes and signatures from this file and use them however they wanted
 

Olimt

New Member
Joined
Feb 18, 2016
Messages
4
Thanks for the quick answer, and sorry for the images that just came out as plain text.

I know I cant protect it very well in Excel, but in the end this is a document that is on a guarded network, and the document "can't" go live, online, outside the network. Therefore I just wanted to see if there is a formula that can do this in excel, and try it out, and in the end it will be up to my boss if we can use this workbook like planned or not.
With signatures i just meant pointing to a name in a cell and the name that's there ends up in the signature cell, but it is in another shape and color.

In my mind it seems very easy to do this, but I cant find the right formula for this.
I am using this formula in one cell ;
=IF(B3=Instructors!B3;(IF(C3=Instructors!C3;B3;""));IF(B3=Instructors!B4;(IF(C3=Instructors!C4;B3;""));""))

But then I am only able to choose from 2 names and use their 2 codes and their signature pops up.
But I cannot choose any other from the list of let say 10 people...
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
Say you have 10 people listed in A1:A10, their corresponding PIN in B and a "signature" in C. The following array formula (entered using Shift & Ctrl & Enter, not just Enter) will return the signature value:
=IFERROR(INDEX(C1:C10,MATCH(A13&B13,A1:A10&B1:B10,0)),"")
It won't return the formatting though, you'd need to use a macro to do that
 

Olimt

New Member
Joined
Feb 18, 2016
Messages
4

ADVERTISEMENT

I am truly grateful for you to try helping me :)

I tried your formula, with some tweaks..
=IFERROR(INDEX(Instructors!D2:D7;MATCH(B5&C5;Instructors!B2:B7&Instructors!C2:C7));" ")
It didn't quite work as expected. Some worked, but not all..

Because I have 2 sheets in my test document.
One sheet where I chose the name of a person, and one open cell adjacent to the name which the person can write his code, and a third cell adjacent to his code which will be the signature:
Ex:
B1 = Instructor C1 = Code and D1 = Signature
B2 = Ole C2 = *** and D2 = Ole (different format)
B3 = Hans C3 = *** and D3 = Hans (different format)
And so on..

The other sheet will be the sheet with ALL the names and the codes for each instructor
Ex
B1 = Name C1 = Code (optional) D1 = signature?
B2 = Jens C2 = ***
B3 = Ole C3 = ***
B4 = Hans C4 = ***
And so on..

This sheet will be hidden and password protected etc, so will all the other sheets, but the cell where the instructors can write their code will be open..

Since each sheet has its own submodule, there can be different instructors for each submodule. Therefore I have the dropdown list with instructor names, and this can be used in every cell.
So when I chose one name from a the dropdown list, this name must be coupled to the code for that instructor, and when he writes his code, the signature pops up..

This is hard for me to explain in plain words, but I hope you can forgive me :)

If none can help, I tried and will go back to finding another solution :)
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
When you experience problems you need to say what those errors are - "It didn't quite work as expected. Some worked, but not all.." - in what way? what was expected? what actually happened? which ones failed?

The formula looks fine and works for me. I've already mentioned you can't pull in a format using this approach

I remain convinced that this is not the way to meet your requirement. Why are you trying to do this? For example, would an email audit trail (to you and the instructor) confirming changes provide a better approach? All transactions are reported to all interested parties and are open to challenge

An example why your approach is bad - all you need is one VLOOKUP formula next to the instructor name, and your PIN is automatically bypassed
 

Olimt

New Member
Joined
Feb 18, 2016
Messages
4
Thanks for helping. I'm way in over my head in this excel world now, so I think I can't make this happen.
I will try to find a different solution. Thanks anyway!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top