if function. Value if true. Do nothing. How?

[markDuffy]

Hi Guys

I am entering a column. I want to use the"if" function. For my "value if true" I want to leave the contents as they are. I am sure there is a solution for his but can’t find it.

Please Login or Register  to view this content.

Mark

[Jim885]

Assuming this is a cell formula you want, try this;
=If(P74="petty cash","petty cash",0)

The formula is designed to return the word PETTY CASH if P74 contains the word PETTY CASH, otherwise the formula will return a vlaue of zero.

If you want the formula to go the other way, then try this;
=If(P74="petty cash","","Petty cash")

[sktneer]

You can use the code as below.....

Please Login or Register  to view this content.

Hope that gives you an idea.

[markDuffy]

Hi Sktneer

Well the expression "do nothing" actually exists. I think what you have is my answer. I currently have two options in the active cell which I want to leave only adding data to the others. It is up to my limited experience to see if I can incorporate it and mark the thread solved. . Thanks

Hi Jim885
Thanks for your suggestion. under other circumstances it would be great. I think that perhaps i did not w=explain my needs carefully enough. Thanks

[markDuffy]

Hi Sktneer

I have been fiddling with your code. It works fine but not quite as I wanted. I am looking at column “P” to see if the contents are “Petty cash”. If they are then I want to leave the original content in Column "B" if they are not then I want to enter Petty cash. The logic being that I am creating another account called Petty cash. Originally the contents of “B” came from another sheet.

I am enclosing the sheet cut from the larger workbook. I have entered a list of possible payees under company and just pasted in their categories. You will see that already in B are the Business and personal.

Sheet 1 is What I have
Sheet is what I want to achieve

Mark

[Solus Rankin]

I'm a little confused. On the sheet "WhatIWant" it seems like you've just added petty cash to a bunch of empty cells at the end.

[markDuffy]

Yes I have – it is mimicking how they might be entered as they arrive. In fact they have come from my control sheet. If the petty cash were to be entered it would be the date and the company name. with all the other stuff being automatic. I intend to create a “Dynamic data validation” for the categories. Hope that’s the correct name.

[markDuffy]

OH Hi Solus had not realized it was you.
Mark

[sktneer]

I am little confused about what do you want exactly.

Do you mean, if there is petty cash in col. P in any row, you don't want to change the value of col. B in that row and if there is not petty cash in col. P, you want to put petty cash in col. B in that row?

Please explain that how your two sheets differ from each other i.e. what changes do you have in "What I want to achieve" sheet and why?

[markDuffy]

Hi Guys

I am terribly sorry that I have clearly not explained what I am doing. I so much appreciate the help I am being given.
I am building an accounting system as an exercise _ Yes I could but it for a few quid.
I have now uploaded where I am. Have removed a couple of screens to reduce size.
All the data has been altered so that none of the data here is real data.
Raw data is downloaded into the OriginalBSCombined.
ControlSheet contains all the tables.
A Macro converts takes the data from the OriginalBSCombined to BDC2 and converts it into my format. MainMacroOne Within this are the individual macros used.
From BDC2 sheet a VAT sheet is created.
From BDC2 sheet a Petty cash sheet is created. This is the sheet I am on now. I want to create a petty cash account where the income is as categorized in BCD2 and the Expenses are in the Petty cash sheet.
In all the sheets I am using the same format.
So now in the petty cash sheet I have all the income but the expenses are not part of the bank accounts but a separate petty cash account. So I want to ensure that all expenses in that sheet are Petty cash and I want that in the same column as the bank name. The petty cash entries will be typed in – Date; Company Name in most cases as the company will be in the CompanyToCategory table. I am working on a Dynamic Validation system to allow for new names which as entered will automatically add themselves to the CategortToComapny table.

Again I hope this makes sense I am sometimes criticized for putting in too much information. I am sure that a lot of the code is as clunky as hell but at a later stage as my experience improves I can shorten it. This would not have been possible for me without the assistance from this site.

Mark