0

I have a sheet where the data shown is based on combobox selections. The values are calculated using a formula like this:

INDEX(INDIRECT($Q61),SUMPRODUCT((INDIRECT($O$5)=$B$1)*(INDIRECT($O$6)=$B$2)*(INDIRECT($O$3)=$B$3)*(INDIRECT($O$7)=B$4)*ROW(INDIRECT($O$8)))-5),"")

Q61 is a range in a different sheet: 'Data'!$AO$6:$AO$104000 SUMPRODUCT matches four other criteria based on combobox selections.

The formula should calculate the row in the range and return a value. Is there a way in VBA to determine the address found by that formula. It should be something like 'Data'!$AO$90. I must get the address because I am looking for the hyperlink in that address.

The INDEX formula correctly returns the value in 'Data'!$AO$90. I want to put back the hyperlink.

This is an image of the sheet. Each value is calculated using an INDEX formula as shown above. A few values should have hyperlinks. I must get the original address in the 'Data'! sheet to put them back with VBA whenever the user makes a new selection.

enter image description here

  • You gan get Excel to show you the row_num being used by the INDEX(). Select the cell with the formula, and click on the word INDEX in the formula bar. Click row_num in the dropdown and hit the F9 key. There are illustrations in an answer to this question. – Bandersnatch Apr 22 '18 at 12:23
  • 1
    You can also use `=CELL("address",your_formula). – Bandersnatch Apr 22 '18 at 15:18
0

From 'Data'!$AO$6:$AO$104000 range , you get the value in 'Data'!$AO$90 . That is how a standard index() functions do.

Since you just need the address of 'Data'!$AO$90 you can get it by bisecting the index() function you use to get it. The idea is.. since index(<range>,<RowNumber>,<ColNumber>), and for this case let sat the is 85. So the "address" you need is

= "'Data'!$AO$"&(<RowNumber>+5)

Have a try, hope that helps. ( :

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.