Change sheets in formula at other workbook automatically

[batakman]

Hi Forum members..


I have a problem to automatically change sheets in my formula.

The problem is like this :

='[shoes.xlsx]art1'!$v$11

It's the "art1" that should change to "art2" and so on.

I'd be very grateful for your precious help.
Thanks...

[dredwolf]

this is do-able, and has been discussed many times here, but without an example workbook(s), its very hard to offer a specific solution, first thing to do is create a list of sheets :
1. go to the "formula" tab and select "Define name","SheetNames"
2. in the "refers to", enter...=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
3. in an area where you want the sheet names, copy this down...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A3)),"")

then you have to use the indirect function() to use those names to get the data you want, again, without a sample, VERY hard to give a specific formula

[batakman]

Hi dredwolf, thanks for your reply really appreciate it..

lets say for example :
There are book1 and book2
Data cell B1 book1 take from book2 sheet1 cell m1.
Data cell C1 book1 take from book2 sheet2 cell m1.
Data cell D1 book1 take from book2 sheet3 cell m1.

How do i make the formula in B1 book1 so that i can copy and drag to C1 in book1, D1 in book1 and so on ?

Thanks and regards...

[dredwolf]

please post sample workbooks, I have no interest in trying to re-create your workbooks, it will take me longer to do that than create the answer...; give me something to work with, and I can answer the question; make me do ALL the work for you, and I have no interest..

[Kevin UK]

Hi batakman

Here are a couple of links to start with.

Relative vs. Absolute Cell References in Spreadsheets

How to use the INDIRECT function to create references in Excel

[batakman]

Thanks Kevin.. but still i cannot solve the problem with the link.

It cannot change the sheet automatically.

Any other option from dear members?
Thanks in advance..