Show values from range > 0 in one cell

  • Thread starter Thread starter Mr. Smith
  • Start date Start date
M

Mr. Smith

Hi

Cell B1:Z1 are either containg a value (char) or is NULL
How could I "cluster" all values from the B1:Z1 in cell A1 and seperate them
with "-".

Psaudo code for formula in A1 someting like this
=IF(B1:Z1>0;<Value>&"-";"")

If some of you understand, please give me a hint.

Mr. Smith
 
Mr Smith,

Try :
IF(B1>0;B1&"-";"") & IF(C1>0;C1&"-";"") & IF(D1>0;D1&"-";"") & .... &
IF(Z1>0;Z1&"-";"")

I'm not sure whether you have characters of numbers (you mention characters,
however you test in your example values).
Maybe you should change the tests in the if functions a bit. The idea is
however the same

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi

Cell B1:Z1 are either containg a value (char) or is NULL
How could I "cluster" all values from the B1:Z1 in cell A1 and seperate them
with "-".

Psaudo code for formula in A1 someting like this
=IF(B1:Z1>0;<Value>&"-";"")

If some of you understand, please give me a hint.

Mr. Smith
Click to expand...

A simple VBA solution:

==============
Function ConcatRange(rg As Range) As String
Dim c As Range

For Each c In rg
If c.Text <> "" Then ConcatRange = ConcatRange & c.Text & "-"
Next c

ConcatRange = Left(ConcatRange, Len(ConcatRange) - 1)

End Function
================

To enter this <alt-F11> opens the VB Editor.

Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the above code into the window that opens.

You can then use ConcatRange as any other function. The argument would be the
range that you wish to concatenate.


--ron
 
Thanx Ron.
Smooth little func.

Anyway I could get the cell where I call the function to be "live". Now I
have to enter and exit the cell to get the content updated.

Thanx again.

Mr. Smith

 
Thanx Ron.
Smooth little func.

Anyway I could get the cell where I call the function to be "live". Now I
have to enter and exit the cell to get the content updated.

Thanx again.
Click to expand...

Gee, if I change the contents of a cell in "rg", the formula updates
automatically for me. It must be something in your environment.

Do you have calculation set to automatic? (Tools/Options/Calculation)




--ron
 
hanx Ron.
Smooth little func.

Anyway I could get the cell where I call the function to be "live". Now I
have to enter and exit the cell to get the content updated.

Thanx again.

Mr. Smith
Click to expand...

Another thought. Perhaps rg has formulas?

If that is the case, then ensuring that this function changes is more
difficult.

It will change when an input cell changes -- but a different formula result
doesn't do that.

By adding application.volatile at the beginning, you can have it calculate when
any calculation is done on the worksheet. However, depending on the order of
the calculations, you may not obtain the result you expect.

You can always hit F9 which forces a recalculation.

You could use an event triggered macro to trigger the recalculation.

Let me know if you need help with one of these ideas.


--ron
 
Very kind of you Ron.

With application.volatile I do get the F9 update working (it did not work
until I added application.volatile in the function). To let you completly
into my caos I'll try to explain the big picture in my workbook.

The range sent into the ConcateRange function is based on a range that
retrives values through an array formula:
{=IF(data_rng=2;B7:CA7;"")}
The cells in the range where the formula spans (dtm_rng) containes either
dates or NULL based on updates in the data_rng done by users.

So actually it's three steps that I want to tailor together.
1. User updates data_rng (marks a cell or cells with an x each cell has a
certain date as column header)
2. Updates in the data_rng range trigg an update in the cell range (dtm_rng)
where the array forumla spans (it looks up the dates where x is set).
3. Finaly a cell calls the ConcatRange function with dtm_rng as parameter,
and showes a "string" of values based on your function.

If you are still there, you might sake your head or lol, but I'm very close
to have it al set. I might settle with a soultion where I have to force
update through F9, but if you would give me a hint to where and how I could
insert an event trigged macro to do the last calculation, I'll be more than
intrested to try it out.

Thanx again Ron.

Mr. Smith

 
I might settle with a soultion where I have to force
update through F9, but if you would give me a hint to where and how I could
insert an event trigged macro to do the last calculation, I'll be more than
intrested to try it out.

Thanx again Ron.

Mr. Smith
Click to expand...

Funny. In trying to set things up so that with Calculation set to automatic, I
could get ConcatRange to NOT calculate, I failed.

So I'm not sure what the circumstances are when one has Calculation set to
Automatic, for this formula to fail. I know it was doing it yesterday here,
but I cannot seem to reproduce that problem. Perhaps someone can jump in and
give some advice in this regard.

In any event, you can detect various types of worksheet change events and
trigger macros. To enter this kind of a routine, right click on the sheet tab
and select View Code from the menu that opens. Then Select Worksheet in the
left hand box above the window, and something appropriate from the right hand
box.

