Hyperlink, Index, Match Fill question

[lpost1214]

Hello All-

I have come up with a hyperlink, index, match formula that is working, however, I am unable to fill the formula down. Currently it is just copying E4 all the way down the column, and I want it to change to E5, E6, E7 etc...

Formula:
=HYPERLINK("#INDEX('Promising IndyUS'!D:D, MATCH(E4,'Promising IndyUS'!D:D,0))", "Detail")

Thanks in advance!

[NBVC]

Try breaking it up like:

=HYPERLINK("#INDEX('Promising IndyUS'!D:D, MATCH(E" & Row(E4) & ",'Promising IndyUS'!D:D,0))", "Detail")

also, have a moderator move your thread to the proper forum.

[arlu1201]

Thread moved.

[lpost1214]

Thanks, this works! As a follow up, i am trying to use the same formula to then take me from the Promising IndyUS detail worksheet to SKU Promising IndyUS detail worksheet. I saved the file from a reporting tool, therefore, on the Promising IndyUS tab, column d- PO, that I am referencing there are blank cells above and below because of a break that had been placed on the value in those fields. How do I insert a range so that it will match just the rows in column D that contain a PO value? Also, how to I let the value in column D- PO serve as the hyperlink, instead of inserting a column labeled Detail?

=HYPERLINK("#INDEX('SKU Promising IndyUS'!$D:$D, MATCH([???,'SKU Promising IndyUS'!$D:$D,0))", "Detail")

Thanks again.

[NBVC]

Is this what you mean?

=HYPERLINK("#'SKU Promising IndyUS'!$D"&MATCH(REPT("z",255),$D$2:$D2)+2,"Detail")

copied down

[lpost1214]

I don't think this formula is working exactly how I need it to. Currently, when i copy it down it works properly for the first few rows but then hyperlinks me to the incorrect PO number. Also, is there a way to eliminate adding a column as a hyperlink and using the value in column D- PO as the hyperlink?

[NBVC]

The hyperlinking seems to work fine for me... see attached.

There is no PO# 2-4460 on the other page, so the hyperlink goes to the bottom...

Not sure what you meant by the rest...

[lpost1214]

My mistake, you are correct, it does work in that sampling, however, when i apply it to the entire report it doesnt work properly. Attached is a larger sampling. For example, click on detail next to column D value "WOMENSSANDALBACKUP". It hyperlinks you to AM03172012C on the other worksheet.

Also, is there a way to click on the value in column D as the hyperlink, ie. click "WOMENSSANDALBACKUP" instead of the word "detail" next to it. Not a big deal, but would just make the report more attractive if it didnt repeat detail over and over.

Thanks again for all of your help.

[lpost1214]

Sorry, here is the larger sample.

[NBVC]

Ok, good, try this:

=HYPERLINK("#'SKU Promising IndyUS'!$D"&MATCH(INDEX($D$2:$D2,MATCH(REPT("z",255),$D$2:$D2)),'SKU Promising IndyUS'!D:D,0)+2,"Detail")

[lpost1214]

Perfect! Thanks you so much