We run a template for creating a new project that has a folder structure with folder data card variables that cascade throughout the structure. All files in these foldes will have the project number as the first part of it's file name followed by the name of the form, ie. P123456-Estimates. I'm trying to create another excel form that will extract info from the estimate form. Normally I would type = and then select the correct cell in the estimate form. The cell would then have a formula looking like this ... ='[P123456-Estimate.xls]Summary'!$A$1. The problem is that this would only work for this particular estimate form. I was thinking that if I had the variable Project_Number already as a folder variable then I could just plug it into the formula as ... ='[%Project_Number%-Estimate.xls]Summary'!$A$1. Of course that won't work since the % is used in EPDM and not Office but not sure what I should use or if there is a better way of expressing this formula.
I hope this made sense and if it does then are there any suggestions?