Excel 2013 Hyperlinking index array

[Kruwler]

Hello, I am having an issue with Excel.

I got an array-index formula on one sheet (SearchSheet) that returns values from another sheet (DataSheet) based on date or name searches-inputs.

So if I enter 15/5/2015 it will return all the values from my datasheet, whose dates are the same or the specified date is within their date range.

it displays like so:

Name(columnA)|startdate(B)|EndDate(C)|Cost(D) etc

What i want is to make a hyperlink from my index formula so that I can edit the corresponding cell on my data worksheet.

the index formula looks like =IF(ISBLANK($B$9);IF(ROWS(B$12:B12)>$C$4;"";INDEX('DataSheet!A$2:A$2070;SMALL(IF('DataSheet'!$C$2:$C$2070<=$B$4;IF('DataSheet'!$E$2:$E$2070>=$B$4;ROW('DataSheet'!$A$2:$A$2070)-ROW('DataSheet'!$A$2)+1));ROWS(B$12:B12))));IF(ROWS(B$12:B12)>$C$9;"";INDEX('DataSheet'!A$2:A$2070;SMALL(IF('DataSheet'!$A$2:$A$2070=$B$9;ROW('DataSheet'!$A$2:$A$2070)-ROW('DataSheet'!$A$2)+1);ROWS(B$12:B12)))))

the structure is to check for the input which is either name or date, if both it goes with name, and displays either all the values that match the name or the values that the date I entered is, or is within their date range

B4 is my date search cell,
C4 is my date countif number,
B9 is the Name search Cell,
C9 is my Name search countif number

This is spread over SearchSheet (contains Date and Name search cell and countifs) on values B12:K100
On the DataSheet Column A has Names, B has Phone number, C has Start date, D has end date, E is the final end date (made this so i don't have to put end days on daily events manually it is a simple if D is blank = C otherwise =D), F is cost.

So the problem is to create a functioning hyperlink for those 2 index formulas that will jump to the corresponding Cost value in Datasheed F Column for the corresponding dates or names.

Thanks

 

[Kruwler]

Any ideas?

 

[FormR]

Any ideas?

You could try this formula in a spare column of the searchsheet, like your current formula it requires array entry (CSE).

=HYPERLINK("#'Datasheet'!A"&IF(ISBLANK($B$9);IF(ROWS(B$12:B12)>$C$4;"";SMALL(IF(Datasheet!$C$2:$C$2070<=$B$4;IF(Datasheet!$E$2:$E$2070>=$B$4;ROW(Datasheet!$A$2:$A$2070)));ROWS(B$12:B12)));IF(ROWS(B$12:B12)>$C$9;"";SMALL(IF(Datasheet!$A$2:$A$2070=$B$9;ROW(Datasheet!$A$2:$A$2070));ROWS(B$12:B12))));"Click here")

 

[Kruwler]

I would prefer for it to be on the same cell as the index return value. click and go to the corresponding cell. it does not need a friendly name, just the return value

 

[FormR]

I would recommend using a couple of helper columns, for example:

Note: you may need to subsititute the ","s for ";"s.

​

Excel 2012
	A	B	C
11	Helper Index	Helper Hyper	Formula
12	1	2	A
13	2	3	B
14	3	4	C
Sheet1

Cell Formulas
Range	Formula
B12	=IF(A12="","",A12+ROW(Datasheet!$A$2)-1)
C12	=IF(A12="","",HYPERLINK("#'Datasheet'!"&ADDRESS($B12,COLUMNS($C12:C12)),INDEX(Datasheet!A$2:A$2070,$A12)))
A12	{=IF(ROWS($A$12:A12)>MAX($C$4,$C$9),"",IF($B$9="",SMALL(IF(Datasheet!$C$2:$C$2070<=$B$4,IF(Datasheet!$E$2:$E$2070>=$B$4,ROW(Datasheet!$A$2:$A$2070)-ROW(Datasheet!$A$2)+1)),ROWS(A$12:A12)),SMALL(IF(Datasheet!$A$2:$A$2070=$B$9,ROW(Datasheet!$A$2:$A$2070)-ROW(Datasheet!$A$2)+1),ROWS(A$12:A12))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Kruwler]

I would prefer if it could still look something like this

while the datasheet is

 

[FormR]

I would prefer if it could still look something like this

Is there any reason why it can't?