Index function & Data Extract

**sulaiman abed** · 10-22-2011, 10:09 AM

Hi all,

i have watched Excel Magic Trick # 185 (Dynamic Data Extraction) and im trying to reflect it in my sheet........ this is my first time using this function..... but unfortunately it didnt work .... so please review the attachement and provide me with a solution or amend it to become working

noting i want to extract data based on the 2 tables unique keys...... function start on Coulmn U and to the entire table then

thank you in advance

**TMShucks** · 10-22-2011, 04:49 PM

Please see the attached updated example.

Thank you for introducing me to the Excel Magic Tricks ... excellent stuff!

Regards

**sulaiman abed** · 10-23-2011, 05:34 AM

Amaizing Man..... Thank you very much .....

i noticed that my problem was due to not using the Substitute Function !! could you please explain it to me and why you used it ?

thannk for your efforts

**TMShucks** · 10-23-2011, 06:13 AM

You are using an INDIRECT function. This is picking up the cell with the Named Range value. Unfortunately, the value in the cell contains spaces and these are converted to underscores when the Named Range is created. The SUBSTITUTE takes the cell value and converts spaces to underscores.

The other way to do it is to ensure your headers don't have any spaces but that limits your flexibility.

If this has answered your question, please mark your thread as Solved. See my signature for details or the FAQ.

Regards