Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on Automating a common function
We liaise with DHS. In our excel sheet we have 2 columns, A and B. A has
names. B has unique 10 digit alpha-numeric case numbers DHS generates, say EAC0516151060. We can check case status at website: "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=EAC0516151060" This way we have 100 names and 100 unique numbers that we enter on Excel sheet on daily basis. Can excel sheet be configured in a way that once I enter the unique case number in column B, the web address with the correct unique case number gets automatically attached to the cell so that I have to just click to visit the external website and see the end result? Thanks Raj |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on Automating a common function
If B1 contains:
EAC0516151060 and A1 contains: James Ravenswood then in C1 enter: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" & B1,A1) This will produce a nice, clickable link. -- Gary''s Student - gsnu200851 "Stumped Non-IT-ian" wrote: We liaise with DHS. In our excel sheet we have 2 columns, A and B. A has names. B has unique 10 digit alpha-numeric case numbers DHS generates, say EAC0516151060. We can check case status at website: "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=EAC0516151060" This way we have 100 names and 100 unique numbers that we enter on Excel sheet on daily basis. Can excel sheet be configured in a way that once I enter the unique case number in column B, the web address with the correct unique case number gets automatically attached to the cell so that I have to just click to visit the external website and see the end result? Thanks Raj |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on Automating a common function
Dear Mr. Gary''s Student - Thank you. Unfortunately it did not work. It says
the formula is not correct. I am using Office Excel 2007. Just FYI we are attempting to use the same hyperlink "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" in all cells but the unique case number EAC0516151060 changes for each cell. "Gary''s Student" wrote: If B1 contains: EAC0516151060 and A1 contains: James Ravenswood then in C1 enter: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" & B1,A1) This will produce a nice, clickable link. -- Gary''s Student - gsnu200851 "Stumped Non-IT-ian" wrote: We liaise with DHS. In our excel sheet we have 2 columns, A and B. A has names. B has unique 10 digit alpha-numeric case numbers DHS generates, say EAC0516151060. We can check case status at website: "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=EAC0516151060" This way we have 100 names and 100 unique numbers that we enter on Excel sheet on daily basis. Can excel sheet be configured in a way that once I enter the unique case number in column B, the web address with the correct unique case number gets automatically attached to the cell so that I have to just click to visit the external website and see the end result? Thanks Raj |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on Automating a common function
It's time to share your formula that you tried.
And explain what is in each of the cells that are used in the formula. I was confused about the 10 character alphanumeric string when you showed this as an example: EAC0516151060 That's 13 characters. And make sure you don't have an existing "Insert|Hyperlink" style hyperlink in that cell. Select the cell Ctrl-k (or insert|hyperlink) and remove any link you see Personally, I would put that long URL into a dedicated cell. Just this portion: https://egov.uscis.gov/cris/caseStat...appReceiptNum= Say E1 Then use a formula like: =hyperlink($e$1&b1,a1) instead of: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum="&B1,A1) Then if for some reason that URL changes, I'd only have to change it once. ps. I got to a site that show this: Application Type: I129, PETITION FOR A NONIMMIGRANT WORKER Stumped Non-IT-ian wrote: Dear Mr. Gary''s Student - Thank you. Unfortunately it did not work. It says the formula is not correct. I am using Office Excel 2007. Just FYI we are attempting to use the same hyperlink "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" in all cells but the unique case number EAC0516151060 changes for each cell. "Gary''s Student" wrote: If B1 contains: EAC0516151060 and A1 contains: James Ravenswood then in C1 enter: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" & B1,A1) This will produce a nice, clickable link. -- Gary''s Student - gsnu200851 "Stumped Non-IT-ian" wrote: We liaise with DHS. In our excel sheet we have 2 columns, A and B. A has names. B has unique 10 digit alpha-numeric case numbers DHS generates, say EAC0516151060. We can check case status at website: "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=EAC0516151060" This way we have 100 names and 100 unique numbers that we enter on Excel sheet on daily basis. Can excel sheet be configured in a way that once I enter the unique case number in column B, the web address with the correct unique case number gets automatically attached to the cell so that I have to just click to visit the external website and see the end result? Thanks Raj -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on Automating a common function
Dear Mr. Peterson - Sorry for the errors. Yes it is 13 character number. I
have not used any formula before I approached this forum. I used to manually place the hyperlink in each cell and it takes a lot if time. Your following formula works well: Personally, I would put that long URL into a dedicated cell. Just this portion: https://egov.uscis.gov/cris/caseStat...appReceiptNum= Say E1 Then use a formula like: =hyperlink($e$1&b1,a1) Thank you so much for your help. Now I need help in: 1) How to get the formula "=hyperlink($e$1&b1,a1)" automatically configured for each cell? 2) Using the formula "=hyperlink($e$1&b1,a1)", when I clicked on the cell it did take me to the external website and automatically inserted the unique case number in the application number box of the website. However the number needs to be input exact without spaces. In our case it did put a single space before the case number for some reason. I have to remove it. 3) Also is it possible to get to the direct end result "Application Type: I129, PETITION FOR A NONIMMIGRANT WORKER" without having to correct the space issue and then clicking on "Search Button". Thank you so much again. "Dave Peterson" wrote: It's time to share your formula that you tried. And explain what is in each of the cells that are used in the formula. I was confused about the 10 character alphanumeric string when you showed this as an example: EAC0516151060 That's 13 characters. And make sure you don't have an existing "Insert|Hyperlink" style hyperlink in that cell. Select the cell Ctrl-k (or insert|hyperlink) and remove any link you see Personally, I would put that long URL into a dedicated cell. Just this portion: https://egov.uscis.gov/cris/caseStat...appReceiptNum= Say E1 Then use a formula like: =hyperlink($e$1&b1,a1) instead of: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum="&B1,A1) Then if for some reason that URL changes, I'd only have to change it once. ps. I got to a site that show this: Application Type: I129, PETITION FOR A NONIMMIGRANT WORKER Stumped Non-IT-ian wrote: Dear Mr. Gary''s Student - Thank you. Unfortunately it did not work. It says the formula is not correct. I am using Office Excel 2007. Just FYI we are attempting to use the same hyperlink "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" in all cells but the unique case number EAC0516151060 changes for each cell. "Gary''s Student" wrote: If B1 contains: EAC0516151060 and A1 contains: James Ravenswood then in C1 enter: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" & B1,A1) This will produce a nice, clickable link. -- Gary''s Student - gsnu200851 "Stumped Non-IT-ian" wrote: We liaise with DHS. In our excel sheet we have 2 columns, A and B. A has names. B has unique 10 digit alpha-numeric case numbers DHS generates, say EAC0516151060. We can check case status at website: "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=EAC0516151060" This way we have 100 names and 100 unique numbers that we enter on Excel sheet on daily basis. Can excel sheet be configured in a way that once I enter the unique case number in column B, the web address with the correct unique case number gets automatically attached to the cell so that I have to just click to visit the external website and see the end result? Thanks Raj -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help on Automating a common function
There's nothing in either formula that would have inserted a space character.
So I'm guessing that you actually typed that space character in the document name. Depending on how bad your typing is, you may be able to get by with: =hyperlink($e$1&trim(b1),a1) The =trim(b1) will remove any leading/trailing (and change multiple internal spaces to a single space). If your typing is really bad (including internal spaces), you could use: =hyperlink($e$1&substitute(b1," ",""),a1) And after you get the formula working for one cell, you can click on the autofill button (bottom right corner of the selection box) and drag down as far as you need. Debra Dalgleish explains it here (with pictures): http://contextures.com/xlDataEntry01.html#Mouse ===== As for correcting the link... You can use either adjustment to the formula, but doing things like that scares me. If I ever need that value in the cell for a different use, I have to remember to adjust any new formula the same way. Personally, I would bite the bullet and do my best to fix the real data. If the only problem is those extra spaces.... Select the column (or range) Edit|Replace what: (space character) with: (leave blank) replace all If by chance you've received that data from a web site, you may find that those spaces aren't really normal spaces--they're those non-breaking HTML spaces. You can try this: Select the column (or range) Edit|Replace what: alt-0160 (hold the alt key while typeing 0160 on the numeric keypad) with: (leave blank) replace all =========== And to check to see if you've fixed your data... Insert a new column C. Then put this in C1: =len(b1) and drag down using that technique from Debra's site. Look for lengths that are not exactly 13. Those you'll want to fix--either retype or maybe some other way (depending on the problem). Stumped Non-IT-ian wrote: Dear Mr. Peterson - Sorry for the errors. Yes it is 13 character number. I have not used any formula before I approached this forum. I used to manually place the hyperlink in each cell and it takes a lot if time. Your following formula works well: Personally, I would put that long URL into a dedicated cell. Just this portion: https://egov.uscis.gov/cris/caseStat...appReceiptNum= Say E1 Then use a formula like: =hyperlink($e$1&b1,a1) Thank you so much for your help. Now I need help in: 1) How to get the formula "=hyperlink($e$1&b1,a1)" automatically configured for each cell? 2) Using the formula "=hyperlink($e$1&b1,a1)", when I clicked on the cell it did take me to the external website and automatically inserted the unique case number in the application number box of the website. However the number needs to be input exact without spaces. In our case it did put a single space before the case number for some reason. I have to remove it. 3) Also is it possible to get to the direct end result "Application Type: I129, PETITION FOR A NONIMMIGRANT WORKER" without having to correct the space issue and then clicking on "Search Button". Thank you so much again. "Dave Peterson" wrote: It's time to share your formula that you tried. And explain what is in each of the cells that are used in the formula. I was confused about the 10 character alphanumeric string when you showed this as an example: EAC0516151060 That's 13 characters. And make sure you don't have an existing "Insert|Hyperlink" style hyperlink in that cell. Select the cell Ctrl-k (or insert|hyperlink) and remove any link you see Personally, I would put that long URL into a dedicated cell. Just this portion: https://egov.uscis.gov/cris/caseStat...appReceiptNum= Say E1 Then use a formula like: =hyperlink($e$1&b1,a1) instead of: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum="&B1,A1) Then if for some reason that URL changes, I'd only have to change it once. ps. I got to a site that show this: Application Type: I129, PETITION FOR A NONIMMIGRANT WORKER Stumped Non-IT-ian wrote: Dear Mr. Gary''s Student - Thank you. Unfortunately it did not work. It says the formula is not correct. I am using Office Excel 2007. Just FYI we are attempting to use the same hyperlink "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" in all cells but the unique case number EAC0516151060 changes for each cell. "Gary''s Student" wrote: If B1 contains: EAC0516151060 and A1 contains: James Ravenswood then in C1 enter: =HYPERLINK("https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=" & B1,A1) This will produce a nice, clickable link. -- Gary''s Student - gsnu200851 "Stumped Non-IT-ian" wrote: We liaise with DHS. In our excel sheet we have 2 columns, A and B. A has names. B has unique 10 digit alpha-numeric case numbers DHS generates, say EAC0516151060. We can check case status at website: "https://egov.uscis.gov/cris/caseStatusSearch.do?appReceiptNum=EAC0516151060" This way we have 100 names and 100 unique numbers that we enter on Excel sheet on daily basis. Can excel sheet be configured in a way that once I enter the unique case number in column B, the web address with the correct unique case number gets automatically attached to the cell so that I have to just click to visit the external website and see the end result? Thanks Raj -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining two ranges based on common a common value | Excel Discussion (Misc queries) | |||
Common footer but not common margins please -(Page 1 of 2) etc | Excel Discussion (Misc queries) | |||
Any function to determine the smallest common factor from a list? | Excel Discussion (Misc queries) | |||
Automating the Excel camera function | Excel Discussion (Misc queries) | |||
Need help with automating the updating of a function in worksheet | Excel Worksheet Functions |