Question description:
This user has given permission to use the problem statement for this
blog.
I need the column C in ACTIVITY sheet to display only items that have populated fields in the DATA INPUT sheet for each person. Details will be provided!
Solved by D. Q. in 59 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/09/2018 - 09:45
Hello
User
10/09/2018 - 09:45
Hello D.D. I was left off with one of your colleagues as he was working on my sheet.
Excelchat Expert
10/09/2018 - 09:46
oh.. Ok.. Do you have the sheet upto which thye worked?
Excelchat Expert
10/09/2018 - 09:46
Did you give the same description?
User
10/09/2018 - 09:46
No, unfortunately. We realized we could no longer extend the session and he or she never managed to send the sheet.
User
10/09/2018 - 09:46
It was essentially done, as well.
Excelchat Expert
10/09/2018 - 09:47
were you able to reach out to the support
User
10/09/2018 - 09:47
No. I will try right now, thank you for that suggestion!
Excelchat Expert
10/09/2018 - 09:49
if you do not mind Can you send the data i can work and provide the solution
User
10/09/2018 - 09:49
[Uploaded an Excel file]
User
10/09/2018 - 09:49
Here is the Excel
Excelchat Expert
10/09/2018 - 09:49
Non of the colleagues responded to my initial query they might have logged off or in other session
User
10/09/2018 - 09:50
Okay, so Data Input is the database.
Excelchat Expert
10/09/2018 - 09:50
ok i'm on activity sheet
Excelchat Expert
10/09/2018 - 09:50
ok
User
10/09/2018 - 09:50
Let's look on Activity sheet, yes
Excelchat Expert
10/09/2018 - 09:50
ok
User
10/09/2018 - 09:51
When I select a worker in A,B of Activity Sheet, I would like column C to display only those items which have cells populated in the Data Input sheet
User
10/09/2018 - 09:51
Instead of ITEM, ITEM, ITEM, I would like to only see those items which have populated fields in the Data Input sheet
User
10/09/2018 - 09:52
What's to keep in mind is that the little form in Activity sheet is an Output for Data Input sheet. It will display one worker at a time, depending on selection. It is also a monthly summary of a worker's activity. B24 in Activity allows me to select the month to display.
Excelchat Expert
10/09/2018 - 09:53
in Data Input that employee have multiple rows and each of them will have different items
Excelchat Expert
10/09/2018 - 09:53
you want to pull together all those lists?
User
10/09/2018 - 09:55
No, In Data Input each employee has his own row. Each Column in Data Input from J to CR is an item.
User
10/09/2018 - 09:55
In Column C, in Activity I want to have displayed only those items that are populated for that particular worker.
Excelchat Expert
10/09/2018 - 09:56
lets take example PETRE BARBU
[Uploaded an Excel file]
Excelchat Expert
10/09/2018 - 09:56
attached is the screen shot of data input
Excelchat Expert
10/09/2018 - 09:57
so u want in activity for day 2 item 2 NS ITEM 3 be populated i.e column F?
Excelchat Expert
10/09/2018 - 09:57
or in column C you want item 2,3?
User
10/09/2018 - 09:58
Basically, PETRE BARBU has only ITEM 2 and ITEM 3 populated in Data Input sheet, ok? Well, I want, in the Activity Sheet col C, instead of ITEM, ITEM, ITEM to have displayed ITEM 2 and ITEM 3.
User
10/09/2018 - 09:58
And the form in Activity Sheet is on a monthly basis, so it's important it takes all a worker's activity throughout the month.
Excelchat Expert
10/09/2018 - 09:59
ok so if he had item 5,6 in another date then column c should be item 1,item 2 ,item 5,item 6?
User
10/09/2018 - 09:59
Yes. As long as it's the same month, yes.
Excelchat Expert
10/09/2018 - 09:59
hmm ok let me try
User
10/09/2018 - 10:00
Okay. I will have to be away for a little bit, but I will make sure to prolong the session.
Excelchat Expert
10/09/2018 - 10:00
yes at the end of 2 min you will have to click on the extention
Excelchat Expert
10/09/2018 - 10:01
quick question
User
10/09/2018 - 10:01
Yes?
Excelchat Expert
10/09/2018 - 10:01
all the values in Data input is being pulled from Worker Input
User
10/09/2018 - 10:02
Yes.
Excelchat Expert
10/09/2018 - 10:02
can i use that to get the monthly items?
User
10/09/2018 - 10:03
If you cannot, under any circumstance, do it from Data Input, yes.
Excelchat Expert
10/09/2018 - 10:04
i'll give it a try with data input 1st then move to Worker Input as i see it daa input has information from Worker Input just reprasented in a array format
User
10/09/2018 - 10:10
Yes
Excelchat Expert
10/09/2018 - 10:25
still here almost there just testing various senarios
User
10/09/2018 - 10:25
I hope you managed to do it with Data Input - that'd be utterly lovely.
Excelchat Expert
10/09/2018 - 10:29
Yes i did in spite of the fact that i wasn't sure it was doable :)
[Uploaded an Excel file]
Excelchat Expert
10/09/2018 - 10:29
please verify if this looks good
User
10/09/2018 - 10:31
Hm. I just changed the name in ACTIVITY and whenever I reselect BARBU PETRE nothing appears in Column C
User
10/09/2018 - 10:31
Nor do I get anything when I select another name that has data populated in DATA INPUT sheet
Excelchat Expert
10/09/2018 - 10:32
hmmm let me check that . i tried just changing values in data input to see if they appear let me check why it isn't appearing
User
10/09/2018 - 10:32
Okay, thank you!
Excelchat Expert
10/09/2018 - 10:36
1 min found it
User
10/09/2018 - 10:36
Okay!@
Excelchat Expert
10/09/2018 - 10:37
[Uploaded an Excel file]
User
10/09/2018 - 10:38
Two more things I want to get down:
Excelchat Expert
10/09/2018 - 10:38
does the excel look good?
User
10/09/2018 - 10:39
Yup, works well!
Excelchat Expert
10/09/2018 - 10:39
ok
User
10/09/2018 - 10:39
1. In Data Input sheet, I will continue putting in data for the following months. Will this formula still work? Do I need to do anything myself to extend it?
User
10/09/2018 - 10:39
What I mean is I will go lower with rows, probably in the tens of thousands.
Excelchat Expert
10/09/2018 - 10:40
No as long as the month in b24 matches the month in G column your good
Excelchat Expert
10/09/2018 - 10:40
oh rows
Excelchat Expert
10/09/2018 - 10:40
then yes
Excelchat Expert
10/09/2018 - 10:40
1 min will tell you the changes you need to make
User
10/09/2018 - 10:40
Okay, brilliant.
Excelchat Expert
10/09/2018 - 10:40
currently if you check C6 in activity you will see the following formula
Excelchat Expert
10/09/2018 - 10:40
=IFERROR(INDEX('Data Input'!$J$1:$CR$1,SMALL(IF((LEFT($A$3,FIND(" ",$A$3)-1)='Data Input'!$B$3:$B$1130)*(RIGHT($A$3,LEN($A$3)-FIND(" ",$A$3))='Data Input'!$C$3:$C$1130)*($B$24='Data Input'!$G$3:$G$1130)*('Data Input'!$J$3:$CR$1130<>""),COLUMN('Data Input'!$J$1:$CR$1)-COLUMN('Data Input'!$I$1)),ROW(1:1))),"")
User
10/09/2018 - 10:40
Yes
Excelchat Expert
10/09/2018 - 10:41
now if you notice this formula checks till row 1130
Excelchat Expert
10/09/2018 - 10:41
'Data Input'!$B$3:$B$1130 and so on
Excelchat Expert
10/09/2018 - 10:41
so if you increase the rows come to this formula change 1130 to any number you want then click cntrl+shift+enter
Excelchat Expert
10/09/2018 - 10:41
NOT ENTER
Excelchat Expert
10/09/2018 - 10:42
this is an array formula that is the only thing you need to do
User
10/09/2018 - 10:42
Ok. Do I change ALL 1130s in that formula to the number I want?
Excelchat Expert
10/09/2018 - 10:42
I can increase it now it self but it will slow your excel
Excelchat Expert
10/09/2018 - 10:42
Yes do not touch any other nubers...
User
10/09/2018 - 10:42
It's okay, I will change it on a need basis so it doesn't load the Excel.
User
10/09/2018 - 10:42
Okay, sounds good!
Excelchat Expert
10/09/2018 - 10:43
make sure the formula has { } at the start and end of the formula
User
10/09/2018 - 10:43
Okay, will do!
Excelchat Expert
10/09/2018 - 10:43
[Uploaded an Excel file]
Excelchat Expert
10/09/2018 - 10:43
Have a Nice day
User
10/09/2018 - 10:43
Have a nice day too and ..... million thanks! Absolutely wonderful, you were! Thank you!
Excelchat Expert
10/09/2018 - 10:44
:) Please do leave a feedback once you end the session
User
10/09/2018 - 10:44
Will definitely do! Thank you!
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.
