Hyperlink via indirect cell reference

Hi

I have workbook that contains a number of sheets. On a separate sheet I 
would like to be able to insert a hyperlink so that I can jump to a specific 
sheet.

However, rather than inserting all of the hyperlinks manually (I will have 
to replicate this over many workbooks) I wondered if there was a formula to 
allow me to jump to a cell (say A1) in another worksheet, based on the name 
of that worksheet being entered in a cell reference.

For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
"Sheet4".

In another sheet I would have 

     A
1 Sheet1
2 Sheet2
3 Sheet3
4 Sheet4

then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
A4.


Thanks


PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 12:19:01 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
4264 Views

Similar Articles

[PageSpeed] 27

Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
of the small arrows to the left of all the Tabs and select the needed sheet ?
To the best of my knowledge - the other way involves Macros. 
Micky



"BabyMc" wrote:

> Hi
> 
> I have workbook that contains a number of sheets. On a separate sheet I 
> would like to be able to insert a hyperlink so that I can jump to a specific 
> sheet.
> 
> However, rather than inserting all of the hyperlinks manually (I will have 
> to replicate this over many workbooks) I wondered if there was a formula to 
> allow me to jump to a cell (say A1) in another worksheet, based on the name 
> of that worksheet being entered in a cell reference.
> 
> For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> "Sheet4".
> 
> In another sheet I would have 
> 
>      A
> 1 Sheet1
> 2 Sheet2
> 3 Sheet3
> 4 Sheet4
> 
> then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> A4.
> 
> 
> Thanks
> 
> 
> PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 12:43:01 PM
I won't hold it against you - however as I have to do this many times I am 
trying to find a formula.

Thanks

"מיכאל (מיקי) אבידן" wrote:

> Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
> of the small arrows to the left of all the Tabs and select the needed sheet ?
> To the best of my knowledge - the other way involves Macros. 
> Micky
> 
> 
> 
> "BabyMc" wrote:
> 
> > Hi
> > 
> > I have workbook that contains a number of sheets. On a separate sheet I 
> > would like to be able to insert a hyperlink so that I can jump to a specific 
> > sheet.
> > 
> > However, rather than inserting all of the hyperlinks manually (I will have 
> > to replicate this over many workbooks) I wondered if there was a formula to 
> > allow me to jump to a cell (say A1) in another worksheet, based on the name 
> > of that worksheet being entered in a cell reference.
> > 
> > For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> > "Sheet4".
> > 
> > In another sheet I would have 
> > 
> >      A
> > 1 Sheet1
> > 2 Sheet2
> > 3 Sheet3
> > 4 Sheet4
> > 
> > then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> > A4.
> > 
> > 
> > Thanks
> > 
> > 
> > PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 12:52:01 PM
Well..., after another thought and assuming all your sheets begin with 
"Sheet" and with an sequential number beginning with 1 - try this:
In cell A1 - in the other spare sheet - put this formula and copy down as 
much as your sheets count:
=HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW()&"!A1")),"Go to 
Sheet"&ROW())
Micky


"BabyMc" wrote:

> I won't hold it against you - however as I have to do this many times I am 
> trying to find a formula.
> 
> Thanks
> 
> "מיכאל (מיקי) אבידן" wrote:
> 
> > Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
> > of the small arrows to the left of all the Tabs and select the needed sheet ?
> > To the best of my knowledge - the other way involves Macros. 
> > Micky
> > 
> > 
> > 
> > "BabyMc" wrote:
> > 
> > > Hi
> > > 
> > > I have workbook that contains a number of sheets. On a separate sheet I 
> > > would like to be able to insert a hyperlink so that I can jump to a specific 
> > > sheet.
> > > 
> > > However, rather than inserting all of the hyperlinks manually (I will have 
> > > to replicate this over many workbooks) I wondered if there was a formula to 
> > > allow me to jump to a cell (say A1) in another worksheet, based on the name 
> > > of that worksheet being entered in a cell reference.
> > > 
> > > For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> > > "Sheet4".
> > > 
> > > In another sheet I would have 
> > > 
> > >      A
> > > 1 Sheet1
> > > 2 Sheet2
> > > 3 Sheet3
> > > 4 Sheet4
> > > 
> > > then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> > > A4.
> > > 
> > > 
> > > Thanks
> > > 
> > > 
> > > PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 1:29:01 PM
=HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)


