I have tables of figures which I am extracting, via VLOOKUP, to different
reports. I am then compiling a total, of each of these values, in the reports
themselves.
Then I am comparing the total given in my report with the total that is also
included within the initial table and asking Excel to give me an error
message should the two amounts not balance.

I have done this many times without any issues - however I seem to have a
problem in some reports whereby I am receiving an error message even when the
value in the report matches the value in the table.

I have manually checked that the report adds up correctly - and the value in
the table. I have checked that neither value includes a spurious amount
within the decimal (down to 5 places).
I have also split my formula (that produces and error message) in to its two
halves - and these both return the same result.

I am afraid I can't fathom why I am getting an error message. Can anyone help?

Thanks

The miniscule error may be even farther "down" than the 5th decimal place.
Try this, it may cure your problem:

Make a copy of your workbook and use it for this test. In the Tools --
Options dialog, go to the [Calculation] tab and select the "Precision as
Displayed" option. You'll be warned of a loss of precision when you apply
the change. This is a 'by workbook' change, so it won't affect other
workbooks. See if that clears up your problem.

"BabyMc" wrote:

I have tables of figures which I am extracting, via VLOOKUP, to different
reports. I am then compiling a total, of each of these values, in the reports
themselves.
Then I am comparing the total given in my report with the total that is also
included within the initial table and asking Excel to give me an error
message should the two amounts not balance.

I have done this many times without any issues - however I seem to have a
problem in some reports whereby I am receiving an error message even when the
value in the report matches the value in the table.

I have manually checked that the report adds up correctly - and the value in
the table. I have checked that neither value includes a spurious amount
within the decimal (down to 5 places).
I have also split my formula (that produces and error message) in to its two
halves - and these both return the same result.

I am afraid I can't fathom why I am getting an error message. Can anyone help?

Thanks

Thanks

Due to what seemed to be error messages received in my original posting - I
ended up with the same posting more than once.

I think I may now have a resolution - I've copied my latest response from
that thread. If you are able to answer my further query then that would be
lovely.


I don't understand why one would need to put the round function around a
lookup where the results are not to more decimal places than required.
However, having said that, inserting the round function around the lookup
does cure it.

This would leave me with another question.

How does one easily insert the round function around the lookup - when I
have many forumlas to do and the lookup is using many different filenames
(which, I think, prevents me from using edit replace)?

Thanks again


"JLatham" wrote:

The miniscule error may be even farther "down" than the 5th decimal place.
Try this, it may cure your problem:

Make a copy of your workbook and use it for this test. In the Tools --
Options dialog, go to the [Calculation] tab and select the "Precision as
Displayed" option. You'll be warned of a loss of precision when you apply
the change. This is a 'by workbook' change, so it won't affect other
workbooks. See if that clears up your problem.

"BabyMc" wrote:

I have tables of figures which I am extracting, via VLOOKUP, to different
reports. I am then compiling a total, of each of these values, in the reports
themselves.
Then I am comparing the total given in my report with the total that is also
included within the initial table and asking Excel to give me an error
message should the two amounts not balance.

I have done this many times without any issues - however I seem to have a
problem in some reports whereby I am receiving an error message even when the
value in the report matches the value in the table.

I have manually checked that the report adds up correctly - and the value in
the table. I have checked that neither value includes a spurious amount
within the decimal (down to 5 places).
I have also split my formula (that produces and error message) in to its two
halves - and these both return the same result.

I am afraid I can't fathom why I am getting an error message. Can anyone help?

Thanks

My solution may save you from having to hunt down all of the VLOOKUPs and
modify them. You can't easily change them all because of the things you've
said, like referring to different filenames. At a minimum you'd have to
change each one with a different filename and extend/fill it as permitted,
and that might not be completely possible.


"BabyMc" wrote:

Thanks

Due to what seemed to be error messages received in my original posting - I
ended up with the same posting more than once.

