Duplicating data from 2 tabs

Maybe you should put together a little mock-up file showing what you have in Sheet1 and what you want to achieve in the other sheets - the FAQ describes how to attach a file to a post.

Pete

Thanks for feedback Pete,

See attached, with only 2 inputs, not 5.

Thank you

There are 3 basic formulae in the attached file. I've used column G in the Capture sheet as a helper column, with this formula in G2:

Formula:

=IF(B2="","-",B2&"_"&COUNTIF(B$2:B2,B2))

This is copied down beyond your data. Then I've used column F as a helper in the subsidiary sheets, with this formula in F3:

Formula:

=IF(COUNTIF('Capture Sheet'!G:G,$A$1&"_"&ROWS($1:1))>0,MATCH($A$1&"_"&ROWS($1:1),'Capture Sheet'!G:G,0),"-")

again, copied down beyond your data to allow for expansion. The helper columns are coloured blue, but you can hide them if you wish.

Then the data can be retrieved using this formula in A3:

Formula:

=IF(OR($F3="",$F3="-"),"",INDEX('Capture Sheet'!A:A,$F3))

which can be copied across (with slight change to the range).

The only difference between the subsidiary sheets is the registration in A1, so to set up other sheets you can copy one of the existing sheets and just change A1 (and the sheet name).

Hope this helps.

Pete

By the way, your post has nothing to do with Conditional Formatting - please change your thread title. by clicking EDIT then Go Advanced and change your title.

Pete