Incrementing cell horizontally, sheet number vertically.

Hello,

I've been trying to work this out for a while.

I've got a spreadsheet with all staff's individual hours which I use to calculate accrued holiday etc. We also have a timesheet spreadsheet which is filled in on a weekly basis. This one sheet contains all staff hours for that week. I have automated everything except for entering the staffs hours.

I would like to be able to copy each weekly 'timesheet' sheet into a sheet on my individual employee hours spreadsheet (I don't want to import the data directly)

Then be able to use a formula to correspond to the person's hours on the timesheet into their individual sheet and be able to drag the formula across and down, incrementing the cell number horizontally and sheet number vertically on each of our employee named sheets.

This would mean, instead of spending ages doing the data entry, I can just drag the formula down each week.

So it would be:

First weekly timesheet data in Sheet1 cells B2 - B6
Second weekly timesheet data in Sheet2 cells B2 - B6

'Bobs' sheet

First Week: =Sheet1!B2 =Sheet1!B3 =Sheet1!B4(..)
Second Week: =Sheet2!B2 =Sheet2!B3 =Sheet2!B4(..)

I have found a workaround using =INDIRECT("Sheet" & ROW(A1) & "!B25") and changing the cell value manually and dragging down. The problem is the rows on the initial timesheet also change around a lot, so 'Bob's' hours could be on row 7 one week and then row 8 the next week so I also need to be able to search for the name in the column and then use that row reference in the formula, possibly using an IF statement?

I am not sure if this is actually possible but would really appreciate any help given (even if it is just to say that)!

Apologies,

We have weekly timesheets with everyone's names listed on the left and then mon, tues, wed, thurs, fri as column headers with corresponding hours in the cells. This information will be in another sheet in the same document (as in the document you supplied).

I also have tabs for each person which contain only their own weekly hours. I need the data from the weekly timesheet to be input into the corresponding week on their individual tab.

So yes, sorry, I think you have interpreted me correctly!

The formula you gave seems to do what I want in your document but I'm having trouble translating it for mine, do you mind explaining what the different parts are referring to so I can make sure I am changing the right values?

It would help if you attached a sample Excel workbook, so we don't have to guess how your data is laid out, and we can supply you with a formula bespoke to your requirements.

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

OK this is the layout I'm working with:

It's important that the names in A1 of the individual sheets are the same as those used on the weekly timesheets, so I put Bob, Tom and Mary in cells A2:A4 of sheets 1 to 3, then you can use this formula in E2 of the individual sheets:

=IFERROR(VLOOKUP($A$1,INDIRECT("'Sheet"&ROWS($2:2)&"'!A2:F34"),COLUMNS($D:E),FALSE),"")

Copy this across to I2, then you can copy those formulae down. Do the same for the other individual sheets.

Hope this helps.

Pete

This is the part that determines which sheet number to get the data from:

'Sheet"&ROWS($2:2)

which basically evaluates to "Sheet1". When it is copied down, the ROWS part changes to ($2:3), then ($2:4), then ($2:5) on successive rows, so you should always ensure that the formula I gave you is put into the first cell (i.e. cell E54 on Bob's sheet), and then copied across and down from there.

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).