I think I may now have a resolution - I've copied my latest response from
that thread. If you are able to answer my further query then that would be
lovely.


I don't understand why one would need to put the round function around a
lookup where the results are not to more decimal places than required.
However, having said that, inserting the round function around the lookup
does cure it.

This would leave me with another question.

How does one easily insert the round function around the lookup - when I
have many forumlas to do and the lookup is using many different filenames
(which, I think, prevents me from using edit replace)?

Thanks again


"JLatham" wrote:

The miniscule error may be even farther "down" than the 5th decimal place.
Try this, it may cure your problem:

Make a copy of your workbook and use it for this test. In the Tools --
Options dialog, go to the [Calculation] tab and select the "Precision as
Displayed" option. You'll be warned of a loss of precision when you apply
the change. This is a 'by workbook' change, so it won't affect other
workbooks. See if that clears up your problem.

"BabyMc" wrote:

I have tables of figures which I am extracting, via VLOOKUP, to different
reports. I am then compiling a total, of each of these values, in the reports
themselves.
Then I am comparing the total given in my report with the total that is also
included within the initial table and asking Excel to give me an error
message should the two amounts not balance.

I have done this many times without any issues - however I seem to have a
problem in some reports whereby I am receiving an error message even when the
value in the report matches the value in the table.

I have manually checked that the report adds up correctly - and the value in
the table. I have checked that neither value includes a spurious amount
within the decimal (down to 5 places).
I have also split my formula (that produces and error message) in to its two
halves - and these both return the same result.

I am afraid I can't fathom why I am getting an error message. Can anyone help?

Thanks

Thanks (both JoeU2004 and JLatham)

I'm not sure I want to go down the PAD route in this instance - though I
shall try and remember it for future reference.
I'm not familiar, enough with VB, so I think I will go with Edit, Replace.
For what I need to do, and given that I will need to access each workbook
anyway it will be my best option.

Thanks again

"JLatham" wrote:

My solution may save you from having to hunt down all of the VLOOKUPs and
modify them. You can't easily change them all because of the things you've
said, like referring to different filenames. At a minimum you'd have to
change each one with a different filename and extend/fill it as permitted,
and that might not be completely possible.


"BabyMc" wrote:

Thanks

Due to what seemed to be error messages received in my original posting - I
ended up with the same posting more than once.

I think I may now have a resolution - I've copied my latest response from
that thread. If you are able to answer my further query then that would be
lovely.


I don't understand why one would need to put the round function around a
lookup where the results are not to more decimal places than required.
However, having said that, inserting the round function around the lookup
does cure it.

This would leave me with another question.

How does one easily insert the round function around the lookup - when I
have many forumlas to do and the lookup is using many different filenames
(which, I think, prevents me from using edit replace)?

Thanks again


"JLatham" wrote:

The miniscule error may be even farther "down" than the 5th decimal place.
Try this, it may cure your problem:

Make a copy of your workbook and use it for this test. In the Tools --
Options dialog, go to the [Calculation] tab and select the "Precision as
Displayed" option. You'll be warned of a loss of precision when you apply
the change. This is a 'by workbook' change, so it won't affect other
workbooks. See if that clears up your problem.

"BabyMc" wrote:

I have tables of figures which I am extracting, via VLOOKUP, to different
reports. I am then compiling a total, of each of these values, in the reports
themselves.
Then I am comparing the total given in my report with the total that is also
included within the initial table and asking Excel to give me an error
message should the two amounts not balance.

I have done this many times without any issues - however I seem to have a
problem in some reports whereby I am receiving an error message even when the
value in the report matches the value in the table.

I have manually checked that the report adds up correctly - and the value in
the table. I have checked that neither value includes a spurious amount
within the decimal (down to 5 places).
I have also split my formula (that produces and error message) in to its two
halves - and these both return the same result.

I am afraid I can't fathom why I am getting an error message. Can anyone help?

Thanks