Forum Home
Press F1
Thread ID: 87759	2008-03-03 03:26:00	Two Excel Formula Puzzles!	GrahamB (750)	Press F1

Post ID	Timestamp	Content	User
645797	2008-03-03 03:26:00	1. Is there a way to interrogate a cell to determine if it is written in Upper Case? Or if, say, the 10th letter is Upper Case? 2. I am trying to create a series of hyperlink tables within a worksheet, so that from a list of 30 items on Sheet1 I can bring up a related range of 30 cells (1 of 30 such ranges, (1 for each cell on Sheet 1) on Sheet2 (A1:A30 – Z1:Z30)). And from a selection in that range link down to a series of ranges in the same columns on Sheets 2 (A32:A61,A63:A92 - Z32:Z61 … etc) to make a visual selection I am able to do this using the Insert Menu, but the problem is that the result is a formula that includes the full file name for the Worksheet. As I want to be able to share this Worksheet with others, who will have different file locations, and additionally because of the large number of links that need to be created, I need a formula that can: a. Be created by adding in details from a prepared list of cell ranges, and b. Use only the Worksheet Sheet Names. I hope that makes sense, and even more, I hope someone can come up with something brilliantly simple! TFYH Regards GrahamB	GrahamB (750)
645798	2008-03-03 05:07:00	Hi Graham To test the 10th character to see if it is upper or lower case, try this formula: =IF(CODE(MID(A1,10,1))>96,"lower","UPPER") Beware that this formula will return a #VALUE error if there is no 10th character. Change the 10 to whichever character you want to test. In this thread (pressf1.pcworld.co.nz) I provided a formula for testing the right-most character. Regarding your second question, when I created a series of hyperlinks within a document and then shifted the location, the hyperlinks updated themselves. Whilst the tooltip might show the location, Excel knows the hyperlinks are within the document itself so I'm not sure if this is a problem. Have you tried moving the document and seeing what happens? Andrew	andrew93 (249)
645799	2008-03-04 21:37:00	Thanks Andrew The formula on Upper and lowerworks works well. I missed that earlier info - on checking the last letter - my email notificatiion had turned off. That formula is especially useful in the circumstances. And yes, I can move the File around so that is taken care of. The problem now is the creation of the links. I can drag a link across a row, or down a column, but the link remains constant, so that does not help. Given that I have got something like 720 (26x30) links to make I do not want to have to do it by hand, and additionally, if I could simply drag them, the displayed text would change and obliterated the list. I could use a separate column just for the hyperlink beside the list, like a clickbox, but until I can copy across or down and have the link move with the drag, I'm back to doing 720 separate insertions. Which is, as they say, a drag! On the offchance I tried a global "Replacement", but the underlying link is not recognised, and it is not a formula. If I write a formula using =Hyperlink() then I can see the resulting cell without having to shift from one sheet to the other, but it will not link to a range and shift on moiseover or click. Any ideas? Regards Graham	GrahamB (750)
645800	2008-03-06 01:51:00	Hi Graham Is there any pattern to where you want the hyperlink to appear and where you want the hyperlink to point to? Or is there some way we can derive the hyperlink? If there is then VBA may be an option otherwise it might be the slow way.... Andrew	andrew93 (249)
645801	2008-03-06 05:32:00	Hi Andrew Yes there is a pattern. All of the second stage is on a single Sheet that I have called "LOOKUP". In every second column A,C,E,G etc through to AY there will be a group of up to 30 rows from Row3:Row32 (30 Rows). There will then be a blank row (33) into which I will put a Return to Top Range (Row3:Row32) Hyperlink. A33 Returns to A3:A32, C33 returns to C3:C32 etc) Below this will be a 30 layers of 30 rows of lookup data which I need to lookup as a 2 x Col range (A34:B63) followed by a Row for the Return to Top Range Hyperlink, and this sequence will continue for a total of 30 lots of 30, with a Return to Top link between each. In each of the Ranges below Row 33 the left hand column (A, C, etc) will contain an alphabetic list and the right hand column (B, D, etc) will contain a reference number. Row 3 links to the first layer (Row34:Row64), Row 4 links to the second layer (Row 65:Row94) etc. I do not know if it complicates the Programme too much, or is even possible, but when an item has been located the left hand column of the selected range, then the number in the right hand column has to be entered into F20 on Sheet [Create List]. This can be done manually if necessary, but if a click on the number in the right hand column can then post that number to F20 on Sheet [Create List] then that would simplify my process even more. I do appreciate your help on this, but if it is too much of a challenge then I will understand I have created a spreadsheet that has calculated the rows in each layer. I have pasted a ‘values’ copy of this chart here. If it doesn’t come through the PressF1 system, or if you want a working copy, contact me [edited out personal email addy] - contact via PM Best regards Graham A3 Links to Range A34:B63 A64 Hyperlink to A3:A32 A4 Links to Range A65:B94 A95 Hyperlink to A3:A32 A5 Links to Range A96:B125 A126 Hyperlink to A3:A32 A6 Links to Range A127:B156 A157 Hyperlink to A3:A32 A7 Links to Range A158:B187 A188 Hyperlink to A3:A32 A8 Links to Range A189:B218 A219 Hyperlink to A3:A32 A9 Links to Range A220:B249 A250 Hyperlink to A3:A32 A10 Links to Range A251:B280 A281 Hyperlink to A3:A32 A11 Links to Range A282:B311 A312 Hyperlink to A3:A32 A12 Links to Range A313:B342 A343 Hyperlink to A3:A32 A13 Links to Range A344:B373 A374 Hyperlink to A3;A32 A14 Links to Range A375:B404 A405 Hyperlink to A3:A32 A15 Links to Range A406:B435 A436 Hyperlink to A3:A32 A16 Links to Range A437:B466 A467 Hyperlink to A3:A32 A17 Links to Range A468:B497 A498 Hyperlink to A3:A32 A18 Links to Range A499:B528 A529 Hyperlink to A3:A32 A19 Links to Range A530:B559 A560 Hyperlink to A3:A32 A20 Links to Range A561:B590 A591 Hyperlink to A3:A32 A21 Links to Range A592:B621 A622 Hyperlink to A3:A32 A22 Links to Range A623:B652 A653 Hyperlink to A3:A32 A23 Links to Range A654:B683 A684 Hyperlink to A3:A32 A24 Links to Range A685:B714 A715 Hyperlink to A3:A32 A25 Links to Range A716:B745 A746 Hyperlink to A3:A32 A26 Links to Range A747:B776 A777 Hyperlink to A3:A32 A27 Links to Range A778:B807 A808 Hyperlink to A3:A32 A28 Links to Range A809:B838 A839 Hyperlink to A3:A32 A29 Links to Range A840:B869 A870 Hyperlink to A3:A32 A30 Links to Range A871:B900 A901 Hyperlink to A3:A32 A31 Links to Range A902:B931 A932 Hyperlink to A3:A32 A32 Links to Range A933:B962 A963 Hyperlink to A3:A32 A33 Hyperlink to A3:A32 Next Column Group C	GrahamB (750)
645802	2008-03-06 09:58:00	Hey Graham, just be a tad careful posting your email address publicly like this as its probably going to be harvested by spam bots. Can a mod please remove it?	beeswax34 (63)
645803	2008-03-11 08:32:00	Hi Graham Apologies for not getting back to you sooner. I'm struggling to understand what you are trying to achieve (as they say a picture is worth a thousand words!) but, unless I'm mistaken, it sounds like you want to retrieve certain values based on certain input - is that correct? If so, have you looked at the Vlookup function? If that doesn't quite give you what you want then there are plenty of options with functions like Index and Match. Can you post a screen-shot of what you are trying to do? Or maybe e-mail me a sample? Andrew	andrew93 (249)
1