Dragging down indirect function

[Coenjn89]

Dear MrExcel army, I have recreated an indirect function which I found on youtube, to let a dropdown box select from which tab data is pulled. this is my formula:=INDIRECT("'"&$E$1&"'!E17"). E1 is the reference to the dropdown where I can select tabs box and E17 the value it looks up in other tabs. I want to drag this function down and sideways, so that E17 becomes E18, E19 or F17 etc. When i drag down now, E17 just remains E17 for all the cells. Any help would be greatly appreciated!

 

[Special-K99]

Try

=INDIRECT("$E$1'!"&E17)

 

[Coenjn89]

Try

=INDIRECT("$E$1'!"&E17)

Thanks for the help already! It does indeed somewhat help the problem, as the cells are now adjusted when I drag down. But now the formula returns a #Ref! error. Any idea how to solve this?

 

[Peter_SSs]

Welcome to the MrExcel board!

Special-K99 has dropped out some of your single & double quotes, which will still be required.
See if this fixes it.

=INDIRECT("'"&$E$1&"'!"&E17)

 

[Jonmo1]

I think you need

=INDIRECT("'"&$E$1&"'!"&CELL("address",E17))

 

[Coenjn89]

@Peter_SSs Thanks for the welcome! Your suggestion still rendered the same error though.

@Jonmo1 Thanks for that. It works! I am too much of an excel noob to understand the formula in this condition, but it works and thats what matters


You guys rock!

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[Peter_SSs]

Ah, I misunderstood your use of E17. I thought it (& others below/right) contained the addresses of the cells to look at in the other sheets.
Glad Jonmo could see what you were after.

 

[alfars4ever]

Greetings All, I just joined this great forum, am new in excel and hope to increase my knowledge in it.
Regarding dragging using INDIRECT function i tried the solution provided by Jonmo1 and it worked fine with me, however there is a problem when i add a row in the main data sheet, consequently the data will change from E17 to E18 which will miss up all my calculations. is there a way to avoid this?
ur help is much appreciated
thank you http://www.mrexcel.com/forum/members/jonmo1.html