Excel 2007 : Freezing result

[ALEZI]

Hello guys,

I have a little problem into my Excel table (please look up attached file), there are a simple formulas, my wish is: After calculations freezing numbers in column C and deleting numbers in column B.

Thank you in advance are never disappointed me.

[Marcol]

Have you tried

1/. Select ColumnC
2/. Copy
3/. PasteSpecial > Values
Then
4/. Clear ColumnB

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[ALEZI]

Thanks you for solution and this particularly work but we are loosed formulas which calculate relation between column B and C, when C column would be freeze, for me is problem when I next time entrance ,when I need to subtract new value in column B from remains in column C(first case).

Thanks

[Marcol]

I'm sorry I don't understand what you mean by "freeze."

Do you just need to protect the values so that no unauthorised user can change them?

[ALEZI]

Maybe I was little confused when explain my problem, I need to after calculation (subtract value in column B from value in column C) save result in column C, next time when I want to again put number in column B I have to delete numbers from previous enter but result have to remain and I will again doing calculation column B and C.I using this for control remains from orders.

[Marcol]

Try this and see if it is what you are after.

Please Login or Register  to view this content.

Enter your quantities beginning in B3 then down In C2 where I have date try a real date or whatever else you might want.

Click on the orange cells

Your formula remains in column C

I have attached a demo workbook.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[ALEZI]

Thanks Marcol, you are very close, actually I don’t want new column after saving, only hold new condition in column C up to next entries and then again subtract number in column B with column C and again will be freeze in column C new condition when I push bottom save and reset.

[Marcol]

I'm still a bit unsure as to what you want,

Are you looking for a running result?
Therefore you will know what you have left in stock?

Could you post a worksheet showing what you want after running once
Then show what you expect after running a second, and third time

[Marcol]

Try this

This will keep a running total for stock issued and the remaining stock, as orders are entered.

Please Login or Register  to view this content.

I have put this in the workbook module for sheet2

[EDIT]
1/. Code amended to work with protected sheet
2/. Conditional Formatting added
3/. Attachment Updated


Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[ALEZI]

Marcol, thank you for your time which you spent to help me, basically I looking to a way following my order, because I never got all quantities which was ordered and I have to know how much remains from order, whole quantities will arrived after two- three times, in question here only sheet, one table, like a part of more information about orders. I attempted to show you in attached file.

[Marcol]

Try this these variations in the attached sheet.

Sheet2 is an adaption of Post #9
Sheet3 has no formulae and therefore dosn't need extra columns

From what I can gather I would go with Sheet2


Hope this helps.

If this is not what you need, post a sample of the sheet showing the rest of the table and where it is relative to other data on the sheet.

The actual sheet would be best, provided it has no confidential data.

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[ALEZI]

Thanks Marcol,
You are very close, sheet 2 like solution looks nice and work perfectly but I need to separate those columns and put them into one large table (see attached file), I will colored where I want to put specific column, function column will be same only position of column would be change.
I will explain shortly what was I think, when I make new order in sheet” order form” ordered quantities will be automatically moved in sheet “master file” column E (ordered quantities) and with this quantities we will be continue calculated,”came from order” in column H,”remains from order” in column J and finally “received to date “and “comments” in columns L and M.
At the end, what we do have to do, assembling your sheet 2 whit my table, calculation remain same like dummy sheet.

Thanks again...

[ALEZI]

Marcol,
Please could you tell me are possible to combine those operations as I wanted (see attached file), if not, please show me what I can do.
I will give you one shortly explanation what I want to do, when I was make new material order and he would be saved (sheet Order form) , automatically values from order should be separated in second sheet (Master profile) per columns ,depending of character (length, bars…),now I want to your column E “ordered quantities” include like column for ordered bars, function column “came from orders” still same only would be put in column H, rest of table have function as had, only position per column is different.
Thanks in advance.

[Marcol]

It would help me if I knew what kind of product we are talking about.

1/. Where do we get "Colour" from?

2/. What does "Bars" stand for?

3/. What is the relationship of "Length" to "Weight(kg)"? Possibly kg/m ?

If you could explain, I will have a solution this afternoon.

Cheers

[ALEZI]

Marcol,
I will try to give you answers,
1. We will getting *color* from *order form* sheet, cell which have name material description(see example),after I will save this changes those date would be automatically transfer to sheet 2 (master file).
2. Bars represent plastic stripes; usually we will order thousands those stripes.
3. Information about weight is not important, when I have information about bars I will simple calculate weight of bars.



What is important for me, as I show in my example calculation remains bars according to ordered quantities, now when I make new order in sheet “order form” (as I show) after saving this automatically remove to table for calculation remains, when we received some quantities from this order (not full order) a will put in table, automatically those quantities (received) will be subtract from quantities which are ordered. I had been tried to incorporate table from your previous post (sheet 2) because it work fine (originally version-your) but when I was assemble with my document (rest of the table) it does not work. My question is could you do involve your table (sheet 2) in my old form and connected all and make making my remains list from order simply.
I again attached document, where I was put fresh order and one example how I make order (sheet “order form”).

[Marcol]

Okay, I think I've got it now.

Try this attached workbook

I've adjusted my code to suit your layout and put a few changes in yours, I've added comments to help explain.

Try adding "ABC/Red/Blue" to the Material/Description field and check if the response is correct.

Also I have a thing about CommandButtons on a worksheet so I've removed them and triggered the macros using the Selection_Change Event for the respective sheets.

CommandButtons have problems with workbook references when they are not properly referenced, especially when the workbook is saved as something else.

This method avoids that problem.

It's maybe just me, but I find this method more reliable.

Finally I've added a default value of todays date if this field is empty, just write over it if you need any other order date, it will only restore if the cell is left empty.


Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[ALEZI]

Thanks Marcol,

That works perfectly, after a lot attempts we got results, I’m so appreciative for your effort.

Thanks

[Marcol]

Happy to have helped.

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[ALEZI]

Hi Marcol again,

I have been hurry up a little when I said that everything ok but when I start to work I founded problem, what is happened, on the masterfile sheet I have about 12 orders, which ordered in different times in the past, with different quantities in column “balance of order”, when some of them would be came I’m going to put those quantities in column “Delivered” and after that I will be push bottom “calculate balance”, problem is way all quantities which are on the list change state in column “Balance of order” not only rows where material arrived, that means I will be losing data about remains quantities from previous deliveries , could you set up that when I push bottom “calculate balance” calculating only rows where I have numbers in “delivery” column (see attached example).

Example represents how it should be work in three steps; I hope that I’m not so boring

[Marcol]

Hi again ALEZI

Try this amended workbook

The code now checks for missing or invalid data, and will bar progress until any error is corrected.

an example is on the test sheet
(+)1120 is a string value, the programme will not let you proceed until it is corrected

Hope this helps

Regards
Alistair