Dragging down indirect function

Coenjn89

New Member
Joined
Aug 29, 2014
Messages
3
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!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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)
 
Upvote 0
@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!
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top