Counting cells with data in them

[kragster]

Hi,
I am still working on this ordersystem and have a couple of questions in this regard.

1. First of all my Excell seems to have shifted language all of a sudden. All the excel commands are now in danish which is very annoying, when you have gotten familiar with the english names. Is there any way to change this within excel?

2. I have a cell in my spreadsheet with this command in it:

=TÆLV($B$32:$B$64938)

I cant remember the name of the english version, but what it basicly does is count the cells with value in them in a range. I use this to display the total amount of orders. The problem is, that when a new order is inserted into row 32, TÆLV is changed to this:

=TÆLV($B$33:$B$64939)

which evidently displays the same number of orders, even though a new one has just been typed in. Is there any way to make TÆLV allways count from row 32 regardless of how many new rows are inserted or do I have to make a macro to do this?

 

[Fr33dan]

This question is really related to VBA but to office and you should try looking here for your solution: forum68: Microsoft: Office

 

[SkipVought]

Hi,

"...when a new order is inserted into row 32..."

How is a new order inserted?

How is the FORMULA assigned?

Is there some VBA code, maybe? Could we SEE the code, maybe? Do you think that it might help to show the code that is in operation here? Maybe?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[PHV]

I cant remember the name of the english version
Select the cell then in the debug window (Ctrl+G):
? activecell.formula

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

the name of the function in english that will get around this problem is INDIRECT

However, you have a process issue whereby you are inserting data ABOVE the current data. This is generally bad practice as it causes the kind of issues you are experiencing. To be honest, the best solution would be to not insert new data at the top but at the bottom of your data...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kragster]

Hi,
Well I guess your right about it being bad practice. Right now I have a submit button that copies a row of userinput and inserts it in the top of the table. This architecture has some other issues as well, so maybe I should just change it so that it copies the row to the bottom of the table.

Skip: Sorry about not posting the code, didnt think it would be relevant.

 

[xlbo]

AS a workaround for the issue in hand, you would use something like:

=TÆLV(INDIRECT("$B$32:$B$64938"))

where you would need to substitute INDIRECT for the appropriate Danish command


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[kragster]

Exactly what was looking for. Thanks!