• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

#value! instead of date with index.

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
0
Eric Zwiekhorst
Asked:
Eric Zwiekhorst
  • 6
  • 5
  • 5
  • +1
1 Solution
 
SiddharthRoutCommented:
The reference in Cell A19 is broken. Correct that and I believe it will work after that.

Sid
0
 
StephenJRCommented:
When I open your attachment, I just get #REF errors.
0
 
Rory ArchibaldCommented:
Did you delete a sheet before posting?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
StephenJRCommented:
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)
0
 
Rory ArchibaldCommented:
Oh, and you need to array-enter your formula using Ctrl+Shift+Enter.
0
 
Eric ZwiekhorstAuthor Commented:
I had problems uploading the file before, this is a new try..

KPI46-2804.xls
0
 
StephenJRCommented:
Glad you cleared up that mystery rory.
0
 
Rory ArchibaldCommented:
Same issue, but see my last comment - it's an array formula.
0
 
Eric ZwiekhorstAuthor Commented:
I tried to open the first file and it opened fine for me?

Kind regards

Eric
0
 
SiddharthRoutCommented:
Zwiekhorst: Please refer to my post.

Sid
0
 
SiddharthRoutCommented:
What is the actual formula in Cell A19?

Sid
0
 
Eric ZwiekhorstAuthor Commented:
I can also reopen the second attempt?

There must be something else wrong..

kr
0
 
Rory ArchibaldCommented:
I repeat - you need to array-enter the formula in B19. :)
0
 
Eric ZwiekhorstAuthor Commented:
=INDEX(JCDS!D1:D5732,MATCH(A19&B17,JCDS!A1:A5732&JCDS!B1:B5732,0),0,1)

this is the formula giving the problem
0
 
Rory ArchibaldCommented:
Select B19. press f2, then press Ctrl+Shift+Enter.
0
 
SiddharthRoutCommented:
You again missed my point ;)

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

Sid
0
 
StephenJRCommented:
Zwiekhorst: just read rorya's post!
0
 
SiddharthRoutCommented:
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
0
 
Rory ArchibaldCommented:
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.
0
 
Eric ZwiekhorstAuthor 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
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now