Finding it hard to copy data from one worksheet to another

Hello everyone,

I am producing a workbook, where sheet one is the master sheet which includes details of all of our ongoing work on it.

Due to issues we have recently had with people filtering out data and then duplicating it and messing it up, I was looking at creating more worksheets, which organises the data by the store.

So for example, sheet 2 is store 3601. So I have added a formula into the cells in this sheet to copy across data from the main sheet which involves store 3601 (=IF(FIND("3601",Sheet1!A2)<>0,Sheet1!A3,"")

The formula searches the main worksheet for 3601 in the first column, then returns the value from cell a3 and put it in the cell the formula is in. When this is copied across into each cell I get the full row copied across.

The main problem is that I have a lot of #VALUE! cells returned from the rows where 3601 is not present, but the rest of the row on my new sheet is copied across.

Basically is there any way that I can get it to only copy across the relevant data, so I dont get the #VALUE! bits?


Cheers

Yes, you can do it with a few formulae. It would help, though, if you attached a sample Excel workbook, so we can see how your data is laid out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Hi Pete,

Please take a look at my sample data and assist me where possible.


Kind Regards,


Chris

In the attached file I've used column K in the Investigations sheet as a helper column, with this formula in K6:

=IF(A6="","-",LEFT(A6,FIND(" ",A6)-1)&"_"&COUNTIF(A$6:A6,A6))

This is copied down to the bottom of your table (as indicated by the hyphens), and it picks out the 4 digits for each store and adds on a unique sequential number, thus giving each record a unique reference.

In the other sheet, which I have renamed as Store_details, I have set up a data validation drop-down in cell E1 where you can choose the store from your pre-defined list. I have also used column K as a helper, with this formula in K6:

=IFERROR(MATCH(LEFT($E$1,FIND(" ",$E$1)-1)&"_"&ROWS($1:1),Investigations!$K:$K,0),"-")

This will find the row in the Investigations sheet where the first record for the store in E1 is located. When it is copied down, the ROWS($1:1) term becomes ROWS($1:2) then ROWS($1:3) and so on in successive rows, and thus will return the row where the second, then the third etc. matching record occurs.

I have put this formula in A6:

=IF(OR($K6="-",$K6=""),"",INDEX(Investigations!A:A,$K6))

which will bring the appropriate data from column A of the Investigations sheet from the row given by K6. Normally this formula can be copied across, but as you have a lot of blanks in the Investigations sheet, I've changed the formula in B6 to this:

=IF(OR($K6="-",$K6=""),"",IF(INDEX(Investigations!B:B,$K6)="","",INDEX(Investigations!B:B,$K6)))

which will return a blank if column B in the main sheet is blank. This formula can be copied across to J6, thus bringing all the other fields across. Finally, all the formulae in row 6 can be copied down as far as you think you might need them - I've just copied to row 20 in the example file, as can be seen by the hyphens in column K.

Now, instead of having multiple sheets, one for each store, you just have one sheet where you can change the store using the drop-down in E1.

Hope this helps.

Pete

Hi Pete,

Many thanks for your assistance, it has been of a great help.

I am trying to use this formula for something similar, but where there are already records manually entered into sheets. I need data from the main sheet to copy across into other sheets when it is inputted - is there a way I can do this, despite the previous records not following suit with this formula due to being manually entered?

Cheers,

Chris

I don't quite understand what the problem is - please elaborate.

Pete

Hi,

Basically i have a spreadsheet which records the data of our company's invoices on it. I have added a number of tabs to the spreadsheet, based on their category (ANPR, Guarding etc). I have then copied the data from the main invoices tab, into each individual tab manually.

What I would like to do from now on is set up some formula which would automatically copy the data from the invoices tab into the relevant tab when it has been entered into the main invoice tab - if you know what I mean?

So in this case it would be looking to transfer across based in the category of the data.


Many thanks,

Chris

So, can you not adapt what I did for your other file to this Invoices file? If it is very different, then please attach a new sample file - ensure that you don't include any confidential data, but use the same headings etc. that you have in the real file.

Pete

Hi Pete,

I did try adapting it, but I wasnt getting any joy.

I have added some test data for you to look at. I will have a few more tabs along the bottom for some more categories, but I am looking for the data from the 2016 invoice sheet to copy across into the relevant tabs, based on the category number, but in some cases it will need to copy across based on the category number and the budget allocation - as there are two different types of guarding, but they have the same category number.

Hopefully you will see what I mean on the attached.


Cheers,


Chris

I'm not really sure where the Category Number comes into it, but see if the attached file does what you are after.

I've put this formula in cell O2 of the Invoices sheet:

=IF(C3="","-",C3&"_"&COUNTIF(C$3:C3,C3))

and copied it down to row 100 (as the hyphens indicate) - you can copy it further if you have more data. It identifies which of the subsidiary sheets the record belongs to and sets up a unique sequential number for each record (you can't see this very clearly in the example file, as you only had one record for each sheet).

In the subsidiary sheets themselves, I have made them identical, by including a Store column in the two Guarding sheets, and I've also put the sheet name followed by an underscore in cell L1 (coloured yellow). I put this formula in cell K2:

=IFERROR(MATCH($L$1&ROWS($1:1),'2016 Invoices'!$O:$O,0),"-")

which finds the row in the Invoices sheet where the first record for that sheet can be found. I then put this formula in cell A2:

=IF(OR($K2="",$K2="-"),"",INDEX('2016 Invoices'!A:A,$K2))

which brings the appropriate data from column A of the Invoices sheet for the row given in cell K2. This formula can be copied across to bring corresponding data from columns B, C, D etc., although as you are not bringing across data from every column, the formulae in G2, H2 and I2 need to be amended slightly to point to the appropriate column in the Invoices sheet. You also need to apply the correct formatting to the date columns and the amount. The formulae in row 2 can then be copied down as far as you need them (I've only copied down to row 20 in the example file). The same formulae can be used in all the subsidiary sheets - you just need to change the entry in cell L1, as required.

Hope this helps.

Pete

OMG Pete, you are an absolute life saver, that has been of a massive help.

I have applied it to all of my data and it works brilliantly, however I did notice on one of the rows in the relevant tab, it has matched the relevant row, but the data has not copied across, despite the formula being present in each cell...any ideas???


Many thanks,


Chris

Hi Chris,

it is difficult to diagnose the problem without being able to see the workbook, but it might be that you have mis-spelt the entry in column C of the Invoices sheet - maybe a leading or a trailing space. The result in column O for that record might be able to confirm this. Although you do say that it matches the row, so I'm not sure what the cause is - you haven't set the font colour to white, by any chance?

Hope this helps.

Pete