3 challenges; concequences of activating cell + 2 text formulaproblems

[Snoopy]

Hey guys - sorry I slipped on the send-button....
I have some minor issues on my board:
1) Are there any way to delete the cell value in range A1 when
activating cell/range A2?
- wanted effect is when user activates cell A2 (by click) the value /
content inn cell A1 will be deleted.
2) When constructing coordinates as textstrings (X Y Z) by linking
values from range B1, B2 and B3 ( the text string will not appear
whith 1 decimal format in all positions - not where the value is an
integer. Ex: B1=1 B2=2.5 B3=3.4 the string shows: "1 2.5 3.4", but I
want it to show: "1.0 2.5 3.4"
Is this possible?
3) I have this set of formulas in Sheet1 that referes to Sheet2. ex:
=look.up(A1;Sheet2!C:CSheet2!;D;D)
I want the formulas to refere to a given value (=sheetnn) in a cell
(A1) in Sheet1 to "switch" reference from Sheet1 to sheetnn in the
given formula.

You've been supportive so far and I hope you will help again
I use Excel/Office 2007

Regards
Snoopy

 

[ShaneDevenshire]

Hi,

Issue 1:
You will need to use vba

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Range("A22"), Target)
If Not isect Is Nothing Then
Target.Offset(-1, 0).ClearContents
End If
End Sub

In this case you need to add this to the Sheet object. The range A22 is
just the range where if you click or select any of these cells the code
clears the contents from the cell above.

Issuue 2:

=TEXT(B1,"#.0")&" "&TEXT(B2,"#.0")&" "&TEXT(B3,"#.0")

Issue 3:

Your question is unclear to me. But you should look at the INDIRECT
function and you need to clean up the formula you supplied:

=look.up(A1;Sheet2!C:CSheet2!;D;D)

=LOOKUP(A1,Sheet2!C:C,Sheet2!D)

Watch your commas, semi-colons and colons and periods.

 

[Snoopy]

Hi,

Issue 1:
You will need to use vba

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim isect As Range
    Set isect = Application.Intersect(Range("A22"), Target)
    If Not isect Is Nothing Then
        Target.Offset(-1, 0).ClearContents
    End If
End Sub

In this case you need to add this to the Sheet object.  The range A22is
just the range where if you click or select any of these cells the code
clears the contents from the cell above.

Issuue 2:

=TEXT(B1,"#.0")&" "&TEXT(B2,"#.0")&" "&TEXT(B3,"#.0")

Issue 3:

Your question is unclear to me.  But you should look at the INDIRECT
function and you need to clean up the formula you supplied:

=look.up(A1;Sheet2!C:CSheet2!;D;D)

=LOOKUP(A1,Sheet2!C:C,Sheet2!D)

Watch your commas, semi-colons and colons and periods.

--
Thanks,
Shane Devenshire







– Vis sitert tekst –

Thanks Shane
This was very useful!
Regards
Snoopy