indirect formula help!!!!

I am a fairly advanced excel user. However not a pro! Here is what is going on... I have a summary sheet, where i select a drop down. This drop down changes what sheet to pull from.

I am linking to the correct cell by using this: =INDIRECT("'"&$A$2&"'!"&CELL("address",B9))

It works great! however, everytime i insert a row on that summary tab this will happen: =INDIRECT("'"&$A$2&"'!"&CELL("address",B10))

So the cell references will automatically change as well. The absolute $B$9 stuff will not work. It continues to adjust the formula, based on how many columns I add.

Any thoughts??

Thanks Sandy! Wish i had you next to me... now I am doing what you said and it is dragging the same number across and down.

I probably confused you! The b9 should change when i drag up or down, just no "shift" when I add a row or column, does that make sense?

Here i added a sample, you will see the formula that i used to work with the drop down. I have

You added nothing



Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If link is not from FORUM server you have to wait until someone will want to watch the file from an external server.

did you get it??

Try this ...

=VLOOKUP($A9,INDIRECT("'"&$A$2&"'!A8:AK1000"),COLUMN(),0)

Thank you however it doesnt work because there are duplicates in which I need to keep there which is why i need to link it directly to the cell. Thank you though!

IC, I will look at this ...
But in this case if you want value from B9 and use DV for changing Sheets and add row(s) over cells you need to look for VBA solution.
If you want you can play with OFFSET() or INDEX() but both of them return value not address but you need free not locked address (B9) inside INDIRECT.

ahhh! I feel like this is so close, and that it is something so silly! THanks for your patience.

You are welcome

Maybe?

=INDIRECT("'"&$A$2&"'!R"&CELL("row",'Inside Sales'!B9)&"C"&COLUMN(),0)

Thank you Phuo, again didnt work!

CELL("address",B9))

it is for sure a problem with this part, just do not know a solution

See attached...

Phuocam, nice R1C1

Wow, great job! is there a way to lock in the columns as well?? adding rows it works fine, adding a column shifts

Thank you!

=INDIRECT("'"&$B$2&"'!R"&CELL("row",'Inside Sales'!B9)&"C"&CELL("col",'Inside Sales'!B9),0)

Or:

=IF(C2="Inside Sales",'Inside Sales'!B9,'Field Sales'!B9)

bravo! I used the first one.. could you explain the formula so i understand it?? this was a lifesaver thank you so much...

If the problem is solved
- it's always a good practice to show respect to the person(s) who have helped you
- is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
- mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,
Thanks