Extracting data from a row on one sheet into another sheet based on a unique cell

**roshanvarghese** · 01-22-2013, 06:30 AM

Hi, I just want to say thanks in advance.
I have a team of 5 people and i want to create a job list for their activities. I have attached an image with 4 excel sheets. Sheet 1 is the master sheet and sheets 2-4 are individual sheets. The actual file has 6 sheets, but i showed only one.
Now, coming to what i want to do.
Each of the individual sheets (2-6) show the activities of each person. each activity has a unique job code. so i feed in each persons activities in their respective sheets, and i want the same to be consolidated and reflected in the master sheet as and when i make an entry in the individual sheet. so if you can show me how to do the following i would be very obliged.

1: I make an entry in an individual's sheet, it automatically reflects in the master sheet
2: Any changes i make in the individual sheet should reflect in the mastersheet.Picture1.jpg

**zbor** · 01-22-2013, 06:32 AM

Can you upload excel workbook rather than picture?
Make sure to avoid private informations.

**roshanvarghese** · 01-22-2013, 06:37 AM

sure. sorryJob Sheet1.xlsx

**zbor** · 01-22-2013, 06:44 AM

Is there a spetial reason why you doing things in opposite way?

In Excel is common practice to enter all data in what you call master sheet, and then you can filter or extract values in each sheet from that master.

**roshanvarghese** · 01-22-2013, 06:47 AM

Either way would be fine. I dont mind entering everything into the mastersheet and then having it reflect in the individual sheets. I just dont want to do the work twice

But any change that i make in the mastersheet should reflect in the individual sheets.

**roshanvarghese** · 01-22-2013, 06:50 AM

In which case, I think we should add a name column in the master sheet so that the name can be used as a reference to move it into each respective sheet. do you think that would make sense?

**zbor** · 01-22-2013, 07:59 AM

look at this example.
You add everything on Master sheet.
One row after other.

And in all other sheets automatic pulling values for each user.
You must create one sheet per user (I did for 2 and I'll show you how to do for others):

Right click on existing user sheet name (for example S)
Move or copy
(move to end)
check box Create copy
(look at the picture in attachment)
OK

Now your sheet will be named S(2)
Right click
Rename
and write some other name like V

Then go to A1 of that new sheet and write user name into that cell.

Another way would be to have drop down list in that A1 cell with all names and then you don't need to have all sheets. Only one.
That is showed in sheet Example and sheet List names is used just to have that names.
Then you can have all names there (with maybe some other info of each user).

Of course, all of that you can have if you add Filter in Master sheet (as I did). Then I see no point of another sheet when you can just filter out per any user)

**roshanvarghese** · 01-22-2013, 09:17 AM

Hi,
Thanks a lot. this has really helped. its worked as i wanted.
there is just one small thing.
if i enter some content in the boxes, but not in all the columns, the boxes in the individual sheets are shown as dates. if i enter something then it comes properly.
what do i do?

**zbor** · 01-22-2013, 09:59 AM

Select all columns, right click and format cells as General (unless you want dates).

Also, if some datea is missing it will write 0.
If this is not OK for you change formula to:

Formula:

=IF($A2="","",IF(INDEX(Master!C$1:C$10000,$A2)="","",INDEX(Master!C$1:C$10000,$A2)))

**roshanvarghese** · 01-23-2013, 12:22 AM

Hi,
I tried using the formula that you gave, but it doesn't seem to work.
I have attached the actual sheet that i want to use.
If you look at the master sheet, i have made some entries.
If you look at the corresponding individual's sheets, you will see that the entries are getting duplicated successfully as i wanted. The only issue is that if i do not enter anything in the remarks column, a date is shown there.

Is there a way to make it in such a way that it would remain blank if the corresponding cell in the mastersheet is blank?

Really appreciate your help on this.

Thankstrial.xlsx

**zbor** · 01-23-2013, 03:49 AM

I don't understand.
I've just copy-paste above formula into Arwind sheet C2 cell and it works.
What doesn't work for you?

Extracting data from a row on one sheet into another sheet based on a unique cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Re: Extracting data from a row on one sheet into another sheet based on a unique cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions