Dynamic Formulas

[Guest]

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

 

[vezerid]

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

 

[Guest]

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!

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

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

 

[Don Guillett]

Assumes your a2 a number to start with. If a date then
mystring = Range("a1") & "_" & format(Range("a2"),"yyyymmdd")

Sub makestring()
mystring = Range("a1") & "_" & Range("a2")
MsgBox mystring
ActiveWorkbook.SaveAs Filename:="c:\a\" & mystring
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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!

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

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