I am looking for a formula of VBA if not possible. I will have 7 workbooks (from different users) and one master. They are all clones so have on one register sheet that has the same columns. The data will be different and the number of rows will change. At any point in time the master needs to be able to see all the data from all the rows on the register from all the workbooks on the master register. If I knew the number of rows it would be quite simple to link them all over. I could have a separate sheet in the master corresponding to each of the 7 registers (then vba which I've seen online to combine them into one) and just copy the formula down but it would give zeros for blanks which again I could get rid of but I'd prefer not to have endless formulae copied down and I'm sure there must be a neater solution out there? thanks