PDA

View Full Version : Refernce formula and formatting



dspdanpcl
02-13-2013, 06:23 PM
HI there,

First time poster.

I am using an offset function with two match functions inside it to reference a particular position on a spreadsheet based on a stock code and a Factor I want to look at.

=OFFSET('Sheet 2'!$b$1 , MATCH('Sheet 1'!"Stock Code",'Sheet 2'!$A$2:$A$360,0) , MATCH('Sheet 1'!"Factor",'Sheet 2'!$c$1:$hr$1,0))

This formula is located in cells, 'Sheet 1'!h4:k92

My problem arises as I have 3 different formats, %, # and $ in my reference range. The cell I have the formula in is referencing the correct cell which gives me the correct value but can not pull through the format from the refrenced cell and I am wondering if / how this can be done. Conditional formatting does not work obviously and I know it will end up having to be complteted in vba but I can not think as to how this would work.

Any help would be much appreciated

dspdanpcl
02-13-2013, 07:07 PM
Solved it myself,

I gave each factor a heading of n , p or d (number, % or $) and used the initial offset function to reference the code, then used conditional formatting.