"מיכאל (מיקי) אבידן" skrev:

> Well..., after another thought and assuming all your sheets begin with 
> "Sheet" and with an sequential number beginning with 1 - try this:
> In cell A1 - in the other spare sheet - put this formula and copy down as 
> much as your sheets count:
> =HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW()&"!A1")),"Go to 
> Sheet"&ROW())
> Micky
> 
> 
> "BabyMc" wrote:
> 
> > I won't hold it against you - however as I have to do this many times I am 
> > trying to find a formula.
> > 
> > Thanks
> > 
> > "מיכאל (מיקי) אבידן" wrote:
> > 
> > > Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
> > > of the small arrows to the left of all the Tabs and select the needed sheet ?
> > > To the best of my knowledge - the other way involves Macros. 
> > > Micky
> > > 
> > > 
> > > 
> > > "BabyMc" wrote:
> > > 
> > > > Hi
> > > > 
> > > > I have workbook that contains a number of sheets. On a separate sheet I 
> > > > would like to be able to insert a hyperlink so that I can jump to a specific 
> > > > sheet.
> > > > 
> > > > However, rather than inserting all of the hyperlinks manually (I will have 
> > > > to replicate this over many workbooks) I wondered if there was a formula to 
> > > > allow me to jump to a cell (say A1) in another worksheet, based on the name 
> > > > of that worksheet being entered in a cell reference.
> > > > 
> > > > For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> > > > "Sheet4".
> > > > 
> > > > In another sheet I would have 
> > > > 
> > > >      A
> > > > 1 Sheet1
> > > > 2 Sheet2
> > > > 3 Sheet3
> > > > 4 Sheet4
> > > > 
> > > > then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> > > > A4.
> > > > 
> > > > 
> > > > Thanks
> > > > 
> > > > 
> > > > PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 1:35:01 PM
Of course !
My apology for not noticing the existence of the sheets name list in col. A
However - your formula can be shorter:
=HYPERLINK("#"&A1&"!A1","Jump to Sheet: "&A1)
Micky



"excelent" wrote:

> =HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)
> 
> 
> "מיכאל (מיקי) אבידן" skrev:
> 
> > Well..., after another thought and assuming all your sheets begin with 
> > "Sheet" and with an sequential number beginning with 1 - try this:
> > In cell A1 - in the other spare sheet - put this formula and copy down as 
> > much as your sheets count:
> > =HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW()&"!A1")),"Go to 
> > Sheet"&ROW())
> > Micky
> > 
> > 
> > "BabyMc" wrote:
> > 
> > > I won't hold it against you - however as I have to do this many times I am 
> > > trying to find a formula.
> > > 
> > > Thanks
> > > 
> > > "מיכאל (מיקי) אבידן" wrote:
> > > 
> > > > Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
> > > > of the small arrows to the left of all the Tabs and select the needed sheet ?
> > > > To the best of my knowledge - the other way involves Macros. 
> > > > Micky
> > > > 
> > > > 
> > > > 
> > > > "BabyMc" wrote:
> > > > 
> > > > > Hi
> > > > > 
> > > > > I have workbook that contains a number of sheets. On a separate sheet I 
> > > > > would like to be able to insert a hyperlink so that I can jump to a specific 
> > > > > sheet.
> > > > > 
> > > > > However, rather than inserting all of the hyperlinks manually (I will have 
> > > > > to replicate this over many workbooks) I wondered if there was a formula to 
> > > > > allow me to jump to a cell (say A1) in another worksheet, based on the name 
> > > > > of that worksheet being entered in a cell reference.
> > > > > 
> > > > > For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> > > > > "Sheet4".
> > > > > 
> > > > > In another sheet I would have 
> > > > > 
> > > > >      A
> > > > > 1 Sheet1
> > > > > 2 Sheet2
> > > > > 3 Sheet3
> > > > > 4 Sheet4
> > > > > 
> > > > > then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> > > > > A4.
> > > > > 
> > > > > 
> > > > > Thanks
> > > > > 
> > > > > 
> > > > > PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 1:50:08 PM
Thanks for both of your replies. I will try them out following the festive 
break.

