Dear All,
attached are two result and data source sheets. For this question I'll request your attention to the values highlighted red in the 'Result Sheet'. These values for each corresponding PI Name marked yellow are got from their corresponding tables in the 'Data Sheet'. The values are obtained with help from the offset+indirect+match combination formula below.
= OFFSET(INDIRECT("[Data_Sheet.xls]SAA_CBA!$C"&MATCH(B3,'C:\bryen\040510\[Data_Sheet.xls]SAA_CBA'!$A$1:$A$51,0)+1),0,0)
Can anyone help me apply this formula to all the cells using a macro? This would help avoid typing the formula in each cell.
Thx for all help...
bryen
Last edited by bryenwalt; 05-05-2010 at 04:28 AM. Reason: a clearer explanation...
Hi All, so far i have not recieved any help on this. After a considerable time I've found a workaround to this problem, which i should share.
in the offset formula, the double quotes need to be replaced with quadruple quotes, see below:
Formula as inputed directly into table cells:
= OFFSET(INDIRECT("[Data_Sheet.xls]SAA_CBA!$C"&MATCH(B3,'C:\bryen\040510\[Data_Sheet.xls]SAA_CBA'!$A$1:$A$51,0)+1),0,0)
Formula as inputed into table cells via a macro (change activecell to your required cell):
activecell.value= "=OFFSET(INDIRECT(" & """" & "[Data_Sheet.xls]SAA_CBA!$C" & """" & "&MATCH(B3,[Data_Sheet.xls]SAA_CBA!$A$1:$A$51,0)+1),0,0)"
Note that there is also no need for the URL.
Hope that helps someone.
A quick reason to the need for double quotes in the above solution:
the macro tries to dump the formula as a string, but gets an error when it encounters the second double quotes, after the word "INDIRECT(". We need to add other strings to our formula though, thus the need for: & """" &.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks