HLOOKUP Not working as expected

[robbbo]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have created a PDF in Adobe Acrobat that consists of another of forms that are filled in by the user. When the fill in is complete the user emails the data to me. Within Acrobat I change the data emailed to me to a spreadsheet. In the resultant spreadsheet the names of the forms from the PDF are in row 1 and the data entered in the forms are just below it in row 2. I need to link this data to an Excel spreadsheet. I used HLOOKUP to find the data to link to my excel spreadsheet. I filled in all the forms on the PDF to test my links and everything works fine.

In some cases a user my not fill in all the forms and leave some blank. When some are blank, and the data is emailed to me, when I convert the data to a spreadsheet within Acrobat the forms that are blank no longer appear in the spreadsheet that is generated by adobe Acrobat. When the spreadsheet that has the links to this Adobe Generated spreadsheet using the HLOOKUP function is opened the data is updated, but the HLOOKUP linked cell that no longer is available selects the data from the cell adjacent to it where it location used to be rather than returning an error or just a blank like it should.

It appears that all the cells that do have data in them work correctly but the cells that are no longer in the "linked to" cell return the data from the adjacent cell rather than a blank.

The link formula is as follows:

=HLOOKUP("10-YourName",'report.csv'!$B$1:$ZY$2,2)

The report.csv is the worksheet generated by the Adobe Acrobat program and the "10-YourName" is the name of the form from the PDF.

How can I correct this issue?

Any suggestions would be appreciated!

 

[John McGhie]

Check the Excel Help for the HLOOKUP function.

What is your setting for the "range_lookup" parameter, and why?

As you have discovered, handling data returned by "end users" who don't care
about your result at all is very character-building. Understand apply the
concept of "Defensive Programming" (look it up in Google...)

In this case, you are scanning the whole of a CSV returned by an end user:
you have no validity test or error-checking or contingency routine to handle
the possibility that the data you expect may be "not there!".

That is a Victoria Cross-winning level of bravery; and remember that most
Victoria Crosses are awarded "posthumously"

Cheers

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I
have created a PDF in Adobe Acrobat that consists of another of forms that are
filled in by the user. When the fill in is complete the user emails the data
to me. Within Acrobat I change the data emailed to me to a spreadsheet. In the
resultant spreadsheet the names of the forms from the PDF are in row 1 and the
data entered in the forms are just below it in row 2. I need to link this data
to an Excel spreadsheet. I used HLOOKUP to find the data to link to my excel
spreadsheet. I filled in all the forms on the PDF to test my links and
everything works fine.

In some cases a user my not fill in all the forms and leave some blank. When
some are blank, and the data is emailed to me, when I convert the data to a
spreadsheet within Acrobat the forms that are blank no longer appear in the
spreadsheet that is generated by adobe Acrobat. When the spreadsheet that has
the links to this Adobe Generated spreadsheet using the HLOOKUP function is
opened the data is updated, but the HLOOKUP linked cell that no longer is
available selects the data from the cell adjacent to it where it location used
to be rather than returning an error or just a blank like it should.

It appears that all the cells that do have data in them work correctly but the
cells that are no longer in the "linked to" cell return the data from the
adjacent cell rather than a blank.

The link formula is as follows:

=HLOOKUP("10-YourName",'report.csv'!$B$1:$ZY$2,2)

The report.csv is the worksheet generated by the Adobe Acrobat program and the
"10-YourName" is the name of the form from the PDF.

How can I correct this issue?

Any suggestions would be appreciated!

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[robbbo]

Check the Excel Help for the HLOOKUP function.

>
> What is your setting for the "range_lookup" parameter, and why?
>
> As you have discovered, handling data returned by "end users" who don't care
> about your result at all is very character-building. Understand apply the
> concept of "Defensive Programming" (look it up in Google...)
>
> In this case, you are scanning the whole of a CSV returned by an end user:
> you have no validity test or error-checking or contingency routine to handle
> the possibility that the data you expect may be "not there!".
>
> That is a Victoria Cross-winning level of bravery; and remember that most
> Victoria Crosses are awarded "posthumously"
>
> Cheers
>
> On 6/04/10 2:24 AM, in article (e-mail address removed)2ac0,
> "[email protected]" wrote:
>
>
> --
>
> The email below is my business email -- Please do not email me about forum
> matters unless I ask you to; or unless you intend to pay!
>
> John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
> McGhie Information Engineering Pty Ltd
> Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]
>
>
>

It appears I left out the 4th parameter for the HLOOKUP Function which is True or False. Using False when the data is not there returns a #N/A which is okay, but I would like to have it show a blank. Do you know how to convert an #N/A to a blank?

 

[John McGhie]

It appears I left out the 4th parameter for the HLOOKUP Function which is True
or False.

Well done That's what I hoped you would find

Using False when the data is not there returns a #N/A which is okay,
but I would like to have it show a blank. Do you know how to convert an #N/A
to a blank?

That's what I meant by "Defensive Programming". You need to program to
allow for the possibility that the data will be "not there" and handle it
gracefully when it isn't.

You need to add an IF statement to tell Excel what to do if the data is not
there, and an ISNA function to determine accurately whether the data really
is not there.

=IF(ISNA(HLOOKUP("10-YourName",'report.csv'!$B$1:$ZY$2,2,FALSE)),"",HLOOKUP(
"10-YourName",'report.csv'!$B$1:$ZY$2,2,FALSE))

Working from the inside out, that first runs the HLOOKUP to see what is
there: using the FALSE parameter to ensure that we find out if the answer is
"nothing".

We return the result of that to the ISNA function, which will return TRUE if
the HLOOKUP returns exactly "N/A" (the name field may be there, but contain
a single space: we need to know the difference).

If ISNA returns FALSE (the data is anything except "not there", then we run
the lookup again and past the value back to the IF statement. If ISNA has
given it "TRUE" then the IF statement prints a space, otherwise, it prints
whatever the HLOOKUP sends it.

If you wanted to be truly defensive, you would add an outer IF statement
that tests to make sure the Form is actually present: if the whole form is
missing, you may want to suppress activity for all of the lookups.

Hope this helps

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[robbbo]

WOW it works. Actually in some of the cells I needed to replace the blank ("&quot with a zero (0) as these were used in math formulas that when left blank created errors.

Thank You,Thank You,Thank You,Thank You

Robbbo