News:

And we're back!

Main Menu

Excel hell

Started by viper37, May 06, 2011, 04:38:45 PM

Previous topic - Next topic
Print
Go Down Pages 1 2
User actions

viper37

#15
June 16, 2011, 10:19:02 AM
Got another one...
I want to create an e-mail hyperlink, from a value presented elsewhere in the worksheet.

So I use the function: "=HYPERLINK("mailto:"&"Courriel: "&J6)" where cel J6 contains my e-mail adress.
I actually want the adress to look like "Courriel:  [email protected]".
However, Excel always shows "mailto:" in front of my adress.  How do I get it to disapear?
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

Grey Fox

#16
June 16, 2011, 10:24:12 AM
Quote from: viper37 on June 16, 2011, 10:19:02 AM
Got another one...
I want to create an e-mail hyperlink, from a value presented elsewhere in the worksheet.

So I use the function: "=HYPERLINK("mailto:"&"Courriel: "&J6)" where cel J6 contains my e-mail adress.
I actually want the adress to look like "Courriel:  [email protected]".
However, Excel always shows "mailto:" in front of my adress.  How do I get it to disapear?

Right Click the hyperlink. There's a dialog call "Text to Display".
Colonel Caliga is Awesome.

viper37

#17
June 16, 2011, 10:32:25 AM
There's no such thing when I right-click in Excel 2010.  And the "hyperlink" button in the inster bar is greyed out.
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

Grey Fox

#18
June 16, 2011, 10:36:19 AM
Quote from: viper37 on June 16, 2011, 10:32:25 AM
There's no such thing when I right-click in Excel 2010.  And the "hyperlink" button in the inster bar is greyed out.

Is there an Edit Hyperlink option?

My Excel 2010 is currently unavaible so can't check.
Colonel Caliga is Awesome.

viper37

#19
June 16, 2011, 10:42:42 AM
Ah, got it.

I had to create the link with the dialog box and change the text in this dialog box.  Once created, I cannot in any way modify the text shows.
And I can't use the formula to create an hyperlink, and if I tried to use ="Courriel: "&J6 it wouldn't create a link.

But now, it works as intended.  Excel is kinda tricky&capricious, but I'm taming the beast.

Thanks :)
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

Grey Fox

#20
June 16, 2011, 10:47:08 AM
No Problem.

You are trying to do crazy shits with it too.

I make nice borders :)
Colonel Caliga is Awesome.

viper37

#21
June 16, 2011, 01:02:45 PM Last Edit: June 16, 2011, 01:06:25 PM by viper37
Quote from: Grey Fox on June 16, 2011, 10:47:08 AM
No Problem.

You are trying to do crazy shits with it too.

I make nice borders :)
another one... I'm displaying text&date.  Got the format right.  However, I'd like Excel to write "Juin" instead of "June" for the month... worst case scenario, I'd leave it in a short date format, but if I could get the long one...

Also, how do I modify part of a text that is inside a formula so it lookes like this?
="Soumission modifiée le "&now" ?
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

Grey Fox

#22
June 16, 2011, 01:34:41 PM
Excel takes it's month from your Windows Regional Settings.
Colonel Caliga is Awesome.

viper37

#23
June 16, 2011, 02:39:19 PM
Quote from: Grey Fox on June 16, 2011, 01:34:41 PM
Excel takes it's month from your Windows Regional Settings.
I've changed it... but I believe I needed it to be set to "English Canada" for one particular piece of software...  Anyway, I'll live with this.
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

viper37

#24
June 17, 2011, 10:53:14 AM
Quote from: viper37 on June 16, 2011, 01:02:45 PM
Also, how do I modify part of a text that is inside a formula so it lookes like this?
="Soumission modifiée le "&now" ?

well, apparently, that can't be done in Excel, not even using VBA. :(
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

viper37

#25
June 20, 2011, 11:35:52 AM
I'm trying to figure something that won't involve VBA...

So, I've got one of my worksheets that's used for the indexation of the projects.
All projects are listed there, with basic info (project #, project name, project owner, city, type, size, prices (3 columns)).

In Quattro Pro, I used to have a macro that created the index line when I created the project.  It basically copied the info from my dialog box to the relevant cells.
Now, I wanted to try something different. 
Let's say my macro will eventually create the project # and name in the first two columns, and from there, the rest will automatically adjust according to the project #.

Example:
Sheet: Index
Project #: F12705

So, Index:A4 contains "F12705".  Index:B4 contains "project name" as an hyperlink to that sheet, also named "F12705".  Eventually, I'll learn enough about VBA to do that automatically, as it was done in QP.

Now, if I wanted to have cell Index:C4 contain F12705:D1 where "F12705" is always the content of cell Index:A4 and D1 is always fixed, how would I do that?
I tried =A4&"D1", but that's not working.  tried converting the value to text first, then merging it with "D1", but it ain't working either.
Any ideas or am I asking the impossible?
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

ulmont

#26
June 20, 2011, 01:35:30 PM
Quote from: viper37 on June 20, 2011, 11:35:52 AM
Example:
Sheet: Index
Project #: F12705

So, Index:A4 contains "F12705".  Index:B4 contains "project name" as an hyperlink to that sheet, also named "F12705".  Eventually, I'll learn enough about VBA to do that automatically, as it was done in QP.

Now, if I wanted to have cell Index:C4 contain F12705:D1 where "F12705" is always the content of cell Index:A4 and D1 is always fixed, how would I do that?
I tried =A4&"D1", but that's not working.  tried converting the value to text first, then merging it with "D1", but it ain't working either.
Any ideas or am I asking the impossible?

You want to use INDIRECT("F12705!D1").  So, based on your sheet, INDIRECT(A4&"!D1") works.

viper37

#27
June 20, 2011, 01:46:14 PM
that's the ! i keep forgetting...
many thanks :)
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.

ulmont

#28
June 20, 2011, 01:51:07 PM
Quote from: viper37 on June 20, 2011, 01:46:14 PM
that's the ! i keep forgetting...
many thanks :)

The INDIRECT is also pretty important.  =A4&"!D1" isn't going to work, but =INDIRECT(A4&"!D1") will.

viper37

#29
June 21, 2011, 08:17:43 AM
Quote from: ulmont on June 20, 2011, 01:51:07 PM
Quote from: viper37 on June 20, 2011, 01:46:14 PM
that's the ! i keep forgetting...
many thanks :)

The INDIRECT is also pretty important.  =A4&"!D1" isn't going to work, but =INDIRECT(A4&"!D1") will.
I had the INDIRECT part, I found the function after posting :)
I don't do meditation.  I drink alcohol to relax, like normal people.

If Microsoft Excel decided to stop working overnight, the world would practically end.
Print
Go Up Pages 1 2
User actions