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.
