Automatically post row details from master sheet to other sheets

Hello,

I am working on a compensation and benefits workbook. I have a master sheet where all entitlements are entered. On the master sheet, I have columns for Date, Employee name, Department, Amount and Transaction ID. I have other sheets for each department to compile compensation and benefits transactions for that specific department. What I need to do is to ensure that the data for each employee as entered on the master sheet gets posted directly to the sheet for the department to which that employee is related as specified on the master sheet. I have attached a workbook example as a guide. So for example, the Date, Employee name, Amount and Transaction ID for Smith John must be automatically posted to the next empty row on the Mining Sheet. I need help with getting a formula to do that automatic posting for me. What I have been doing is to filter the data on the master sheet and copy each row to the required departmental sheet but this has been very slow and inefficient so will appreciate any assistance to get the posting done automatically as and when new data is entered on each row.

Secondly, I also need a formula/function/trick that will generate a popup notification to enter the transaction ID on the master sheet and prevent the next row from being active until the transaction ID is entered for the current active row.

Thank you.

Using array formula, hope it works

Thank you very much, it worked like magic!!!!

But I was wondering if there are any alternatives to array formulas other than VBA (I don't know anything about VBA)!

Also, what if it were the other way round, as in entering into the other sheets and getting posted automatically to the next empty row on the master sheet so that the master sheet consolidates all the other sheets automatically?

The other part of my problem that is not solved is the cell restriction and pop up part. Can you help?

Thanks in advance!

Hi,

Try this with arrya function


Punnam

Thank you too Punnam. Your solution is similar to that suggested by azumi and it worked perfectly. I am grateful! Are there alternatives to array formulas?

Can you also help me to do a reverse of the same thing? As in, post instead from the departmental sheets to the master sheet such that entries on the departmental sheets get copied/posted automatically to the next empty row on the master sheet.

I would also like a formula/function/trick that will generate a popup notification to enter the transaction ID on the master sheet and prevent the next row from being active until the transaction ID is entered for the current active row.

Thank you.

First Sorry for providing the same solution

i amused the solution provided by Azumi was of VBA .for the rest let me try whether i can find a way .

Punnam