Dynamic formula creation and p;acement
 

Hi Prasad -

See my reply to your previous post on this subject. It looks like some of
your parameters have changed a bit since your original post, but the code
posted there should still apply with slight modifications.

Jay


"EE" wrote:

Hi

I need some macro help. There are two components to my problem.

1. Match Cells to Identify Target Column

In Sheet "Menu", I enter a date in cell "C3". Lets call it DATE.

In Sheet "Metrics", I have a Table Similar to the one below

Date1 Date 2 Date 3 ........
A
B
C
D
E

I am currently using a MATCH function to identify the column in
Metrics where DATE = Date "n". Lets call this "TargetColumn"

2. Creating a Dynamic Formula

The second part of the job is to create a dynamic formula. In rows
2,3,4 in the "TargetColumn" identified above, I need to insert a
VLOOKUP formula.

The look-up value is a concatenation of "Column A and Row
number(2,3,and 4)" & "Row 1 of Target Column". Lets call this
"LookupValue" (and the first part of lookupvalue will be different by
Row). As an example if TargetColumn is Column R, Lookupvalue for Row 2
will be A2&" "&R1.

The formula is = Vlookup(LookupValue, NamedRange(Pre-defined),2,FALSE)
and the formula needs to be pasted in row 2, 3 and 4 of target
column..

If any part of this is not understood, please reach out to me. I
actually did it for one row by hardcoding the Target Column, but I
need to make this dyanmic.

Best
Prasad