We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

#value! instead of date with index.

Eric Zwiekhorst
on
Medium Priority
238 Views
Last Modified: 2012-06-22
dear experts,
on B19 of sheet KPI 4.6 you can see #VALUE!
but when you click on the Fx for function help it shows as result 20-7-2010?
Why can't I see this result instead of the #VALUE!

kr

Eric
KPI46-2804.xls
Comment
Watch Question

The reference in Cell A19 is broken. Correct that and I believe it will work after that.

Sid
When I open your attachment, I just get #REF errors.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Did you delete a sheet before posting?
I must confess I don't know why that is happening - no doubt others will - but one workaround would be to concatenate A and B in G and then use this formula:

=INDEX(JCDS!D1:D5732,MATCH(A19&B17,JCDS!G1:G5732,0),1)
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Eric ZwiekhorstSAP Business Consultant

Author

Commented:
I had problems uploading the file before, this is a new try..

KPI46-2804.xls
Glad you cleared up that mystery rory.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Same issue, but see my last comment - it's an array formula.
Eric ZwiekhorstSAP Business Consultant

Author

Commented:
I tried to open the first file and it opened fine for me?

Kind regards

Eric
Zwiekhorst: Please refer to my post.

Sid
What is the actual formula in Cell A19?

Sid
Eric ZwiekhorstSAP Business Consultant

Author

Commented:
I can also reopen the second attempt?

There must be something else wrong..

kr
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
I repeat - you need to array-enter the formula in B19. :)
Eric ZwiekhorstSAP Business Consultant

Author

Commented:
=INDEX(JCDS!D1:D5732,MATCH(A19&B17,JCDS!A1:A5732&JCDS!B1:B5732,0),0,1)

this is the formula giving the problem
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Select B19. press f2, then press Ctrl+Shift+Enter.
You again missed my point ;)

That formula is based on Cell A19. What is the formula in A19?

Sid
Zwiekhorst: just read rorya's post!
Cell B19 formula will only work if the value in Cell A19 is correct. Are we sure that Cell A19 is correct? Only OP can confirm...

Sid
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
If Eric is getting #VALUE rather than #REF, which he states is the case, then his A19 formula is OK, but he hasn't array-entered the formula in B19.
Eric ZwiekhorstSAP Business Consultant

Author

Commented:
Hi Rorya,

You are correct, I array entered and the value showed, a assumed that when using the entry help Fx it would do automaticly the correct entry...

This shows again we are never to old nor to wise to learn..
Kr Eric
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.