Timesheet IF formula needed and VLOOKUP help

I am looking for help with two spreadsheets.
The first one is a timesheet, where i need a formula that will convert start and finish times to 15 minute intervals, however if for example a member of staff clocks in at 6:04 it needs to show 6:15 (3 minutes late is allowed, anything over is docked) The only issue i have at the minute with this formula is the start times can vary between 5:00 and 8:00 and the formula i wrote isnt long enough.

Secondly, i need a formula that will look up multiple cells on different tabs and enter the details onto a main invoice sheet at the front.
this is a work in progress so the spreadsheet can be attached should anybody need to look at what i mean.

Isn't everyone? Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Hi gordymoore5

So on the Invoice tab you are looking up a value in A16 on sheet C6493. Where is the table with the lookup value = C6463!

i want the first lookup to to look for the tab name, then the job parts

a better explanation would probably be A16 is the tab name, B16 is row 5 on tha tab, C16 is col a or c or e.

Or maybe that isnt a better explanation :-)

May be of some help if you put in the result you are expecting in E16 from the table marked in red below in your formula.

=VLOOKUP(A16,'C6493'!$A$6:$B$29,2,FALSE)

Hi

Then your lookup in the invoice sheet will be "Unit Type B5". Format the cell for 3 decimal places.

Hi,

If you're wanting to look up values then they must first exist in the object table. It's no good looking up say A5 (C6493) in column A of the C6943 sheet since all you'll find is the row 1 sheet name value.

You need to ensure that column A of the site sheet contains values like B5 and not 'Unit Type B5. Then you can use an indirect formula like

Formula:

Please Login or Register  to view this content.

So the formula in E16;
=IFERROR(IF(B16="North",VLOOKUP(A16,'C6493'!$A$6:$B$29,2,FALSE),VLOOKUP(A16,'C6493'!$C$6:$D$34,2,0)),"")

Hi,

Let's take this step by step. When you say that A16 will contain the job code, I think what you really mean is that you want to look to the SHEET that has that reference. That's a subtle but important difference to implying it's a value that exists in a cell. Hence you will need to include an INDIRECT formula as I showed you in my earlier post so that A16 can be INTERPRETED as a sheet name.

In B16 you have the value NORTH. To be able to use this and find the appropriate column in row 5 of say the C6493 sheet then the first thing you need is either the full description in B16, i.e. NORTH APPROACH TROUGH, or change row 5 so that it just contains the word NORTH, SOUTH and say EASTWEST. Then you'll be able to find the column with a MATCH() formula

Finally if C16 is to contain the value B5 then you need to have B5 as a value all on its own in Column A of the site sheet.

So assuming all this is in place the formula will be

Formula:

Please Login or Register  to view this content.

I'd also caution against merging cells as you do on row 5 of the site sheets. They are just more trouble than they are worth and in other situations will cause problems. Just use formatting and 'Center across cells' to achieve the same thing without the potential hassle.

Originally Posted by gordymoore5

i think this sheet may just be a delete and start again

??????????

Hi gordymoore5

Is the data different then the sample worksheet. If not you will only require 1 table.