Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula calculation automation by value insertion

    I have a spreadsheet where I am trying to automate the calculation of 17 lines for all the employees in my organization for budgeting purposes. On the attached spreadsheet, you'll note that on Row 3, Row 4, Column B, and in Column C, I have highlighted the series of numbers in red or tan.

    What I am trying to automate is to take each of the numbers from Row 3, insert them to Column B where the XXXXX are in red, and then based on the value that comes to, multiply Row 4 with Column C.

    Example using the first three strings:
    01-51400-61111-00000-000 = 100,000 * 35% = 35,000 in Cell I8
    01-51500-61111-00000-000 = 100,000 * 50% = 50,000 in Cell I9
    01-51600-61111-00000-000 = 100,000 * 5% = 5,000 in Cell I10

    Way over my level of Excel skill. If anyone can assist in this, it would be appreciated. Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    It seems that what you might be trying to do is this:
    For a particular account number in column B, you want to extract the account obj number (e.g. 61117), then look for this account obj number in row 3.
    If you find this, then take the corresponding amount in row4 and multiply it by the percent in column C.

    Is this what you are trying to do???

    See attachment.

    Regards
    zeddy
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello zeddy - Nice work. Awaiting aapke's reply.
    Just curious, in your example, why did Cells K4 thru Q4 and Q2 and Q3 change?

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I had moved the range [b3:q4] sideways to the right, to align above the block in [i7:x13],
    ..then did some other stuff before putting it back where it started. But I didn't check against the original file.

    However, I have put the original numbers back in the attached file.

    He can check my idea by editing the 5-digit red account obj within any of the cells in range [b8:b12], and seeing the result in the block to to the right.

    zeddy
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the quick response. I may not have explained it correctly but here I go again.
    1. We have 24 character account shells that are associated with the position no (column A). Each of the account shells have percentages associated with them. (100 employees all associated with all the object codes but the fund/ddf/proj/cct all vary)
    2. I want to create 11 duplicate shells and percentages for each string. Each of the duplicate shells shall have one of the other 11 object codes inserted into the shell. So say, my initial string is 01-51400-61111-00000-000. I want the 11 more that are created to have the other object codes from Row 3.
    3. Then I want to multiply the percentage assigned to each string with the amount in Row 4.

    Did that even make sense?
    Thanks.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are you looking for something like this in I8?:

    =iferror(VLOOKUP($A8,$A$3:$P$4,MATCH(I$7,$A$3:$P$3 ,0),FALSE),0)*$C8

    Steve

  7. #7
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tried that. It doesn't work. While the sample data is in one table, the account numbers are in another.

    Table 1, has a list of the Position numbers with the amounts that are associated with the Object numbers.

    Table 2, has the Position number, Account number, and percentage. The other data is from me as I attempted various ways to automate this.

    What I am trying to do is, look at the position number on Table 1, get the initial Account number from Table 2, create 11 more Account numbers on Table 2 by grabbing the rest of the Object numbers from Table 1, have them inserted in the same column in Table 2 (next empty row), then perform the calculation of taking the amount associated with the object from Table 1 and multiply it with the percentage on Table 2.

    Getting closer. Thank you!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the example you originally posted is not the setup you are using, it is not surprising that the examples we provide are not appropriate. Could you attach a more representative example of your setup?

    Steve

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    It's still not very clear what you are trying to do.
    Let's see if the attached file helps us to work out what you want.

    The only way to get the required 'account objs' into column B (without macros) is to use formulas.

    In the attached file, I am assuming you get data for rows 3 and 4 from somewhere.
    I have colour coded this in pale green.

    In the Table2 block, I assume you get the data for PCT, FUND, DDF, PROJ, CCT from somewhere.
    I have colour coded this in pale green.

    I have put formulas into column B table 2.

    Is this getting closer???

    zeddy
    Attached Files Attached Files

  10. #10
    Lounger
    Join Date
    Jan 2008
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow, that is incredibly close to what I need! I'm trying again with the attachment. I've created sample data for the attached file that will all look the same but will hopefully work for this incredibly weird thing I need to get done. So in the attachment, I have split up the tables on separate tabs.

    Tab 1/Table 1 can be considered as a replica of what the finance system will dump out related to our positions. So in basic terms, the data I can get out of the system is in Rows 4 to 13. Pretend that the 'positions' in Column A are 10 separate employees. On their timecards, they will all charge to the Payroll Object codes in Row 3. Each object has a total amount that that is budgeted there for a full year for all employees.

    Tab 2/Table 2 is what I have created using other data dumps and pivot tables. The data I will have available to work from is the Position No (column A), Account No (column B), and the percentage per employee (column C) from historical data. For budgeting purposes, I now need to divvy up where to get the money from to pay everyone. In other words, I can get the first Account No and need to automatically create the remainder by inserting the Object codes from Tab 1 (the only change). Once the accounts are created, using the percentage from Column C, I need to go to Tab 1 and calculate the amount by multiplying the percentage in Tab 2/Column C by the amount in Tab 1/Columns C to P. As you can see, this is quite laborious the way I am currently doing (mainly manually), so I was hoping the Excel gurus in this forum had better ideas.
    Attached Files Attached Files

Posting Permissions

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