SOLVED

Switching/Opening worksheets across 2 workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-1787161%22%20slang%3D%22en-US%22%3ESwitching%2FOpening%20worksheets%20across%202%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1787161%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20workbook1%20and%20workbook2%20each%20with%2012%20worksheets%20one%20for%20each%20month%20January%2C%20February%20etc.......%20I%20open%20workbook1%20first%20and%20at%20some%20time%20open%20or%20switch%2C%20if%20it%20is%20already%20open%2C%20to%20workbook2%20by%20using%20in%20a%20cell%20HYPERLINK(%22Quotation%20Unit%20Prices.xlsm%22).%20Is%20there%20a%20way%20that%20i%20can%20open%20or%20switch%20to%20workbook2%20in%20the%20same%20worksheet%20that%20i%20were%20in%20workbook1%3F%3F%3F%3F%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1787161%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1790724%22%20slang%3D%22en-US%22%3ERe%3A%20Switching%2FOpening%20worksheets%20across%202%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1790724%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F826237%22%20target%3D%22_blank%22%3E%40kobus1305%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DHYPERLINK(%22%5Bc%3A%5Ctest%5Cexcel%5Cdestination.xlsx%5D%22%26amp%3BMID(CELL(%22filename%22%2CA1)%2CFIND(%22%5D%22%2CCELL(%22filename%22%2CA1))%2B1%2C255)%26amp%3B%22!A1%22%2C%22destination%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BObviously%2C%20you%20will%20need%20to%20change%20%22c%3A%5Ctest%5Cexcel%5Cdestination.xlsx%22%20for%20your%20path%20and%20your%20filename.%3C%2FP%3E%3CP%3EThis%20will%20grab%20the%20sheet%20name%20you%20are%20currently%20in%20and%20will%20open%20the%20same%20sheet%20in%20your%20destination%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792662%22%20slang%3D%22en-US%22%3ERe%3A%20Switching%2FOpening%20worksheets%20across%202%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20Thank%20you%20so%20much%20for%20your%20response.%20I%20do%20not%20think%20I%20have%20expressed%20the%20situation%20so%20very%20well.%20I%20am%20going%20to%20forward%20the%20workbook%20to%20you.%20You%20will%20see%20in%20column%20header%20%22Open%20Quotation%20Unit%20Prices%22%20is%20the%20hyperlinks%20on%20each%20worksheet.%20That%20means%20when%20workbook%20%22Quotation%20Unit%20Prices%22%20is%20opened%20that%20hyperlink%20knows%20on%20which%20worksheet%20it%20is%20and%20which%20worksheet%20to%20open%20on.%20My%20Hyperlink%20do%20not%20have%20a%20path%20name%20in%20it%20because%20the%20files%20is%20in%20the%20same%20directory%20on%20the%20hard%20drive.%20I%20just%20can%20not%20figure%20out%20how%20to%20format%20the%20Hyperlink%20function%20to%20accomplish%20it.%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1792880%22%20slang%3D%22en-US%22%3ERe%3A%20Switching%2FOpening%20worksheets%20across%202%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792880%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F826237%22%20target%3D%22_blank%22%3E%40kobus1305%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EYou%20sent%20%22Quotation%20Program%22.%20I'm%20assuming%20you%20have%20another%20workbook%20named%20%22Quotation%20unit%20price%22%20somewhere%2C%20right%3F%20Sorry%20I'm%20a%20bit%20confused%2C%20I%20feel%20like%20I'm%20missing%20some%20info.%3C%2FP%3E%3CP%3EEither%20way%2C%20the%20following%20will%20give%20you%20the%20name%20of%20the%20current%20worksheet%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMID(CELL(%22filename%22%2CA1)%2CFIND(%22%5D%22%2CCELL(%22filename%22%2CA1))%2B1%2C255)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESo%2C%20in%20my%20previous%20post%2C%20I%20concatenate%20it%20in%20the%20hyperlink%20formula.%20that%20way%2C%20if%20you%20are%20in%20workbook%20%22ABC.xlsx%22%2C%20in%20sheet%20%22august%22%20the%20link%20will%20open%20workbook%20%22DEF.xlsx%22%20in%20sheet%20%22august%22%20You%20can%20remove%20the%20path%20for%20the%20filename%20if%20you%20want%20but%20leaving%20it%20there%20won't%20hurt.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20if%20you%20can%20send%20me%20the%20other%20missing%20file%2C%20tell%20me%20exactly%20where%20and%20in%20which%20workbook%20%26amp%3B%20worksheet%20you%20want%20the%20link%20to%20be%20(give%20me%20the%20actual%20cell.%20e.g.%22E1%22)%20I'll%20be%20able%20to%20better%20assist%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi, I have workbook1 and workbook2 each with 12 worksheets one for each month January, February etc....... I open workbook1 first and at some time open or switch, if it is already open, to workbook2 by using in a cell HYPERLINK("Quotation Unit Prices.xlsm"). Is there a way that i can open or switch to workbook2 in the same worksheet that i were in workbook1????

Thank You

Regards

7 Replies
Highlighted
Best Response confirmed by kobus1305 (Occasional Contributor)
Solution

Hi @kobus1305,

 

Try this:

=HYPERLINK("[c:\test\excel\destination.xlsx]"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"!A1","destination")

 Obviously, you will need to change "c:\test\excel\destination.xlsx" for your path and your filename.

This will grab the sheet name you are currently in and will open the same sheet in your destination workbook.

Highlighted

@Bennadeau 

Hi, Thank you so much for your response. I do not think I have expressed the situation so very well. I am going to forward the workbook to you. You will see in column header "Open Quotation Unit Prices" is the hyperlinks on each worksheet. That means when workbook "Quotation Unit Prices" is opened that hyperlink knows on which worksheet it is and which worksheet to open on. My Hyperlink do not have a path name in it because the files is in the same directory on the hard drive. I just can not figure out how to format the Hyperlink function to accomplish it.

Regards

Thank You

Highlighted

@kobus1305 

Hi,

You sent "Quotation Program". I'm assuming you have another workbook named "Quotation unit price" somewhere, right? Sorry I'm a bit confused, I feel like I'm missing some info.

Either way, the following will give you the name of the current worksheet:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

So, in my previous post, I concatenate it in the hyperlink formula. that way, if you are in workbook "ABC.xlsx", in sheet "august" the link will open workbook "DEF.xlsx" in sheet "august" You can remove the path for the filename if you want but leaving it there won't hurt. 

So, if you can send me the other missing file, tell me exactly where and in which workbook & worksheet you want the link to be (give me the actual cell. e.g."E1") I'll be able to better assist you.

Highlighted

@Bennadeau 

Hi,

I am confused to!!!!!When I am in workbook "Quotation Program.xlsm" on August worksheet and select the hyperlink "Hyperlink("Quotation Unit Prices .xlsm") to open or switch to "Quotation Unit Prices.xlsm"

how must the hyperlink looks like to also open on August worksheet in "Quotation Unit Prices.xlsm? The function do not have to go looking for which worksheet I am on. Each hyperlink function is formulated on each worksheet for that worksheet!

Regards

Thank You

Highlighted

@kobus1305 

ok, in Workbook "Quotation Program.xlsm", Sheet "August", type in this formula:

=HYPERLINK("[Quotation Unit Price.xlsm]"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)&"!A1","destination")

This will dynamically open "Quotation Unit Price.xlsm" in sheet "August".

 

If you don't want this to be dynamic, then use this formula:

=HYPERLINK("[Quotation Unit Price.xlsm]#August!A1","destination")

This will have the same end result as the previous formula but, you will need to modify, in the formula, "August" to whatever sheet you want to land in your workbook.

For example, if you want to land in the sheet named "June", your formula will look like this:

=HYPERLINK("[Quotation Unit Price.xlsm]#June!A1","destination")

Finally, the last "destination" in the formula is simply whatever will be presented on your sheet so you can type in whatever you want.

 

Highlighted

@Bennadeau 

Hi, Hell but you are an angel that is what i was looking for but could not figure it out, to use help you have to type just the right phrase to get help to give you more or less the correct info. I just could not figure out the #. Thank You so much

Regards

Highlighted

@kobus1305 

Using "hell" and "angel" in the same sentence, does that make me Lucifer? Probably the wrong discussion board for that question. Lol!

No problem, I'm glad we were able to find a solution.

Cheers!