Worksheet Cell Reference

[VectorGeek]

Is it possible to have a worksheet formula reference a cell? For example, if my formula is this:

=COUNT((((S='Unit A1') & ('Unit Disposition'.'Building'='2'))))

 

Would it be possible to have a cell with value of "2" (let's pretend that is cell G45 for argument's sake). I'd like to reference that cell rather than typing in the string in each formula. Something like:

 

=COUNT((((S='Unit A1') & ('Unit Disposition'.'Building'=G45)))

Doesn't seem to work though. I know there is a worksheet function called value(), but it doesn't seem to do anything either.

 

Any help would be appreciated.

 

V-G

Chooglin' since 1969.

[Pat Stanford]

What you show about should work. With a caveat.

 

2 can be a number, or it can be a String containing the ascii character for the number 2.  Those are not the same thing and your Equals test will not match them.

 

Values stored in records are always strings, so if the value in 'Unit Disposition'.'Building' is returning 2 then it is a string.

 

If you type just 2 into a cell by default VW will consider it a number.

 

Two possible work arounds:  

 

1. In cell G45 instead of just entering 2 enter '2'.  This single quotes around the number should force it to be a string.

 

2. Change the end of the formula from =G45 to =Concat(G45).  Concat concatenates items together to make a single string. It also happens to convert numbers to strings.

 

If neither of those work for you ask again. And probably post or DM the file so we can figure out exactly what is happening.

[VectorGeek]

Hi Pat:

 

I tried both of those ideas, in addition to setting the cell (G45) to be "text" in the formatting options. No luck. I will try and cobble together an example file and post. The main file is 300MB. 🙂

 

V-G

[Pat Stanford]

Let's try it this way.  Here is an example showing that it does work if you get the right string or number values.  Record PTS has two fields, Building (formatted as text) and BuildingNum (formatted as number).

 

The Count using criteria very similar to yours works as soon as you get the value forced to the right form.

 

I did have problems forcing a cell to be a string.  I had to format the cell as text, enter something that I knew would be taken as text and then replace it with the number to have it understood as text. Easiest way to tell is that strings by default are left justified and numbers are right justified.

 

 

TextVSNum.vwx

[VectorGeek]

Pat! You solved it!!!

I was just creating a file to upload and I tried the steps you note. Changed the cell to be "text" format, entered a letter (which the cell infers as true text), then changed the letter to the desired number. Works all day long! The key is tricking the cell with a piece of text first. Weird.

This will be a huge help in our huge area tracking spreadsheets for multi-unit developments.

Thanks for the effort!