I am trying to combine data from 3 different sheets ("Sheet 1", "Sheet 2" and
"Sheet 3") into one ("Linked Sheet").

I would want the Linked Sheet to get updated automatically when new entries
are made into the three individual sheets, but in such a manner that only
rows of data that have a nonblank entry in a certain column ("Signifier") get
transferred - Excel would check a given data row for a particular column
("Signifier") and if it is nonblank, then it would transfer from the given
row selected group of entries in columns "A", "D", "W", "R", C", in that
order.

In addition, I would like to have the rows carry with them history of what
sheet they came from that would go into a new separate column (labeled "Data
Source") on the linked sheet that would automatically get filled in with
"Sheet 1" if it comes from first sheet, "Sheet 2" if it comes from the second
sheet, etc.

Is there any way to make such automation work in Excel? If anybody knows how
to or figures this out, I would be very very pleased and grateful to that
person.

Thank you very much!