Question description:
This user has given permission to use the problem statement for this
blog.
Spreadsheet with several tabs, each with a persons name. When that person enters info into their tab, I need it to populate onto a "cover sheet" that is hopefully very simple and just has column A (a dropdown list of the names) and column B which I'm trying to figure out how to get to populate a simple sentence if the person types a task on their tab. does that makes sense? I think I'm making this harder than necessary.
Solved by F. F. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
18/05/2018 - 05:02
Hello and welcome to Got it! :)
User
18/05/2018 - 05:02
hi
Excelchat Expert
18/05/2018 - 05:04
If I understand correctly, you wish to populate a single sheet with data relevant to updates on each of the person's sheets. Basically, you want to link all sheets to a single tab. It's perhaps best if we could demonstrate this with an example. :)
Excelchat Expert
18/05/2018 - 05:05
Or if you could provide some sample data we could work on, it would be great. :)
Excelchat Expert
18/05/2018 - 05:07
Hello, still there? :)
User
18/05/2018 - 05:08
I cant provide data as it is client info and is private.I t is as you described. I simply want column B to populate something like "Kim has requested a task be completed" when "Kim" types on her tab
Excelchat Expert
18/05/2018 - 05:08
Okay, let's try doing this then with an example. Can you see the document preview to the right?
User
18/05/2018 - 05:09
but I need it to work for 10-12 different people so I have ste up a DATA page just in case I need it
User
18/05/2018 - 05:09
yes
User
18/05/2018 - 05:10
I do have a dropdown list in ColA but not sure if I need it
Excelchat Expert
18/05/2018 - 05:11
You don't need it actually. We can just list the names in Cover sheet, column A, and make a notification in column B if someone makes a request/update
User
18/05/2018 - 05:13
This is an ongoing, spreadsheet, the names will change every day--the sam few, but who is using it will change, it isn't a static ting
Excelchat Expert
18/05/2018 - 05:14
If the tab names change, we actually need to reflect the changes in the cover sheet as well, since there's no non-VBA function that lets us fetch the names of the tabs.
User
18/05/2018 - 05:15
Kim amay enter 1 thing one day and 8 tasks the next. One day there may be 4 people using the sheet and the next day 12 people are adding tasks to be completed--maybe 3-8 thinges each
User
18/05/2018 - 05:16
Is it easier if I take away the name column and just have a formula on each tab to link to the Noticification cell?
Excelchat Expert
18/05/2018 - 05:16
Oh, like we just show the notifications in the coversheet then?
User
18/05/2018 - 05:17
It might be easier than having the dropdown and the notification will come from each tab and the name will be in the notification.
Excelchat Expert
18/05/2018 - 05:20
In the cover sheet, we can try to make separate columns for each tab, then populate the cover sheet with all the updates with each tab.
User
18/05/2018 - 05:21
that's nice. how does that work?
Excelchat Expert
18/05/2018 - 05:21
If you can see the document preview, we can do it as that. So every time they make updates on their respective tabs, it gets copied to the cover sheet.
Excelchat Expert
18/05/2018 - 05:22
Each of the column is linked to the column A of the person's notification. For example, John's notification is linked to the updates done in the column A of his tab.
Excelchat Expert
18/05/2018 - 05:23
Linking a cell to another cell is pretty easy to do.
Excelchat Expert
18/05/2018 - 05:24
For example, we want to link cell A2 of the cover sheet in the cell A2 of John's tab. You can do that by clicking on cell A2 of cover sheet, then typing the = sign.
User
18/05/2018 - 05:24
Even if the "tasks" will be changed/erased eachday?
Excelchat Expert
18/05/2018 - 05:24
After you typed the = sign, click on John's tab, then click on cell A2 in that sheet.
Excelchat Expert
18/05/2018 - 05:24
yes. :)
Excelchat Expert
18/05/2018 - 05:25
So what happens in each of the column A of each tab is reflected in the cover sheet.
Excelchat Expert
18/05/2018 - 05:26
If you try to go in Kim's tab and type anything in column A, it will get reflected in the cover sheet
Excelchat Expert
18/05/2018 - 05:28
The cell content of the cover sheet should not be edited by the way, as it may unlink the cells to their corresponding tabs and columns.
User
18/05/2018 - 05:28
So, it's just =, click on tab then A2 ?
User
18/05/2018 - 05:28
no formula?
Excelchat Expert
18/05/2018 - 05:29
That's actually the formula. :)
Excelchat Expert
18/05/2018 - 05:29
That's still considered as a formula btw. :)
User
18/05/2018 - 05:30
I feel like Im missing something. So for Kim, it would be =Kim's tab, A2?
Excelchat Expert
18/05/2018 - 05:30
Yes! :) But the row numbers should match. So in the cover sheet, you would want to link A2 of Kim's tab to cell B2.
User
18/05/2018 - 05:31
Ok then. I'll give it a try. Thanks for your help!
Excelchat Expert
18/05/2018 - 05:31
And you can link Kim's A3 to coversheet cell B3, A4 to cell B4, and so on, by just dragging down the A2 link you've just made. :)
Excelchat Expert
18/05/2018 - 05:31
If everything is good then, you can now end the session (can't do it from my end). Please do ensure to make a good feedback upon your exit! Thanks again and have a nice day! :)
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.
