Centralize text data from multiple sheets into one sheet

Hi there,


First of all I want to state that I am not so advanced with using Excel, but lately I have a lot of work with this software so I started to encounter diffrent 'problems' for which I need your help.

So, I have this Excel file that have multiple sheets, for each meember of the Sales department plus one sheet that is meant to centralize all the data from each individual sheet.

My problem is this: how can I centralize the data (text) from all the sheets into one sheet so that when somebody inserts one line of data it will automatically appear in the centralized table in the first sheet, so we will have an overlook of all that is happening in the department?

I tried using IF function combined with ISTEXT and ISBLANK but I could only obtain a static formula, good only for the first emplyee of the department, but I cannot find something good enough for the rest. Also I couldn't find something relevant in my research until now, so I would need your help to sort this up.


Thank you in advance!

Hi Romconsulting,

iSee if attached file work for you otherwise you will have to get help from macro division of this forum

in my format insert the lines in between each user as & when you feel its getteing over paste above line formula in it


Thanks,
A

Hi amy_d2,


Your solution is simple and elegant, I guess it would do for what I have to do, altough it is not quite I imagined. I was afraid that I can't do this without any VBA coding.

Also, it would be interesting if we could do this only using functions and formulas.


Thanks,
R.

Hi romconsulting,

Glad that it help you to some extend. formula's cant change /shift any places in excel, hence new insertion of rows & logical allocation of data is not possible through formula , as far as my knowledge.

also i don't think any of the function is available for your requirement too.

hence VBA code would be appropriate solution for your requirement.

Thanks
A