Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formulas
I am building a report template that pulls like data from different vendor
workbooks. I want to be able to create "Dynamic Formulas" that can "Read" what vendor I am working with and change based on that information. For example; Raw data may be contained in workbooks as follows: RAWDATA_Vend1.xls RAWDATA_Vend2.xls RAWDATA_Vend3.xls I am thinking that I would specify a location in the template to indicate which vendor I am working with, (i.e. Vend2) and that criteria could be used within formulas inside the worksheet, such as; =SUM(A1+'[RAWDATA_?????.xls]TabName'!$A$2) Where ????? = the content of my "criteria" cell. I would want this reference to be used not only to reference external locations, as described above, but also internally to: Change report headers Use in File Save As / In conjunction with a date Change out images (if possible?!) I don't even know if this type of stuff is possible, but it would make my life sooo much easier as now I am modifying multiple templates every time I have to implement a change. A single template would be much better! Thanks in advance! Ray |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formulas
You will need the INDIRECT function. For example, if filename is in A1
and you know you will always refer to sheet Sales in any workbook and seek cell F3, then =INDIRECT("'C:\Myfolder\["&A1&"]Sales'!F3") Note the single quotes needed in such cases. Caveat: the workbook must be open or you get the #REF! error. HTH Kostis Vezerides On Jul 6, 4:28 pm, RayportingMonkey wrote: I am building a report template that pulls like data from different vendor workbooks. I want to be able to create "Dynamic Formulas" that can "Read" what vendor I am working with and change based on that information. For example; Raw data may be contained in workbooks as follows: RAWDATA_Vend1.xls RAWDATA_Vend2.xls RAWDATA_Vend3.xls I am thinking that I would specify a location in the template to indicate which vendor I am working with, (i.e. Vend2) and that criteria could be used within formulas inside the worksheet, such as; =SUM(A1+'[RAWDATA_?????.xls]TabName'!$A$2) Where ????? = the content of my "criteria" cell. I would want this reference to be used not only to reference external locations, as described above, but also internally to: Change report headers Use in File Save As / In conjunction with a date Change out images (if possible?!) I don't even know if this type of stuff is possible, but it would make my life sooo much easier as now I am modifying multiple templates every time I have to implement a change. A single template would be much better! Thanks in advance! Ray |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Formulas
This is very helpful and has pointed me in the right direction BUT, I am
still trying to work out two detials... 1) Incorporating the contents of a cell into a Save As filename (may need to write a macro to do this?) IE. Cell A1=FFF Cell A2=YYYMMDD Cell A3 = concatonate as FFF_YYYMMDD 2) I have to use different corporate logos in my reports. Is there a way to specify which logo image would load based on cell content? Again, thanks! "vezerid" wrote: You will need the INDIRECT function. For example, if filename is in A1 and you know you will always refer to sheet Sales in any workbook and seek cell F3, then =INDIRECT("'C:\Myfolder\["&A1&"]Sales'!F3") Note the single quotes needed in such cases. Caveat: the workbook must be open or you get the #REF! error. HTH Kostis Vezerides On Jul 6, 4:28 pm, RayportingMonkey wrote: I am building a report template that pulls like data from different vendor workbooks. I want to be able to create "Dynamic Formulas" that can "Read" what vendor I am working with and change based on that information. For example; Raw data may be contained in workbooks as follows: RAWDATA_Vend1.xls RAWDATA_Vend2.xls RAWDATA_Vend3.xls I am thinking that I would specify a location in the template to indicate which vendor I am working with, (i.e. Vend2) and that criteria could be used within formulas inside the worksheet, such as; =SUM(A1+'[RAWDATA_?????.xls]TabName'!$A$2) Where ????? = the content of my "criteria" cell. I would want this reference to be used not only to reference external locations, as described above, but also internally to: Change report headers Use in File Save As / In conjunction with a date Change out images (if possible?!) I don't even know if this type of stuff is possible, but it would make my life sooo much easier as now I am modifying multiple templates every time I have to implement a change. A single template would be much better! Thanks in advance! Ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Evaluate Dynamic DDE Formulas | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic formulas including worksheet references | Excel Worksheet Functions | |||
dynamic formulas | Excel Discussion (Misc queries) | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions |