Excel 2007 cell counting

[midnightsaga]

I am trying to come up with either a formula or sql statement that will take a cell, for the sake of the question I'll use "B2", and then count every cell to the left of it. It doesn't matter if the cells to the left are populated or not. Once the cells have been counted, I would like another cell, "B3" to display the results.


Thanks

 

[SkipVought]

Hi,

"It doesn't matter if the cells to the left are populated or not"

So you want to count way out to column ZZZ or whatever the last column is in 2007? What is the reason dor this count?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

then count every cell to the left of it. It doesn't matter if the cells to the left are populated or not."

What are you trying to do, it would seem to me that this is not the best way to do what you are trying to achieve...


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[midnightsaga]

Essentially, I have a range a1:ab1 and I want to count\display the number of cells to the left of any selected cell in the range. For example, if I have some specified data, like EAB, in AA1, then I want cell B1 to display 26. Meaning that it took 26 cells to get to the cell that has the significant data. From that point I have another worksheet that states that a cell A3 =Sheet1!B1

 

[SkipVought]

Sorry for mixing by LEFT and RIGHT.

If you have AA1 selected, there is no Excel Built-In feature that would return a value to another cell, like B1.

That can only be done with VBA code, using the WorkSheet Selection Change event.

BTW, the VALUE is simply ...
[tt]
=COLUMN(AA1)-1
[/tt]
but there's no spreadsheet feature to get that value into B1.

Why do you need to do this? If I understand what your PURPOSE is, there may be another approch.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

To be more specific:

1. Why do you want to know the number of fields, even if they are empty?

2. What exactly do you do with that number on the other sheet?

3. Instead of using examples, tell us specificaly about your project, what are you doing and why?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.