Before I do, though, might I trouble to ask that - as my sheets aren't 
actually called Sheet1 etc (they are actually various 6 digit numbers) - 
would this formula still work?

Thanks once again.

"excelent" wrote:

> =HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)
> 
> 
> "מיכאל (מיקי) אבידן" skrev:
> 
> > Well..., after another thought and assuming all your sheets begin with 
> > "Sheet" and with an sequential number beginning with 1 - try this:
> > In cell A1 - in the other spare sheet - put this formula and copy down as 
> > much as your sheets count:
> > =HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW()&"!A1")),"Go to 
> > Sheet"&ROW())
> > Micky
> > 
> > 
> > "BabyMc" wrote:
> > 
> > > I won't hold it against you - however as I have to do this many times I am 
> > > trying to find a formula.
> > > 
> > > Thanks
> > > 
> > > "מיכאל (מיקי) אבידן" wrote:
> > > 
> > > > Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
> > > > of the small arrows to the left of all the Tabs and select the needed sheet ?
> > > > To the best of my knowledge - the other way involves Macros. 
> > > > Micky
> > > > 
> > > > 
> > > > 
> > > > "BabyMc" wrote:
> > > > 
> > > > > Hi
> > > > > 
> > > > > I have workbook that contains a number of sheets. On a separate sheet I 
> > > > > would like to be able to insert a hyperlink so that I can jump to a specific 
> > > > > sheet.
> > > > > 
> > > > > However, rather than inserting all of the hyperlinks manually (I will have 
> > > > > to replicate this over many workbooks) I wondered if there was a formula to 
> > > > > allow me to jump to a cell (say A1) in another worksheet, based on the name 
> > > > > of that worksheet being entered in a cell reference.
> > > > > 
> > > > > For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> > > > > "Sheet4".
> > > > > 
> > > > > In another sheet I would have 
> > > > > 
> > > > >      A
> > > > > 1 Sheet1
> > > > > 2 Sheet2
> > > > > 3 Sheet3
> > > > > 4 Sheet4
> > > > > 
> > > > > then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> > > > > A4.
> > > > > 
> > > > > 
> > > > > Thanks
> > > > > 
> > > > > 
> > > > > PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 1:51:01 PM
Weather you have all sheets name in column A starting cell A1 - you might 
consider  Excelents formula and/or mine [the second].
Micky


"BabyMc" wrote:

> Thanks for both of your replies. I will try them out following the festive 
> break.
> 
> Before I do, though, might I trouble to ask that - as my sheets aren't 
> actually called Sheet1 etc (they are actually various 6 digit numbers) - 
> would this formula still work?
> 
> Thanks once again.
> 
> "excelent" wrote:
> 
> > =HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)
> > 
> > 
> > "מיכאל (מיקי) אבידן" skrev:
> > 
> > > Well..., after another thought and assuming all your sheets begin with 
> > > "Sheet" and with an sequential number beginning with 1 - try this:
> > > In cell A1 - in the other spare sheet - put this formula and copy down as 
> > > much as your sheets count:
> > > =HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW()&"!A1")),"Go to 
> > > Sheet"&ROW())
> > > Micky
> > > 
> > > 
> > > "BabyMc" wrote:
> > > 
> > > > I won't hold it against you - however as I have to do this many times I am 
> > > > trying to find a formula.
> > > > 
> > > > Thanks
> > > > 
> > > > "מיכאל (מיקי) אבידן" wrote:
> > > > 
> > > > > Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
> > > > > of the small arrows to the left of all the Tabs and select the needed sheet ?
> > > > > To the best of my knowledge - the other way involves Macros. 
> > > > > Micky
> > > > > 
> > > > > 
> > > > > 
> > > > > "BabyMc" wrote:
> > > > > 
> > > > > > Hi
> > > > > > 
> > > > > > I have workbook that contains a number of sheets. On a separate sheet I 
> > > > > > would like to be able to insert a hyperlink so that I can jump to a specific 
> > > > > > sheet.
> > > > > > 
> > > > > > However, rather than inserting all of the hyperlinks manually (I will have 
> > > > > > to replicate this over many workbooks) I wondered if there was a formula to 
> > > > > > allow me to jump to a cell (say A1) in another worksheet, based on the name 
> > > > > > of that worksheet being entered in a cell reference.
> > > > > > 
> > > > > > For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> > > > > > "Sheet4".
> > > > > > 
> > > > > > In another sheet I would have 
> > > > > > 
> > > > > >      A
> > > > > > 1 Sheet1
> > > > > > 2 Sheet2
> > > > > > 3 Sheet3
> > > > > > 4 Sheet4
> > > > > > 
> > > > > > then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> > > > > > A4.
> > > > > > 
> > > > > > 
> > > > > > Thanks
> > > > > > 
> > > > > > 
> > > > > > PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 2:01:01 PM
I manged to find a little bit of time to take your formula(s), paste in to my 
workbook and amend it to suit my specific requirements.

