Hi all,
I'm slightly new to this and have a query that I'm sure you are all more than capable of answering.
I am trying to look up the first 2 digits of a 6 digit 'job number' eg 010203 which relates to a department each job number has an amount of time booked against it I am trying to find out and add up the amount of time spent on jobs for specific departments.
Any help would be greatly appreciated. If you require any more info please let me know.
Cheers,
Adam
Last edited by adam8090; 05-18-2011 at 12:05 PM.
Hello & Welcome to the Board,
Try...
=LEFT(A1,2)
HTH
Regards, Jeff
Hi Adam8090, Welcome
If your first digit is a '0' and you require that to show then try this (the B4 is the cell where you number is, insert this equation into the cell where you want the 2 digits to show)
=LEFT(TEXT(B4,REPT(0,6)),2)
hope it helps
Jim
Last edited by JamesT1; 05-16-2011 at 05:53 PM.
I fear what I am trying to do is slightly more complex than I first thought!!
I have attached an extract from the spreadsheet I am working form as you can see on the first worksheet 'timesheet' Column C contains the job no that is comprised of 6 digits I need to filter these by the first 2 digits then add the hours allocated to these numbers to give me the total on the second worksheet 'time spent by department' in cells B3, E3, and H3.
Hope this is clear.
Thanks
Adam,
Try...
=SUMPRODUCT(--(LEFT(TIMESHEET!$C$3:$C$7,1)+0=ROW(A1)),TIMESHEET!$D$3:$D$7)
Last edited by jeffreybrown; 05-17-2011 at 08:54 AM.
Hi Jeff,Originally Posted by jeffreybrown
That has worked really well thank you could you tell me what the variable id that I need to change in order to filter out the next number ie 02.
Thanks
How about we go with this instead and as you paste to the right you don't have to change anything.
=SUMPRODUCT(--(LEFT(TIMESHEET!$C$3:$C$7,1)+0=MID(B1,2,1)+0),TIMESHEET!$D$3:$D$7)
Look at the Mid function and this is looking at 01 - DEVELOPMENT and takes the second number 1 and uses it in the calcualtion.
When you drag to the right it changes to 02 - ENGINEERING and extacts a 2.
Hi Jeff,
Sorry to be a pain but I am struggling to get this to work now the attached spreadsheet is essentially a larger of the extract I posted earlier when I try and input the formula you gave me into this I cannot get it to work! Any ideas??
Thank you so much for all of your help so far.
Hi Adam,
Well, got it to work, but I think you are setting yourself up with a nightmare of a spreadsheet.
In order to get it to work, on the Timesheet - AK tab, in cell D12 I placed 00000. For whatever reason, when this cell is blank the formula is returning the #VALUE! and I cannot figure out why.
Anyway, one of the ideas behind creating dynamic formula's is so when you drag those formula's the references will drag making the formula work throughout any sheet.
With your current setup, you are creating a lot of manually manipulation of formulas. Instead, I believe you would be well served in creating a normalized set of data and then you can run all sorts of pivot tables to get your results. IMO will make it less hectic.
Here are a couple of sites which could help if you choose to do some type of userform.
http://www.contextures.com/xluserform01.html
www.fontstuff.com/ebooks/free/fsUserForms.pdf
Thanks for the advice Jeff problem is I think a user form is/will be a bit beyond my capabilities!!!!
Hi Adam,
Well I think user form's were above everybodies capabilities until they got in there and went toe-to-toe.
Anyway, if this satisfies your thread please do not forget to mark as solved.
To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks