Formula to reference a cell between two spreadsheets

[Trying2learnVBA]

S1 is my macro template.
S2 is the actual report which gets a new tab each month. Ex. T1-Jan T2-Feb- etc- to current month.

Sometimes there are changes to S2. So, I want a system to cross check if anything has changed.

Exaple.

S1 n S2 look identical unless a new loan type was added.

So - I want to do this simple formula but for S2 active tab-sheet.

Say I do this on S1 any cell of my choice "=A8=S2 active sheet A8"
S1 =A8="S2 A8" works but once I make a new tab - The formula references the old tab.

Thanks for the help in advance.

 

[James006]

Re: Help finding a formula to reference a cell between two spreadsheets

Hello,

Looks like you need to use Indirect() ...

see Debra's excellent explanation :

https://www.contextures.com/xlFunctions05.html#RefSheet

Hope this will help

 

[Trying2learnVBA]

Re: Help finding a formula to reference a cell between two spreadsheets

=A9='P:\MONTHEND\2019\[LNST2019.xls.xlsx]Aug 19'!A9

That's the simple formula. Is there a command/way to change "Aug 19" to be something like "Active Sheet"?

 

[Joe4]

Re: Help finding a formula to reference a cell between two spreadsheets

Is there a command/way to change "Aug 19" to be something like "Active Sheet"?

You would have to use the INDIRECT function, and build the entire reference.
You can use the method here to get the active sheet name: https://exceljet.net/formula/get-sheet-name-only
(Note that your workbook must be saved in order for this to work!)

So your final formula would look something like this:

=INDIRECT("'P:\MONTHEND\2019\[LNST2019.xls.xlsx]" [COLOR=#0000ff]& MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) &[/COLOR] "'!A9")

 

[Trying2learnVBA]

Re: Help finding a formula to reference a cell between two spreadsheets

I am sorry guys but I can't get it to work.

Do I need to make any changes to the Code given to me by Joe4?

 

[Joe4]

Re: Help finding a formula to reference a cell between two spreadsheets

What happens when you put that formula in the cell?
What does it return?

 

[Trying2learnVBA]

Re: Help finding a formula to reference a cell between two spreadsheets

it says "#REF!"

 

[Joe4]

Re: Help finding a formula to reference a cell between two spreadsheets

Sounds like it cannot find a file/sheet with that name.
Try the formula without the INDIRECT function wrapped around it, so you can see what the inner part returns, and confirm that it is correct, i.e.

="'P:\MONTHEND\2019\[LNST2019.xls.xlsx]" [COLOR=#0000ff]& MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) &[/COLOR] "'!A9"

Does it look exactly like?
='P:\MONTHEND\2019\[LNST2019.xls.xlsx]Aug 19'!A9

If you enter the formula above in a cell, do you get a result back or the #REF error?

 

[Trying2learnVBA]

Re: Help finding a formula to reference a cell between two spreadsheets

Ok, I tried the formula on the other sheet. It returned the number on that cell.
So, I guess I want the formula to work from the other wb.

Also - I wanted a simple true or false. True if = false if not equal. But returning the actual cell value can help me to verify the info. It would be easier n faster if it said true or false instead

 

[Joe4]

Re: Help finding a formula to reference a cell between two spreadsheets

Once you verify and get it working on the sheet you wanted using the methods I decribed in the previous thread, you can easily make it True/False by adding the first part you had originally, i.e.

[COLOR=#ff0000]=A9[/COLOR]=INDIRECT("'P:\MONTHEND\2019\[LNST2019.xls.xlsx]" [COLOR=#0000ff]& MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) &[/COLOR] "'!A9")