Copy value in a cell to another cell based on value in a third cell.

[SteveZed]

Hi All,
Apologies for my opening post. Didn't realise that any spaces would be reduced to one, hence the scramble of the table to the left.

I have updated the thread with a worksheet and explanation of what I am trying to achieve.

We Have 3 Columns containing $ amounts, ( A,D and G), and 3 columns containing category numbers, (B, E and H).
Upon entry of a category value (Column B, E and H), results in the $ amount to the left being added to the Categories 1 to 10 block, as shown.
For clarity the $ signs have bee omitted.
All of the above will be in one work sheet.

A solution to this chalange would be greatly appreciated.

Cheers, Steve

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[SteveZed]

Hi All,

As no solution has been offered, I assume I was not clear on what was needed. So I will try again.
Included is a Workbook.

We have 3 banking accounts, ie Acct 1, Acct 2, Acct 3, with a category for each account.
When a category value is entered after the payment, the payment amount is added to the corresponding column in the Categories Table (columns K to T). When a payment or the category is changed, this is reflected in the Categories Table.
So each row in the Categories Table reflects what is in the Payments Accounts for that row.

For example:
Row 4: B4 = 1, add A4 to K4 giving total in K4 = 10
Row 5: E5 = 2, add D5 to L5 =20
Row 6: H6 = 5, add G6 to O6 = 30
Row 7: B7 = 1, E7 = 1, H7 = 1, add 10+20+30 to K7=60

I hope that this illustrates what I am trying to achieve.
I have no knowledge of VBA, so I was hoping this can be done in formulas.

Help for a solution would be greatly appreciated.

Cheers,
Steve

[JohnTopley]

Remove the "divider" columns so data is contiguous

in I4

=SUMPRODUCT(($A4:$E4)*($B4:$F4=I$3))

Copy across and down

[SteveZed]

Hi John,

Thank you so very much for this seemingly simple solution to a problem I thought was far more complex, based on the time I have spent on it.


Cheers,
Steve

[SteveZed]

Hi John,

Is it possible to have blanks in the cells that have zeros, for visual simplicity of the Categories Table?

Cheers,
Steve

[JohnTopley]

CUSTOM Format the cells as 0;;;@

[SteveZed]

Hi John,

The solution "=SUMPRODUCT(($A4:$E4)*($B4:$F4=I$3))" works just fine.
When I add another pair of Payments and Category, in columns G & H, I can't get this to work. I don't seam to grasp what "I3" (tried I4) at the end stands for.

Also have trouble to Custom Format the cells as 0;;;@. Is the procedure to implement this as follows: Select the Categories Table, then click Data in the Menu, in Data tools select Data Validation, Allow Custom, Formula 0;;;$ . The zeros remain.

Thanks for your help to date.

Steve

[JohnTopley]

I$3 is the Category number which changes to J$3,K$3 as the formula is dragged across the cells : starts at K with new formula below.

=SUMPRODUCT(($A4:$G4)*($B4:$H4=K$3))

To format:

Select the range (K4 to T 1 or U11)

Right click

Format cells

Custom

enter 0;;;@

[SteveZed]

Hi John,

Thank you so much again.

Your solutions works beutifuly. Simple solutions to my complex problems.

Steve