It seems to work - brilliant!!!

Thanks both.

"מיכאל (מיקי) אבידן" wrote:

> Of course !
> My apology for not noticing the existence of the sheets name list in col. A
> However - your formula can be shorter:
> =HYPERLINK("#"&A1&"!A1","Jump to Sheet: "&A1)
> Micky
> 
> 
> 
> "excelent" wrote:
> 
> > =HYPERLINK("#"&OFFSET(A1,,,,)&"!A1","Jump to Sheet : "&A1)
> > 
> > 
> > "מיכאל (מיקי) אבידן" skrev:
> > 
> > > Well..., after another thought and assuming all your sheets begin with 
> > > "Sheet" and with an sequential number beginning with 1 - try this:
> > > In cell A1 - in the other spare sheet - put this formula and copy down as 
> > > much as your sheets count:
> > > =HYPERLINK("#"&CELL("address",INDIRECT("Sheet"&ROW()&"!A1")),"Go to 
> > > Sheet"&ROW())
> > > Micky
> > > 
> > > 
> > > "BabyMc" wrote:
> > > 
> > > > I won't hold it against you - however as I have to do this many times I am 
> > > > trying to find a formula.
> > > > 
> > > > Thanks
> > > > 
> > > > "מיכאל (מיקי) אבידן" wrote:
> > > > 
> > > > > Will you hold it against me if I dare to suggest that you RIGHT(!) click one 
> > > > > of the small arrows to the left of all the Tabs and select the needed sheet ?
> > > > > To the best of my knowledge - the other way involves Macros. 
> > > > > Micky
> > > > > 
> > > > > 
> > > > > 
> > > > > "BabyMc" wrote:
> > > > > 
> > > > > > Hi
> > > > > > 
> > > > > > I have workbook that contains a number of sheets. On a separate sheet I 
> > > > > > would like to be able to insert a hyperlink so that I can jump to a specific 
> > > > > > sheet.
> > > > > > 
> > > > > > However, rather than inserting all of the hyperlinks manually (I will have 
> > > > > > to replicate this over many workbooks) I wondered if there was a formula to 
> > > > > > allow me to jump to a cell (say A1) in another worksheet, based on the name 
> > > > > > of that worksheet being entered in a cell reference.
> > > > > > 
> > > > > > For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", 
> > > > > > "Sheet4".
> > > > > > 
> > > > > > In another sheet I would have 
> > > > > > 
> > > > > >      A
> > > > > > 1 Sheet1
> > > > > > 2 Sheet2
> > > > > > 3 Sheet3
> > > > > > 4 Sheet4
> > > > > > 
> > > > > > then in cells B1 to B4 have a formula that jumps to the sheet named in A1 to 
> > > > > > A4.
> > > > > > 
> > > > > > 
> > > > > > Thanks
> > > > > > 
> > > > > > 
> > > > > > PS - I'm not very good with macros so I was hoping for a formula.
0
Utf
12/24/2009 2:04:01 PM
Reply:

Similar Artilces:

In macros, an instruction that returns the position of the Active Cell
In a macro, how can I make it return the position of the Active Cell Colum & Line. Or how can I set that position as a Condition; e.g. �If ActiveCell i B7 Then�.� Or �If ActiveCell belongs to Column G Then�.� Thanks in advance for any help. AC -- ac ----------------------------------------------------------------------- aca's Profile: http://www.msusenet.com/member.php?userid=385 View this thread: http://www.msusenet.com/t-187368645 Hi Sub test() Dim ws1 As Worksheet Set ws1 = Workbooks("Tester.xls").Sheets("Sheet2") With ws1 MsgBox ("Cell address is &qu...

a cell contains both text and number
how do i make a calculation if a cell has text and a number. Example the cell says "3 weeks" I want to calculate 3 weeks to read in the next column as "120 hours" =LEFT(A1,1)*1*40&" hours" Vaya con Dios, Chuck, CABGx3 "text and formulas" <text and formulas@discussions.microsoft.com> wrote in message news:D8C17645-DAFF-455A-B144-5B936B97ADCE@microsoft.com... > how do i make a calculation if a cell has text and a number. Example the cell > says "3 weeks" I want to calculate 3 weeks to read in the next column as "120 &g...

How Can I Make The Cursor go to the last letter of the cell?
I have a paragraph in the cell and I want to know how to make the cursor go to the end of the paragraph without clicking on it or using sendkey {f2}{end}{right} Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ anyone? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I do not think this is possible without substantial coding. Although i...

Abolute hyperlinks in Access
Hello all I have managed to add a command button that will open the hyperlink dialog. When I try to add a hyperlink to a field (type hyperlink) I can choose for: Look in: - current folder - Browsed pages - Recent files (not counting the e-mail stuff) If I browse to a file and select it, the link to that file is a relative link, relative to the current folder. Is there a possibilty to create absolute links?? TIA Peter-Paul Rijswijk, The Netherlands When you browse, start at My Network (for items on your server) or start at My Computer for things on your personal...

Delete Global Customer via Worksheet 51?
RMS V 1.3, soon to be 1.3R w/ hotfix. We have way too many "John Doe" and "_ New Customer" customers in our database. All of our customer's are created as Global. I would like to delete some of these, however we have some closed locations that remain a part of our database. I understand I can issue a Worksheet Style 601: Delete Global Customers, however, under the section "This Worksheet Should Apply to...", all locations are by default, accepted. I do not like to have any unprocessed worksheets out there, so, I do not like using this method. The clos...

VB- If first cell with formula is blank, all cells in column returns blank.
Hi all, I am using ADO to connect to an excel sheet and display the data in vb form. In excel there is a column named "TAT" which has a formula t add two othe cell values to it. If any of the two cells is blank the the TAT col remains blank. If the very first cell in the TAT column has some value then VB showa all cell values in that column. But if the first cell is empty then al cells in tat column are shown blank, even if there are values in othe cells. What's the solution for this? Thank -- Message posted from http://www.ExcelForum.com Hi Of course you can try with some...

Generic row reference in formula
I am using a formula in Excel to sum the values in a row: =SUM(B2:F2) Is there a way to use a generic row reference in a formula? For example, I don't know for sure at run time if the data and formula is going to end up in the second row. What I want is something like this =SUM(B#:F#) where the # sign would indicate the current row containing the formula. Thank you very much. Rick Quatro rickquatro@gmail.com One way: =SUM(INDIRECT("B"&ROW()):INDIRECT("F"&ROW())) -- HTH, RD --------------------------------------------------------------------------- P...

Cell Values
I would like to return the value of another cell(C1) if a cell has a value in it, otherwise I want it to return a value of 0. Example: IF(A1="any value",C1,0) This formula would be written in B1. "Any value" would be numbers or text. Thanks 4 your help!!! Try: =IF(A1<>"",C1,0) or =IF(ISBLANK(A1),0,C1) Good Luck, Mark Graesser "GaryW" <gary.wicker@acadiapolymers.com> wrote in message news:040b01c34721$41fe8d00$a301280a@phx.gbl... > I would like to return the value of another cell(C1) if a > cell has a value in it, otherwise I...

Having Formatting Remain on "Called" cell using IF function
I'm using an IF function to call some other cells, but I want to have the various words color coded, eg ("Certified" is green, "Recertify" is yellow, and "Not Certified" is red). I have the IF function working correctly, but when it "calls" the cell to have it display the appropriate word, the color of each word is not brought along with it...only the text. Is there a way to be able to color code it so that the appropriate color appears? Thanks! -- Zaraf ------------------------------------------------------------------------ Zaraf's Profi...

How to set program to view hyperlink?
When I make a hyperlink in Excel 2000, I cannot change which program opens it. Where is the set file associations setting? I am hyperlinking to jpgs. It opens my paint program (PSP) instead of my clipart viewer (ACDSee). I have disassociated PSP with jpg - jpeg. Associated ACDSee with jpg -jpeg. Set Windows Explorer\Tools\Folder Options\File Types to open jpg - jpeg extensions with ACDSee. This was working, now all a sudden it has changed. Any ideas? Thanks, Carol Hi any chance your Windows settings have been changed?. >-----Original Message----- >When I make a hyperlink in Excel 2...

Some cells do not recognize email address
Some of the ceels in my worksheet recognize email addresses so I can click on them and send an email. Other cells do not. the format is right. How do a get a cell to recognize an email address? Not knowing exactly what you have in those cells, expect they are complete email addresses, what version of Excel, and your options would suggest the following: If you have Excel 2002 (Excel XL) or higher: you can suppress hyperlink generation under the Auto Correct from the Tools menu. Tools (menu), Autocorrect Options, Autoformat as you type (Tab). so you want to do the opposite. Next...

Calculate to another cell as default value
Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "...

How do I follow a hyperlink by just pressing "enter"?
How do I follow a hyperlink by just pressing "enter"? My 2000 version of Excel allowed me to do just that. With the 2003 version of Excel, I have to put my mouse pointer and left-click... much more cumbersome! Is ther a way that I can make the 2003 version of Excell behave more like the 2000 version in this regard? My e-mail address is smallscopier@earthlink.net Why would you expect to get a different answer than you've received before. ...

Last cell in new formula
Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

concatenate cells from a column and paste to a cell
I'm trying to write a macro that will loop through a column and copy the data to a single concatenated string which will be pasted to another cell. The macro should step through the column of data until it reaches the end of the data set or an empty cell. I could name a range if needed. Any help getting me started???? The concatenate( ) function won't work with ranges..., so I assume I'll have to write a macro to loop. This will do it, but you may not be happy with the results since the resulting text string may be longer than Excel allows. This code runs from the cel...

Splitting Data in a cell, left or right of a separator.
I need to split some data in a cell to the right of a separator, which is a colon. eg: "CONSUMABLE:HPC92298X" Would someone be able to point me in the right direction.. many thanks, Ross One way is to use Data > Text to Columns Select the column Click Data > Text to columns (Delimited) Click Next In step 2: Type a colon in the "Other:" box Click Finish -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "ross" <ross_anderson57@hotmail.com> wrote in message news:01AD4975-1539-411E-B5D8-04B569268783@microsoft.c...

In Excel is it possible to hide a row condtional upon a cell val..
In Excel is it possible to hide a row condtional upon a cell value? with a macro yes,or you could format white text and all values would disapear. -- paul remove nospam for email addy! "Louise" wrote: > In Excel is it possible to hide a row condtional upon a cell value? The white text makes the text disappear, but not the entire row or column of cells. With a macro, is it possible to make the cells automatically unhide then if the data becomes relevant again due to changes? "paul" wrote: > with a macro yes,or you could format white text and all values woul...

date and time in 2 cells to create subtraction in 3rd
I have 2 cells that are in the format line of A1: 27/06/2006 9:42:00 pm B1: 7/06/2006 9:52:05 pm Want third column C1: 00:10 I wish to subtract the two cells to get the difference in time i.e 10:00 (only interested in nearest minute) Then a average at the end of the column of the the times in this 3rd column as a result of the difference between the 2 times. How would I do this? I could do it if it was just numbers but once it is in this time/dtae format I am completely lost! Thanks MIke Hi Mike, =MOD(B1,1)-MOD(A1,1) Format as Time You can use the AVERAGE function on the ...

Adjacent cell belong to same merged cell?
How do I compare two adjacent cells to determine if they belong to the same merged cell? Adjacent cells cannot be merged cells. If two or more cells are merged, they become one cell. That is what "merge" means. Perhaps there is another way to phrase the question? Gord Dibben Excel MVP On Fri, 25 Mar 2005 11:11:02 -0800, DrKilbert <DrKilbert@discussions.microsoft.com> wrote: >How do I compare two adjacent cells to determine if they belong to the same >merged cell? Thanks Gord for getting back to me on this. Let me explain in more detail. I have a worksheet t...

Cell Format #7
Hello, I have a column where I would like to format as ##-##-##- ##-##-##. I went to Format > Cells and format it in Custom category. It works fine when I enter values, but when the data starts with a text, it won't work. Any suggestions. When I entered 123456789123, it converts to 12-34-56-78- 91-23. When I entered AU1234567890, it doesn't do anything. I want it to covert to AU-12-34-56-78-90. Any ideas would be helpful. You can only format numbers that way, you could custom format as "AU"-##-##-##-##-## and just enter the numbers 1234567890 -- Regards, ...

locking data/cells so re-sorting a column doesn't re-position my d
I need to re-sort a column, but I want all the row's data associated with that row to stay with the SORT results. -- mtn_leisure mtn_leisure Wrote: > I need to re-sort a column, but I want all the row's data associated > with > that row to stay with the SORT results. > -- > mtn_leisure Try selecting the data in the other columns aswell and then sort the data should then stay together Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&...

is it possible to see an incoming phone call id in an excel cell?.
is it possible to see an incoming phone call id in an excel cell?... i ve caller id display installed on my phone line i can see the caller id on bt's little box also i ve tried few programs that gets caller id using modem info. but all these dont (cant) connect caller id infoin an excel cell which then retrieves info about customer who ownes the number. thanks ray Hi so you want a CTI solution? Never did this with Excel but as so many things it should be possible :-) - First it depends on your telephone system. What telephone solution are you using - If this is a professional telephon...

Hyperlinks, Pop-up Windows & Graphics
I have a question! I've seen websites in the past where you can make sections of a picture where they had both a mouseover pop-up window (to tell you what the section will link you to) AND a hyperlink. SO far I've figured out to turn a section of a picture (the same picture) into either a hyperlink OR a pop-up window but not both. I need the pop-up to come up and then allow an option to go to the link or not but so far, after about 10 hours of trial and error, I haven't figured it out yet. Anyone have any suggestions on how to do this? "ladyimmortal&quo...

what code pulls in contents of a cell in an Excel header/footer?
I want to put a code in a header/footer to pull in the contents of cell a1. I thought it was \A1, but this doesn't work, and I can't find any reference to how to do this. Ronny, To my knowledge, there's no code. But you can paste this into the ThisWorkbook module: Private Sub Workbook_BeforePrint(Cancel As Boolean) Sheets("Sheet1").PageSetup.CenterHeader = Sheets("Sheet1").Range("A1").Value End Sub To do that, get into the VBE (Alt - F11). Double-click the ThisWorkbook item in the Project manager, which should open a window for the code behin...

Merging text strings into a single cell
HELP!!!! I have created 2 separate columns of text data in a large spread sheet (over 600 rows of data). Now I need to merge the 2 columns together so that the text descriptions from each column are combined together into a single column. If the cells were numeric it would not be a problem (i.e. if C3 were the finished result I was looking for cell C3 would read "=A3+B3. Only problem is Excel simply tells me the formula is invalid because it's text data. Merging the cells of each row simply cuts the text off from the 2nd cell. How can this problem be resolved????? Hi you'...