# Thread: Formula calculation automation by value insertion

1. ## 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.

2. 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

3. 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. 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

5. 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. 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. 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. 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. 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

10. 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.

#### Posting Permissions

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