So I've made up a concatenated formula from several entries in a few different cells on one worksheet.
I get the exact text I need in the resulting cell, but I cannot for the life of me, convert that to a formula to pull data from the cell in question.
B1 is a name
D1 is a date
I've used an Eval Macro described online:
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
I've used Indirect against the cell with the formula in it
I've used Concatenate
Nothing will let me talk to a cell in a (either open or closed, because I know the rule of a closed workbook and the indirect function) to pull the ACTUAL formula for use ..
To be clear, I have the following:
='https://teams.foo-bar.com/sites/GSATGS/secserv/ops/REGION 3/Inventory Sheets/Preston, Brian/[Preston, Brian Inventory Sheet 2-1-2017.xlsx]EQUIPMENT'!E5
This works, with the workbook fully closed.
When I use the concatenate or an indirect, the cell GIVES ME that text, but not the value of the cell I'm looking for.
Concatenate looks like this:
="='https://teams.foo-bar.com/sites/GSATGS/secserv/ops/REGION 3/Inventory Sheets/" & B$1 & "/[" & B$1 & " Inventory Sheet " & TEXT(D$1, "M-D-YYYY")& ".xlsx]EQUIPMENT'!E5"
The indirect looks like this:
=INDIRECT("AF2")&INDIRECT("AF4")&"/["&INDIRECT("AF4")&INDIRECT("AF5")& TEXT(D1, "M-D-YYYY")&INDIRECT("AF7")
(Not important but the AF's all are individual parts of the https://team.foo-bar.com, etc.... Again, it fully displays the formula, not the evaluation of the formula.
I've pulled my hair out for hours trying overnight last night. I can't use an add-on. My options are VBA or a build-in function. This sheet gets sent to 150 people and I can't expect them to have morefunc or the like, added on.
Thanks in advance!
I get the exact text I need in the resulting cell, but I cannot for the life of me, convert that to a formula to pull data from the cell in question.
B1 is a name
D1 is a date
I've used an Eval Macro described online:
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
I've used Indirect against the cell with the formula in it
I've used Concatenate
Nothing will let me talk to a cell in a (either open or closed, because I know the rule of a closed workbook and the indirect function) to pull the ACTUAL formula for use ..
To be clear, I have the following:
='https://teams.foo-bar.com/sites/GSATGS/secserv/ops/REGION 3/Inventory Sheets/Preston, Brian/[Preston, Brian Inventory Sheet 2-1-2017.xlsx]EQUIPMENT'!E5
This works, with the workbook fully closed.
When I use the concatenate or an indirect, the cell GIVES ME that text, but not the value of the cell I'm looking for.
Concatenate looks like this:
="='https://teams.foo-bar.com/sites/GSATGS/secserv/ops/REGION 3/Inventory Sheets/" & B$1 & "/[" & B$1 & " Inventory Sheet " & TEXT(D$1, "M-D-YYYY")& ".xlsx]EQUIPMENT'!E5"
The indirect looks like this:
=INDIRECT("AF2")&INDIRECT("AF4")&"/["&INDIRECT("AF4")&INDIRECT("AF5")& TEXT(D1, "M-D-YYYY")&INDIRECT("AF7")
(Not important but the AF's all are individual parts of the https://team.foo-bar.com, etc.... Again, it fully displays the formula, not the evaluation of the formula.
I've pulled my hair out for hours trying overnight last night. I can't use an add-on. My options are VBA or a build-in function. This sheet gets sent to 150 people and I can't expect them to have morefunc or the like, added on.
Thanks in advance!
