I have around 9 to 10 different sheets in workbook which have columns such as Name, DOB etc and some other columns too.
As i get new record i enter it in relevant sheet and fill all columns.
I want a master sheet which displays Name and Date of Birth of a person picking data from all the sheets.
Is it possible by simple formula ? i have been trying but unsuccessful.
Yes, using Index+Match or Vlookup you can do it.
Pls share the sample excel sheet to assist you in better way
Regards,
Thangavel D
Appreciate the help? CLICK *
I have attached sample excel. There is no data in it.
Basically if i enter one record in Sales sheet,the name and DOB entered should automatically come on main sheet, DOB should come against respective month that person was born.
Similarly if i enter one more record in sheet Marketing, same thing as above should happen.
Last edited by AliGW; 01-24-2019 at 07:59 AM.
Pls check the file
I've used vlookup to get month details from main to respective sheets.
This is not i wanted, this can be done with Vlookup i know that.
In main sheet i wont be adding data. i will be adding data in other sheets.
I want main sheet to be formulated in such a way that whenever i add any data in any of the sheets it should appear automatically in Main Sheet.
Basically Main sheet need to be formulated and not other Sheets.
Last edited by AliGW; 01-24-2019 at 08:00 AM.
It would help if you put some made-up data in your sample, then we can demonstrate to you how it could be done.
I would use a helper column in each of the subsidiary sheets, and a table in the main sheet to keep track of the number of records in each sheet, but it is difficult to set this up without any data.
Hope this helps.
Pete
I have attached updated Excel.
In Sales sheet i have added Name as "ABC" and DOB as "01-01-1981". So this data will be manually added when new record comes.
Now i Want Name "ABC" to be displayed automatically in Name column(Main Sheet A3 Cell) in Main Sheet and DOB which is "01-01-1981" to be displayed in Jan column(Main Sheet B3 Cell).
Similarly i want all records one below the other as i Enter new records.
Again if i enter some new record in "Marketing" sheet say name "CDE" should automatically be displayed below the earlier records in Main sheet and DOB which is "02-04-1978" to be displayed in Apr column since the month is April.
Last edited by AliGW; 01-24-2019 at 08:00 AM.
Check out the attached file.
I have added two helper columns in the department sheets (F and G, coloured blue) and copied these down to row 10 - copy further if required, in order to cover all your data. Note that the formula in F1 of each sheet looks back to the previous sheet, in order for the numbers to run on consecutively.
In the Main sheet I have added a small table (also in blue) to show where records exist in each sheet. I've also added a Department column (B), as I thought that would be useful to you. Then various formulae retrieve the data and put it in the appropriate column - these formulae have been copied down to row 20 in this demonstration.
I've added some test data to some of your sheets (shown in red), but you can delete these.
If you have any questions about any of the formulae, then feed back.
Hope this helps.
Pete
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Ok Moderator....
Pete this has solved my problem. Thanks...i am going through formulaes and logics used, if i have any doubt will revert back.
Glad to help.
You might like to click on the "star" icon below one of my posts, to show your appreciation.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks