Hi Guys,
I need advise on how to make this spreadsheet work.
http://www.mediafire.com/?l5c9wb73cns9rt0
Sorry for the link, the file is tagged to be to large. Anyway, I need to search for the earliest time in Sheet1!E:E, this would need to be filtered by dates in Sheet1!G:G.
Does anybody have ideas on how to make this work?
Thanks in advance for any help.
can you please upload a sample sheet, your link is not opening from my office, it is blocking.
How do I attach my file again? There's no option for it now.
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thanks!
Btw, example is in Sheet3!C2, it needs to show the earliest time from Sheet1!, with Filters of Employee 1 and Oct 1.
Again thank you for any help or idea you can give.
A simple way could be this.
In M2 of sheet1 and copy down use this.
Formula:
Please Login or Register to view this content.
If you like hide this column.
Then in C2 of your other sheet and copy down and across use this ARRAY formula.
Formula:Please Login or Register to view this content.
Format these cells as Time.
-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.
Or, perhaps this array formula in C2 and copy across and down:
=TEXT(MIN(IFERROR(1/(1/(IF(Sheet1!$K$2:$K$5226=CONCATENATE(0+Sheet3!C$1,Sheet3!$A2),MOD(Sheet1!$C$2:$C$5226,1)))),"")),"h:mm AM/PM")
Regards
Thanks! Rep added! however, will there be a way for me to set that if time is PM, use MIN, if time is AM use MAX? or another way around is the check column in Sheet1, as only valid entry wound be from 2F and Time in is in PM (Time out is AM). Sorry for making this too troublesome.
Thanks again in advance for any help!
Sorry - whose solution are you referring to? Mine or Fotis's?
Regards
Pl see attached file.
Formulas in E & G columns in Sheet1 is changed.I feel this is what is required.
Any of the two, or whichever can be flexible enough to have many criteria.Originally Posted by XOR LX
Thanks! However, I tried to update the formula in G column to:
As I need to filter the entries to only those that are from door 2F-Prod Door. But it doesn't affect the values in Sheet3. Is there any way to tweak the formula to add the door filter?
Please Login or Register to view this content.
Thanks a lot guys,
Filter for Entry from door 2F-Prod Door or Exit from door 2F-Prod Door or for both.
Is the formula not sensitive to blank cells? the formula above kinda filters the door, making non 2/f entry blanks. As the source actually being generated on a real time basis I would need to have a check point to filter the 2/f data without deleting the other entries. sorry for this troublesome request.
TIA!
Anyone?
Thanks for any ideas and advice.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks