# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

How do I create a formula for one spreadsheet to look at multiple others to look at cell A2 and if empty value enter A! in field of the spreadsheet. This is to track a sign off form that would show who still needs to sign.
Solved by G. B. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 16/05/2018 - 07:29
other
Excelchat Expert 16/05/2018 - 07:29
Hi welcome!
User 16/05/2018 - 07:29
my company would be 51=
User 16/05/2018 - 07:29
my team is me
User 16/05/2018 - 07:31
I am a medical Laboratory Technologist, and we are trying to create a document to track all our SOP change notifications and the sign off on those
Excelchat Expert 16/05/2018 - 07:31
You have a question on finding Blank cell entries in other sheets?
Excelchat Expert 16/05/2018 - 07:31
Can you upload an example of the sheets?
User 16/05/2018 - 07:31
thats correct I want to be able to create a list on the basis of not signed
User 16/05/2018 - 07:32
I cannot, privacy
User 16/05/2018 - 07:32
A1 field would have the name and A2 would have the signature
Excelchat Expert 16/05/2018 - 07:33
Ok, I can try to respond with a Q &A session here and then suggest the appropriate function to try on your sheet.
User 16/05/2018 - 07:33
the target file is a simple spread sheet with a hyperlinc to the SOP
Excelchat Expert 16/05/2018 - 07:34
So a single file with links to different SopS?
User 16/05/2018 - 07:34
yes
Excelchat Expert 16/05/2018 - 07:35
And the sign off cell is in a single sheet?
Excelchat Expert 16/05/2018 - 07:35
Or is each sign off cell in a different tab?
User 16/05/2018 - 07:36
no there would be a list of the names of all staff A1:A50
User 16/05/2018 - 07:37
A2:A50 would be simply a date field that would indicate date signed
User 16/05/2018 - 07:37
sorry the date would be B1:B50 in that example...
Excelchat Expert 16/05/2018 - 07:38
And you need to known which of the B2-b50 cells are blank?
User 16/05/2018 - 07:38
yes
User 16/05/2018 - 07:38
to which will be tracked on a seperate spreadsheet.
Excelchat Expert 16/05/2018 - 07:39
I think all you need is a simple function -ISBLANK
Excelchat Expert 16/05/2018 - 07:39
I would suggest a conditional format set with ISBLANK function
User 16/05/2018 - 07:40
I do believe this will be a very simple function I am taking over from a colleague that was very skilled with Excel and I am not
Excelchat Expert 16/05/2018 - 07:40
That will Highlight all the cells in Yellow or RED, pick a color that are blank.
Excelchat Expert 16/05/2018 - 07:41
What version of EXCEL do youu have?
User 16/05/2018 - 07:41
however I do want the name to go into a new document ie
User 16/05/2018 - 07:41
if A1 ISBLANK put B2 in new document
User 16/05/2018 - 07:42
2013
Excelchat Expert 16/05/2018 - 07:42
2013 should be fine.
Excelchat Expert 16/05/2018 - 07:42
Can you explain new document?
Excelchat Expert 16/05/2018 - 07:43
You mean open another EXCEL sheet or just a different row or sheet in the current file?
User 16/05/2018 - 07:44
we have 50 procedure change document for SOPs, each one has to be signed by all staff, new document would track which staff have not signed what procedure changes
User 16/05/2018 - 07:44
new file altogether
Excelchat Expert 16/05/2018 - 07:47
That would not be possible with basic EXCEL, would require MACRO or VBA, some form of programming to loop thru files and enter a value in another.
User 16/05/2018 - 07:48
excel does not have Macro
Excelchat Expert 16/05/2018 - 07:49
It does , but scope here does not cover, but let me clarify a few things, I may be over complicating this.
Excelchat Expert 16/05/2018 - 07:50
So there are 50 sheets on a shared drive somewhere or all on your desktop.
User 16/05/2018 - 07:50
shared drive
Excelchat Expert 16/05/2018 - 07:52
As long as you have access to those files, you should be able to create a separate control sheet to access a range in those sheets.
Excelchat Expert 16/05/2018 - 07:52
I think I get the scenario,
Excelchat Expert 16/05/2018 - 07:53
So that is the CONTROL sheet in tthe viewer that should access the 50 separate files
Excelchat Expert 16/05/2018 - 07:53
Or is that the individual file?
User 16/05/2018 - 07:54
that would be the individual file, the control would simply show where the file was and the name of the person who has the blank value on the SOP, once they sign (date) it would then go away
Excelchat Expert 16/05/2018 - 07:56
OK.
Excelchat Expert 16/05/2018 - 07:57
We should go thru this in steps, since dont have the actual file and you are new to EXCEL.
Excelchat Expert 16/05/2018 - 07:58
This is how you reference a cell in the sheet on the shared drive
Excelchat Expert 16/05/2018 - 07:58
This may be impacted by any access controls on the drive , but works on my desktop
Excelchat Expert 16/05/2018 - 07:58
='C:\Users\John\Documents\[test.xlsx]Sheet1'!F4
Excelchat Expert 16/05/2018 - 07:59
This is the Windows file path to a test file I have and i have an open file that grabs cell F4
Excelchat Expert 16/05/2018 - 07:59
First step is to make sure you can access the cells in a file out on the shared drive
User 16/05/2018 - 08:00
=[20180111_PCN_Antigen_Typing_Donor_Units.xlsx]FMC!\$B\$28
User 16/05/2018 - 08:01
is that correct?
Excelchat Expert 16/05/2018 - 08:02
I hope so , not sure of you file path, but if correct that should bring in the contents of cell B28 into a new control file.
Excelchat Expert 16/05/2018 - 08:02
You can test in a blank sheet.
User 16/05/2018 - 08:04
yes works
Excelchat Expert 16/05/2018 - 08:04
ok, great
Excelchat Expert 16/05/2018 - 08:05
So you can copy down all the rows you need
Excelchat Expert 16/05/2018 - 08:06
However, adjust the cell to allow copy by removing the \$ , should be \$b:2
Excelchat Expert 16/05/2018 - 08:06
that will allow you to copy down and each row will increase by 1, Copy B2- B50
Excelchat Expert 16/05/2018 - 08:08
In other words you want to replicate each cell in the control file. It is somewhat overkill, but will give you a good way to audit the whole sign process, vs just the missing stuff.
User 16/05/2018 - 08:10
FMC!\$B2
Excelchat Expert 16/05/2018 - 08:11
Yes ,copy the full string above, but take the \$ from the front of B as you have done above.
Excelchat Expert 16/05/2018 - 08:11
(FULL PATH) FMC!\$B2 -copy down in the control sheet.
User 16/05/2018 - 08:12
okay
Excelchat Expert 16/05/2018 - 08:13
Now I think this is the best for this situation , , but you can also color code blanks in B:
User 16/05/2018 - 08:13
FMC!\$B9:B33
Excelchat Expert 16/05/2018 - 08:14
Try this out first;
Excelchat Expert 16/05/2018 - 08:14
In cell C2:
Excelchat Expert 16/05/2018 - 08:14
=IF(LEN(B2)<1,"NEEDS to SIGN","OK")
Excelchat Expert 16/05/2018 - 08:15
this is simple formula to check for empty cell and will tell you , who left blank.
User 16/05/2018 - 08:21
In ex: A6 John did not date B6 is blank so I would like only Johns name on the control doc
Excelchat Expert 16/05/2018 - 08:22
Set up the control chart as a table
Excelchat Expert 16/05/2018 - 08:23
Select all 3 columns A;B:C
Excelchat Expert 16/05/2018 - 08:24
and HOME-INSERT-TABLE
Excelchat Expert 16/05/2018 - 08:24
Actually, lets not do that
Excelchat Expert 16/05/2018 - 08:25
Not sure how the data from other table and formulas will work.
Excelchat Expert 16/05/2018 - 08:25
What cell holds the name in the main sheet
Excelchat Expert 16/05/2018 - 08:26
A1 correct-?
User 16/05/2018 - 08:26
yes
Excelchat Expert 16/05/2018 - 08:27
we are about out of time. but try this and repost if you need more help.
Excelchat Expert 16/05/2018 - 08:27
=IF(ISNUMBER(B2), "OK", A2 &"Need to SIGN")
Excelchat Expert 16/05/2018 - 08:28
This formula will check for DATE in B2 (dates are stored as numbers in EXCEL),

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.