change values in other set of cells based on cell value in principal cell

**fuwad98** · 06-27-2020, 05:27 PM

Hello !

I am looking forward to getting some help with creating a spreadsheet dealing with money borrowing and repayments. As of now I just need to create a spreadsheet whereby I can just write the amount of cash my friend(s) have borrowed.

Suppose I use cell A1 to drop down list of my friend's names , for instance [A,B,C,D].

Let C4="Money Borrowed"

I want to write the amount of money borrowed in cells C5 and and down such as C5:C20. However, I want the values in the cells C5:C20 to be specific to each person, based on cell A1.

For eg, if A1="B"

C5= 2.5$, C6= 5$, C7 = 8$.

Then when I want to write down for money I lent to my friend "C". Let A1="C", I want the values in cells C5:C20 to be gone so that I can write down for friend "C". I want the values in cells C5:C20 to be saved under each friend depending on cell A1 value.

How may I do this?

eg 1.JPG
eg2.JPG

**GeoffW283** · 06-28-2020, 01:01 AM

Here's a slightly different approach for your consideration.

fuwad98.png

Note the down arrows on the row-1 columns. This is an "autofilter".

To see friend "A"'s entries, click on the cell A1 down arrow and select "A". You will now see the 5 "A" transactions. Select "B" in the autofilter and you will see just friend "B"'s transactions.

When you (hopefully) get a repayment you can enter the column-C amount as a negative value.

Cell C12 is a balance. If you filter on column-A for, say, friend "A" the balance will automatically adjust to reflect just the transactions related to friend "A". The formula in that cell is: =SUBTOTAL(9,C2:C11)

The attached workbook implements the above.

Hope this helps!