+ Reply to Thread
Results 1 to 9 of 9

compiling data from 3 sheets

Hybrid View

  1. 03-31-2010, 08:42 AM #1
    miceic76
    miceic76 is offline
    Registered User
    Join Date
    03-31-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    5

    compiling data from 3 sheets

    Hi,

    Let me say hello to everyone as it's my first post here.

    Wonder if anyone will be able to help....

    So, I have data in 3 sheets. What I need to do is to calculate total rates for shops in different zones in certain time frame

    Basically I need to multiply "drivers payment" (column E, sheet 1) by certain amount (let's say 1.5 for zone 1, 5 for zone 2 ect.) for a certain time period (column A, sheet 1).

    All data as i mentioned earlier is spread over 3 sheets as per attached file.

    Any ideas how to do it?

    Thanks
    M
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-31-2010, 09:40 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: compiling data from 3 sheets

    I don't understand your workbook.

    Where does dates come into play? There is no indication of different pricing based on date or date range..

    Also, are we only interested in Sheet2? What does Sheet3 have to do with getting prices?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 04-01-2010, 02:57 AM #3
    miceic76
    miceic76 is offline
    Registered User
    Join Date
    03-31-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: compiling data from 3 sheets

    well, here's how it works...

    In sheet 1 A column represents dates on which drivers make deliveries to certain shops.
    I already prepared a formula to calculate drivers' payment given list of shops per zones, drivers' rates per zone and pallets quantity.

    What I need to do now is this:

    Let's say the dates in collumn A sheet 1 range from Jan to Dec 2010 with corresponding data in collumns B, C, D, E.

    The shop chain needs to know how much I am going to charge them for deliveries in January 2010 for each of 5 zones separately. That's why I created collumns G-K.

    Cells G3 to K3 should represend a sum of all drivers payments for certain zone in Jan multiplied by set rates:

    1.5 for zone I
    2 for zone II
    ect

    (I updated sheet 1 collumns G-K for better understanding) file attached.

    As to your question " Also, are we only interested in Sheet2? What does Sheet3 have to do with getting prices?" - I'm not too sure if we need data from all 3 sheets to do this...if not, that's even better right?

    Hope my explanation is clear.
    If not please ask.

    Many thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-01-2010, 08:42 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: compiling data from 3 sheets

    Not sure if you did for sample sake or not, but if the zone numbers and shop numbers in Sheet3 match up like that (i.e. shop number is equal to zone number), then you don't need sheet 3...

    ... somehow I doubt that is the case... so then, with Sheet3 in tact, and if I understand the question correctly....

    First, alter the headings in G2:K2 like this: zone I (1.5), etc...

    then in G3:

    =SUMPRODUCT(--(LOOKUP($C$2:$C$7,'sheet 3'!$B$2:$B$6,'sheet 3'!$C$2:$C$6)=1),$E$2:$E$7)*MID(G2,(FIND("(",G2)+1),LEN(G2)-FIND("(",G2)-1)
    copied across.

    Did I get you correctly?
    Reply With QuoteRegister To Reply
  5. 04-01-2010, 09:54 AM #5
    miceic76
    miceic76 is offline
    Registered User
    Join Date
    03-31-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: compiling data from 3 sheets

    first of all many thanks for helping us out!

    well,
    I think you got me correctly.


    As you said - the shop&zone numbers will not match up.
    the list I will have consists of approx. 200 shops spread between 5 zones.

    speaking of the formula - i forgot to mention that I'm unfortunately using OO.... so once i pasted the formula into G3 it returned with a mistake...

    maybe the comas should have a form of semicolons ect? tried to change it without any luck.
    Do you have any idea?
    Reply With QuoteRegister To Reply
  6. 04-01-2010, 10:02 AM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: compiling data from 3 sheets

    Unfortunately I have never really delved into OO so not sure of all comparitive functions and compatibilities.. I do know that OO uses semicolons instead of commas as argument separators.

    You might want to post the formula in an OO forum and see if anyone can translate.

    I attached a copy of the sheet with formula... maybe if you try to open it in OO it will translate automatically?
    Attached Files Attached Files
    Last edited by NBVC; 04-01-2010 at 10:04 AM.
    Reply With QuoteRegister To Reply
  7. 04-02-2010, 02:42 AM #7
    miceic76
    miceic76 is offline
    Registered User
    Join Date
    03-31-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: compiling data from 3 sheets

    WOW!!! NOW IT WORKS!

    MANY THANKS FOR THAT!!!! No Wonder you're Guru...

    If you have spare minutr could you explain to me what spome of the symbols within the formula mean so that I will be able to manipulate with it if necessary?

    What below mean for example:

    1) --
    2) *MID
    3) LEN(G2)
    4) FIND("(",G2)-1)

    Once again Many THANKS!!!
    Reply With QuoteRegister To Reply
  8. 04-02-2010, 07:24 AM #8
    miceic76
    miceic76 is offline
    Registered User
    Join Date
    03-31-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: compiling data from 3 sheets

    sont want to be a pest... but could you please explain to me in detail the following?

    *MID(G2,(FIND("(",G2)+1),LEN(G2)-FIND("(",G2)-1)
    Reply With QuoteRegister To Reply
  9. 04-02-2010, 12:57 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: compiling data from 3 sheets

    The MID() function finds a string within a string

    So =MID(reference,start_position,number_of_characters)

    G2 is the reference, it uses FIND() to locate the position of the first open parenthesis in G2, and adds 1 to that position to start right after that open parenthesis. To get the number of characters to extract, it determines the Length of G2 and subtracts the position to the first open parenthesis and one more to exclude the final character in G2.

    All these functions MID, LEN, FIND are explained with examples in Excel Help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1