Copy formula if other worksheet cell <> null

[Suzanne]

FIRST: I need a formula to capture Data!A2 in Report!A2 if the value in
Data!A2 is not null (and so on for a range anywhere between 2 to 2,000 rows)

Report!A2 =IF('Data'!A2<>"",'Data'!A2,"")

SECOND: I also need Report! to accept row insertions/deletions from Data!
and/or insertions/deletions done in Report!

THIRD: I need to populate a third worksheet with data from two worksheets
(this is for my Word Merge) -- again, refreshing if 'Data'! or 'Report'!
change:

Merge!A2 =IF('Report'!A5<>"",'Report'!A5,"")
Merge!B2 =IF('Data'!A3<>"",'Data'!A3,"")

I've tried many of the solutions already covered, but have had no success.
This is obviously a simplified version of what I'm really working on, but if
I can get help with the formulas for the above, I believe I can get them to
work on the real thing.

My other option is to make users manually copy/paste data from 'Data'! to
'Report'! and then to 'Merge'! (there should be a way to avoid this)

Thanks -- Suzanne

 

[Mike H.]

You must have oversimplified it too much for me because I don't understand.
If Report is just a bunch of formulas from Data, why have report at all? How
can you insert or delete rows from Report if it is just referring back to
data? I don't get it. Maybe a little better explanation would be helpful.

 

[Suzanne]

The workbook essentially contains the following:

o Building: Contains common data needed for the Word merge doc
o Personnel: Contains common data for
o Ergonomic: About 15 columns of simple "X" entries, plus start date and
first/last name from Personnel
o Other: About 20 columns, including multiple validation drop-downs, one
of which contains over 1,800 options, plus nearly all personnel data
o MergeErgo: The sorted/protected worksheet (which includes Building data)
that Word uses for a very simple merge report
o MergeOther: The sorted/etc. (with Building data) that Word uses for a
very complex two-page merge report form

I would like to get Ergonomic and Other set up so that users can enter their
data without worrying about cells which contain formulas (to pull the data
from the Personnel worksheet) and/or the presence of a bunch of cells with
formulas but no values.

Suz

 

[Mike H.]

Perhaps someone else will "get it" as I am not sure what needs to be done.
Good luck. If you get no other replies, we'll have to go from there.