Excel question again.

aliasojo

Got another Excel question if anyone can help?

If for example I have 2 columns, one is for funds due in and one is for received funds...is it possible to have the amount show up automatically in the received column as soon as I note 'paid' in the due in column?

Not sure if that's clear enough.

So column A: headed 'Received' is empty

Column B: headed 'Due In' has a figure of £50 say.

Once I delete the £50 amount and insert 'pd' instead I then usually go to column A to input the £50 in the received column. Is there a way to do this automatically?

stevemcol

aliasojo wrote: »

Got another Excel question if anyone can help?

If for example I have 2 columns, one is for funds due in and one is for received funds...is it possible to have the amount show up automatically in the received column as soon as I note 'paid' in the due in column?

Not sure if that's clear enough.

So column A: headed 'Received' is empty

Column B: headed 'Due In' has a figure of £50 say.

Once I delete the £50 amount and insert 'pd' instead I then usually go to column A to input the £50 in the received column. Is there a way to do this automatically?

The quickest way would be to retain the £50 figure in a third column (say column C). Then in column A you'd have =IF(B1="pd",C1,""). Otherwise you might need a bit of VBA.

KingL

Once I delete the £50 amount and insert 'pd' instead I then usually go to column A to input the £50 in the received column. Is there a way to do this automatically?

No, coz once you've deleted the £50, it's deleted and can't be used for further processing. Get a third column, as above or just cut and paste the entry from the 'Due' to the 'Received' column each time someone pays. Cntl+x; left arrow key; Cntrl+p.

You can forget "pd", since if the amount is in the "received" column, then it has been paid.

aliasojo

Thank you both. Appreciate the help, as ever.

KingL

KingL wrote: »

Cntl+x; left arrow key; Cntrl+p.

sorry - typo, that last one should be Cntrl+v

aliasojo

stevemcol wrote: »

The quickest way would be to retain the £50 figure in a third column (say column C). Then in column A you'd have =IF(B1="pd",C1,""). Otherwise you might need a bit of VBA.

I think I've got my head round that bit....I can get the cell in column A to list the amount here once the cell in column B states 'pd' (see middle image below) but until that happens, it says FALSE (top image). Is there a way to get rid of the word FALSE?

Also can I automate this scenario......

Cell C2 is the sum of cells D2,E2,F2,G2. In this example it's 4 x £50 which give the £200 figure in C2.

Each week when the sum is paid, it gets deleted from the cell so obviously cell C2 reduces accordingly. So after wk 1 £50 would have been paid (and deleted) leaving £150 in the 'due' column etc etc.

What I now want to do (if it's possible) is to have 'pd' (I might not need it but I'd like it) to automatically appear in cell B2 as soon as the amount in cell C2 has reached 0. Then if 'pd' appears automatically then the formula for column A should work automatically thereafter too.

Right now, I can only get the formula in Column A to work if I don't have the sum total being calculated in column C and I manually type in the figure.

I've mocked up an example....

I've quite bad at trying to explain all this as I'm well out of my depth and am trying to pick these things up as I go along. Happy to try to clarify anything if needed.

gb12345

Steve's code should work - can you post up the content of cell A2 in your mock up.

As to your other question put the following in cell B2 =IF(C2=0,"pd","")

aliasojo

gb12345 wrote: »

Steve's code should work - can you post up the content of cell A2 in your mock up.

s to your other question put the following in cell B2 =IF(C2=0,"pd",0)

Cell A2 has this.....=IF(B2="pd",C2)

(JUST SAW THE END " " WERE MISSING -ADDED THOSE AND FALSE NO LONGER APPEARS)

Will try out your code now......(thank you ).....

aliasojo

gb12345 wrote: »

Steve's code should work - can you post up the content of cell A2 in your mock up.

As to your other question put the following in cell B2 =IF(C2=0,"pd","")

Ok...that worked, when I deleted each £50 and the amount in C2 went to 0, B2 showed 'pd'.

BUT.... cell A" then showed 0 too and it should be showing £200?

Edit: Looks like inserting that code into cell B2 cancelled out the previous sum code. Can't I have both in the one cell?

aliasojo

Would it be easier/better to have cell A2 showing the amount the was deleted from cells D2, E2 etc etc?

So whilst cell C2 was decreasing, cell A2 was increasing?

gb12345

The problem you've got is that as you delete the amounts from the week cells you are losing how much was actually due so you've no way of telling how much was paid.