Microsoft Excel Expert Pls help.

[RockStar9238]

Hi,

I need help to set the format for excel worksheet. In many years ago that I used to work in the company which has a very good worksheet for cashflow that can control the bank statement but cannot contact them anymore. I need to format it for my current use but do not know how to set the format.
Eg:

Cashflow Bank statement

Balance b/f 555555 33333
receipt 777 x
payment 222 x
payment 333 333 33000

If I release the x then it will appear the figure of 333 appear which mean the cheque presented and it tally my bank statement figure. How to set this format? It can be done but i forgot. Any expert please help. Thank you very much.

 

[davidktw]

Hi,

I need help to set the format for excel worksheet. In many years ago that I used to work in the company which has a very good worksheet for cashflow that can control the bank statement but cannot contact them anymore. I need to format it for my current use but do not know how to set the format.
Eg:

Cashflow                                               Bank statement

Balance  b/f                 555555                    33333
receipt        777                        x
payment              222                x
payment               333                           333 33000

If I release the x then it will appear the figure of 333 appear which mean the cheque presented and it tally my bank statement figure. How to set this format? It can be done but i forgot. Any expert please help. Thank you very much.

What you have shown is MESSY. Can you explain more clearly what you are trying to achieve ? What is "release the x" ? Can you do a screen capture of your excel sheet on what you want to get ? Show us the before and after ? Can't make out which value belongs to which column.

 

[RockStar9238]

Thank you very much for your reply. Actually, I wanted to attach the excel worksheet but I do not know how. Could you teach me please.



QUOTE=davidktw;88144577]What you have shown is MESSY. Can you explain more clearly what you are trying to achieve ? What is "release the x" ? Can you do a screen capture of your excel sheet on what you want to get ? Show us the before and after ? Can't make out which value belongs to which column.[/QUOTE]

 

[davidktw]

Thank you very much for your reply. Actually, I wanted to attach the excel worksheet but I do not know how. Could you teach me please.

Perhaps you would like to upload your file to http://www.megafileupload.com and then paste the link here for download?

 

[RockStar9238]

I go to megafileupload.com but there is fatal error. Is it I use the wrong step of uploading? Is there other way to upload the file? Thank you

Perhaps you would like to upload your file to http://www.megafileupload.com and then paste the link here for download?

 

[davidktw]

I go to megafileupload.com but there is fatal error. Is it I use the wrong step of uploading? Is there other way to upload the file? Thank you

Use dropbox, upload your excel file and then share the link to download.

 

[RockStar9238]

Hi Davaidktw,

Thank you very much . Finally , I manage to copy the link from dropbox. I have learnt something new. Actually, I never use dropbox before. Can you manage to see it from the link.

https://www.dropbox.com/s/5ngr58skkan2rja/download-1409450089739.xlsx?dl=0

 

[stay8899]

Hi,

I need help to set the format for excel worksheet. In many years ago that I used to work in the company which has a very good worksheet for cashflow that can control the bank statement but cannot contact them anymore. I need to format it for my current use but do not know how to set the format.
Eg:

Cashflow Bank statement

Balance b/f 555555 33333
receipt 777 x
payment 222 x
payment 333 333 33000

If I release the x then it will appear the figure of 333 appear which mean the cheque presented and it tally my bank statement figure. How to set this format? It can be done but i forgot. Any expert please help. Thank you very much.

You'll need a simple if formula at DR and CR (Col H and I). See modified xls file.

 

[davidktw]

You'll need a simple if formula at DR and CR (Col H and I). See modified xls file.

To TS:

Guess someone has already given you a solution.

My input is to use =IF(ISBLANK([TEST-CELL]),[VALUE-CELL],) for flexibility. But it all depends on how you want things to be done.

 

[RockStar9238]

To TS:

Guess someone has already given you a solution.

My input is to use =IF(ISBLANK([TEST-CELL]),[VALUE-CELL],) for flexibility. But it all depends on how you want things to be done.

Hi,

I have apply the above formula but it did not work. What is test cell and value cell?

 

[davidktw]

Hi,

I have apply the above formula but it did not work. What is test cell and value cell?

Please don't take it log stock and barrel, TEST-CELL and VALUE-CELL are the excel cells. It is a formula, you need to apply to the respective cells. Refer to the formula found in the earlier post solution for reference.

 

[RockStar9238]

Please don't take it log stock and barrel, TEST-CELL and VALUE-CELL are the excel cells. It is a formula, you need to apply to the respective cells. Refer to the formula found in the earlier post solution for reference.

I open the excel file another person reply but cannot see the formula in the file from dropbox.

 

[peterchan75]

You can try this.
Copy from below and paste into cell H5.
=IF(G5="",D5,0)
Copy from below and paste into cell I5.
=IF(G5="",E5,0)

After that, you copy from row 5 of column H & I and paste to row 6..9.

 

[RockStar9238]

Thank you all very much. It works.