How to link to another workbook cell but specific sheet is variable

[juntjoo]

I want to display data from one cell of one workbook to another but also be able to change the specific sheet freely to work with different data depending on the sheet sort of how you can have different data on a mail merge page depending on which sheet you select in your data source. Any way to do this? Thanks.

[tuph]

One way to do this is to use INDIRECT function pointing to a cell that contains the names of the worksheets. I have set up a small example in the attachment.

This is the formula =INDIRECT($E$2&"!"&C$1&$A2+1) where cell $E$2 contains a drop down list of worksheet names that can be selected to change the data on the "Yearly" worksheet.

[juntjoo]

oooh that's brilliant! to me at least. thanks. but I was looking under the "data" tab. will your method work across different workbooks/files? I think so right? just include the correct sytax for the file right? [] these or something? will look into it..

[juntjoo]

I don't even know how you made that button. how did you make that cool button?

[juntjoo]

still looking for info on how to make these buttons that link to different sheets. I've gotten lost in the articles on these buttons and all of them instruct you make the list by entering values into cells somewhere. so how would you redirect that reference to sheets? this is way over my head. anyone?

[juntjoo]

good stuff! I backwards engineered all that brilliance there and it's working great. Thanks

[tuph]

Well done! Sorry I didn't get back to you earlier but you've seen how exciting it is to work out a problem yourself. Remember to flag this thread as SOLVED and have a great day.

[juntjoo]

hey tugh, you know how you have an extra row and a column dedicated to indicating the rows and columns for both the main sheet and reference sheets? where is it necessary? I figured that the INDIRECT formula tells it to read it first the sheet, then the corresponding rows/columns, but when I erased the labels in the reference sheets in my file and yours too as an experiment, it didn't affect the results. So then logically I started removing the labels in the source sheet and got problems.

So then that tells me that it's the main sheet that needs the labels but get this: I have one sheet I started using your INDIRECT formula with to reference the other sheets in the same manner as I was already doing, but this one I never put an extra row to label each column, YET up til today(don't know what happened) it was working, INDIRECT'ing to other sheets but WITHOUT that top row #1 labeled a,b,c,d etc... So how was that working before?

edit: never mind! I was wrong. this latest strangely behaving file was doing exactly as it was supposed to and it DID in fact have that extra row in there that I had hid real well. So unless you disagree it appears that Excel reads the INDIRECT instructions as if from the sheet that it's in.