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

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

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

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

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

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