MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy IF Staff Spreadsheet

  Asked By: Kerri    Date: Jan 28    Category: MS Office    Views: 1272

Our scheduling system creates a daily report of what work our guys are
issued but it does not create his name against every job and so we
can't autofilter it.

What i need it to do is search down a column (Staff Name) until it
finds a value then crossrefernce it to another column/same row (Post
Code) and then copy the Staff Name until the cell value in Post Coe
goes blank. I know i've made it sound complicated.

So in otherwords each guys name should be against each job which at the
moment it isn't. I can manually copy and then autofilter but it takes
about 15mins a every time (60 guys to go thru)



4 Answers Found

Answer #1    Answered By: Aaminah Khan     Answered On: Jan 28

Easiest way is probably to use a spare column  to hold a filled-in name.

Assume ... name is in column A; cells without names are empty; if the cell
is empty, then take the name from the previous cell  that is not empty; spare
column is G.


If this is copied down G, it'll bring names across, or look up to the
previous row  for a value.

You can then auto-filter on G.

Now this isn't a copy, it's a formula. E.g. you can't delete a row without
mucking things up.

If you want, though, you can select all of column G, then
copy/paste-special-values to get rid of the formulas.

Not sure why you'd look for Post Code being blank. If that's significant,
then this simple formula won't cater for it.

Answer #2    Answered By: Anne Powell     Answered On: Jan 28

If I send you what we get off the scheduler and what it needs to look like would
that be OK?

Answer #3    Answered By: Tate Thompson     Answered On: Jan 28

I understand the vertical search  for the StaffName. What is a bit fuzzy is; when
'horizontally' do I know there is data for the PostCode? Are the cell  contents
'blank' or 'empty' untill there is data for the StaffName?
Please advise.

Answer #4    Answered By: Charlie Evans     Answered On: Jan 28

Here is an example of what we get from our scheduler the amount of jobs can vary
so the staff names are not always in the same place.


And this is how i want it to end up.


So my macro then need to set up as an add-in so our back office can run this
before it is sent out to the team leaders.

I have blanked the address's with xx

Didn't find what you were looking for? Find more on Copy IF Staff Spreadsheet Or get search suggestion and latest updates.