Here you can set up a routine to test certain cells, see if they have been
affected, and, if so, force a recalculation for the ConcatRange function.

For example, if when your user makes an entry in A2:A10 you wish to force the
recalculation of ConcatRange which is in A1, something like this:

========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AOI As Range

Set AOI = [a2:a10]

If Not Intersect(Target, AOI) Is Nothing Then
[A1].Calculate
End If

End Sub
======================

But, as I said, I'm not sure why I can't get ConcatRange to NOT recalculate
today with Calculation set to Automatic (even without Application.Volatile).


--ron
 
Ron.
Again, thanx for comprehensive tribute to my issue.
You know, I call the function in four cells in the same collumn (B2:B5). The
three first are updated automatically but the last one need F9 to retrieve
new values. If I insert a formula calling the function bellow the last cell
(B6), the formula in B5 suddenly starts to update automatically, and B6 are
the "retarted" one.

I don't even want to start trying to figure out why.

Anyway I'm considering the project finished for now, I will distribute a
prototype to my users based on the version I have. Found out that INDIRECT
is a nice little way of generating dynamic range calls and I bundled some of
that into the magic you brought to my workbook.

Thanks again Ron.

Best rgrds
Mr. Smith


Ron Rosenfeld said:
I might settle with a soultion where I have to force
update through F9, but if you would give me a hint to where and how I could
insert an event trigged macro to do the last calculation, I'll be more than
intrested to try it out.

Thanx again Ron.

Mr. Smith
Click to expand...

Funny. In trying to set things up so that with Calculation set to automatic, I
could get ConcatRange to NOT calculate, I failed.

So I'm not sure what the circumstances are when one has Calculation set to
Automatic, for this formula to fail. I know it was doing it yesterday here,
but I cannot seem to reproduce that problem. Perhaps someone can jump in and
give some advice in this regard.

In any event, you can detect various types of worksheet change events and
trigger macros. To enter this kind of a routine, right click on the sheet tab
and select View Code from the menu that opens. Then Select Worksheet in the
left hand box above the window, and something appropriate from the right hand
box.

Here you can set up a routine to test certain cells, see if they have been
affected, and, if so, force a recalculation for the ConcatRange function.

For example, if when your user makes an entry in A2:A10 you wish to force the
recalculation of ConcatRange which is in A1, something like this:

========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AOI As Range

Set AOI = [a2:a10]

If Not Intersect(Target, AOI) Is Nothing Then
[A1].Calculate
End If

End Sub
======================

But, as I said, I'm not sure why I can't get ConcatRange to NOT recalculate
today with Calculation set to Automatic (even without Application.Volatile).


--ron
Click to expand...
Click to expand...
 
You're welcome.

Glad I could be of some help.

--ron


Ron.
Again, thanx for comprehensive tribute to my issue.
You know, I call the function in four cells in the same collumn (B2:B5). The
three first are updated automatically but the last one need F9 to retrieve
new values. If I insert a formula calling the function bellow the last cell
(B6), the formula in B5 suddenly starts to update automatically, and B6 are
the "retarted" one.

I don't even want to start trying to figure out why.

Anyway I'm considering the project finished for now, I will distribute a
prototype to my users based on the version I have. Found out that INDIRECT
is a nice little way of generating dynamic range calls and I bundled some of
that into the magic you brought to my workbook.

Thanks again Ron.

Best rgrds
Mr. Smith


Ron Rosenfeld said:
I might settle with a soultion where I have to force
update through F9, but if you would give me a hint to where and how I could
insert an event trigged macro to do the last calculation, I'll be more than
intrested to try it out.

Thanx again Ron.

Mr. Smith
Click to expand...

Funny. In trying to set things up so that with Calculation set to automatic, I
could get ConcatRange to NOT calculate, I failed.

So I'm not sure what the circumstances are when one has Calculation set to
Automatic, for this formula to fail. I know it was doing it yesterday here,
but I cannot seem to reproduce that problem. Perhaps someone can jump in and
give some advice in this regard.

In any event, you can detect various types of worksheet change events and
trigger macros. To enter this kind of a routine, right click on the sheet tab
and select View Code from the menu that opens. Then Select Worksheet in the
left hand box above the window, and something appropriate from the right hand
box.

Here you can set up a routine to test certain cells, see if they have been
affected, and, if so, force a recalculation for the ConcatRange function.

For example, if when your user makes an entry in A2:A10 you wish to force the
recalculation of ConcatRange which is in A1, something like this:

========================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim AOI As Range

Set AOI = [a2:a10]

If Not Intersect(Target, AOI) Is Nothing Then
[A1].Calculate
End If

End Sub
======================

But, as I said, I'm not sure why I can't get ConcatRange to NOT recalculate
today with Calculation set to Automatic (even without Application.Volatile).


--ron
Click to expand...
Click to expand...

--ron
Click to expand...
 
Back
Top