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