Moving data from one worksheet to another - based on certain values

[paul.hutchinson]

hi there,

I have a spreadsheet with two tabs - and I want to copy some data from one tab to the other based on certain criteria.

In tab 1, I have a column with a list of staff including employee ID (unique) and then a series of columns for each day of the year.

In tab 2, I have a column with the same unique ID, and then columns for each day of the year but this time populated with shift pattern or an "R" if on annual leave.

The unique IDs are not in the same order on each tab, and the information on Tab 2 is changed weekly

I want to copy to the "r" from the second tab to the first tab, but not the shift patterns.

I was thinking of trying to combine some kind of VLOOKUP and IF statement, but can't figure out how to do that.

Can anyone help please?

Paul

[AndyPS]

To help identify the best way forward, perhaps you could say how many employees there are and what happens when there is a new employee or one leaves. Presumably there is an update to tab 1
Do you want just a formula-based solution or would you consider VBA (Macro)?

As a starter, I have appended a small example workbook using a formula (Vlookup and IF).

=IF(VLOOKUP($A2,'Tab2'!$A$2:$Q$6,COLUMN()-2,FALSE)="R","R","")

The 'COLUMN()' function wihtin it is to select the column from the look-up table based on the position of the column in Tab1. In this example, the first day on Tab2 is in column B (=2)whilst the first day on Tab1 is column D (=4). Thus we need to take 2 off to get the same column. Another reason for using COLUMN() is to avoid having to put in the explicit column number in each VLOOKUP. As it is, with the mix of references, the formula can be copied to each appropriate cell.

There are quite a few pitfalls - any change to the number of rows in Tab2 requires the formula to be rewritten. (Ideally use a range name.) It assumes that the days are in the same order and that the 'R' is always upper case. I probably would not use this myself for more than say 100 employees but instead go to a macro to find the record, copy the data and delete the shift patterns.

Hope this helps (if so remember the star!)

[paul.hutchinson]

Firstly, many thanks for the spreadsheet and the extensive answer - very much appreciated.

In answer to your questions, there are 310 employees, but this could change - in this case, I can add in additional line items on both tabs to ensure alignment. A formula is great, but I woudl be equally at home with a macro, although I have no experience of creating a code - only pressing start and stop!

The spreadsheet you created is pretty much what I have so is perfect for what I need - and many thanks for the COLUMN command, as I had previoulsy thought I woudl need to alter for each cell.

However, I know have some more questions....

This formula will now transfer the cell contents provided it is an "R", and I could add to this with an OR if other codes were used. The problem is that there is already data in the cells on Tab 1 - and this data will be deleted when the formula is added.

So now I should expand on tell you what i'm really trying to do......

On tab1, managers can place in data that shows when they are expecting to have a particular resource available - by placing a '1' against the resource and the date.

On tab2, I have a list of the resources, there current shift patterns, and when they have annual leave "r". I then use conditional formatting to change these cells red.

So what would be ideal is to change the colour of the cells in tab 1 based on the data in tab 2 - the problem being that the names are in a different order on each tab and there are additional columns so that there is no direct relationship.

Does this make sense? not sure how easy this is to resolve, but any help would be appreciated.

Paul.

[6StringJazzer]

Welcome to the Forum!

Let's say that in both worksheets, your employee ID is in column A and days of the year start in column B with Jan. 1. Put this in B2, then fill down and to the right.

=IF(VLOOKUP($A2,Sheet2!$A:$NB,COLUMN(),FALSE)="R","R","")

[6StringJazzer]

I did not preview before I posted so I did not see AndyPS's reply first. That solution correctly takes into account that the columns may not exactly match in the two sheets; paul.hutchinson did not say that they were different but he did not exactly say they were the same either I just assumed they were the same.

To avoid the issue with how many rows there are, simply use a range of columns as in my example.