Just a simple question...

[chazrab]

...can you put a "ghost"number in a cell and not have it counted when adding values in a column that includes that cell? When vb code for Textbox1 on a form executes for that cell, the "ghost" number will be replaced by the value of Textbox1. The "ghost" number can be any number - it's just a reminder number for users to reference when entering a value for Textbox1.

Any help on this out there? - this should be very easy - just can't think of how to do this.
PS I know what comments are. Don't want that. I want to "see" a number - visible to users, but not visible to Excel, maybe would another explanation.

Thanks for anyone's help.

CR

 

[lokiluke]

if i get you right, you have a text box that is going to have a default value in it, just to remind the user to make an entry? two options i can think of:

have the value that the user enters passed to the .tag property of the textbox then insert that value into the spreadsheet.

or just test if the textbox is blank before it's value is inserted, if it is blank, get the user to enter a value before the code continues.

would offer more help but my lunch break is almost over :D

 

[chazrab]

no - Textbox1 values come later and there is no default in the textbox. The ghost value is already in the cell. The form I mentioned is just a vehicle for users to enter values - any values they want in the cell containing a ghost value. The ghost value is replaced by the textbox value when the code for the textbox executes. Below:

Cell A1 starts out with a blank value. A user manually enters 1 in cell A1. Here's what I need to have happen:

In Cell A1 a ghost value = 1
In Cell A2 a normal value = 2
In Cell A3 a normal value = 3
=Sum(a1:a3) = 5, which is what I want - not 6, yet 1 still appears in A1.

Again, by "ghost" I just mean it's visible to users but not "visible" or "recognized", or "counted" in the =SUM formula by Excel. It's a dummy value to use as a reminder.

Now, a form with Texbox1 and an OK button pops up on the screen prompting a user to enter a value in a Textbox1 The user enters a value in Textbox1 of say, 60 and presses the OK button. When that happens, the vb code puts the value of 60 in cell A1. The value in A1 is now 60 and the ghost number 1 is replaced. Then

=Sum(a1:a3) = 65

Note: the ghost value can only be entered ONE time and only in a blank cell. The replacement value from the code is the permanent real value counted in the SUM formula.
This is a projected future BUDGET spreadsheet. Ghost values will be entered and subsequently replaced by real values at times in the future which have not occured yet.

Please help if you can.

CR

That is as simple as I can make it.

 

[HalfAce]

How about making your original formula be:

=Sum(a2:a3)

Then, in the code for TextBox1, when you have it input it's value to A1 have it simply amend the formula at the same time to be =Sum(a1:a3)

That sound like it would work?

 

[Saagar]

Alternatively, if the ghost values are entered with a single quote as a prefix, excel forces the number to be text and would be excluded in the sum.

Later when the Textbox1 updates the ghost value with an actual value (as a number), that will be included in the sum. Hope this helps.