Prev: sum if one or another criteria
Next: Additonal info into newly created Subtotal row
From: Tony on 31 Mar 2010 10:58
Thanks Roger,
Took a little understanding, but managed to follow through.

"Roger Govier" wrote:

> Hi Tony
>
> I don't think you need all those range names!!!
> How is your sheet set out?
> Is it like the following?
> Jan
> Salt
> BP 100
> BV 20
> AV 21
> AP 95
> PV -5
> VV 1
> Pepper
> BP 50
> BV 2
> AV 1.5
> AP 55
> PV -0.5
> VV 5
>
> In other words, is there a blank row for the product Name, followed by 6
> rows of data for the various measures? If so, then on Sheet 2 set it up as
> Product Month BP BV AV AP PV VV
>
> Enter product name in A2 e.g Salt
> Enter Month name in B2 e.g. Mar
> then in C2 enter
>
> =IF(COUNTA(A2:B2)<>2,"",INDEX(Sheet1!$1:$65536,
> MATCH(Sheet2!$A2,Sheet1!$A:$A,0)
> +COLUMN(A1),MATCH(Sheet2!$B2,Sheet1!$1:$1,0)))
>
> and copy across and down as required
>
> If you don't have a blank row for the generic product, but start off
> with Budget price etc. as below
> Salt BP
> Salt BV
> Salt AV
> etc.
>
> then use the following formula
> =IF(COUNTA($A2:$B2)<>2,"",INDEX(Sheet1!$1:$65536,
> MATCH(Sheet2!$A2&"*",Sheet1!$A:$A,0)+COLUMN(A1)-1,
> MATCH(Sheet2!$B2,Sheet1!$1:$1,0)))
>
> In each case the formula would be all on one line, but I have split it
> so the newsreader doesn't break it in odd places.
> --
> Regards
> Roger Govier
>
> Tony wrote:
> > I have a workbook set up with hundreds of single row range names with the
> > columns showing January to December. Each row is a different Chemical.
> > What I need to do is have the chemicals listed on a different sheet with
> > columns showing various data. The formula needs to pull in the month being
> > questioned, then populate the relevant columns with the corresponding data
> > from the range name.
> >
> > ie Salt may have range names for the following data by month.
> > Budget Price, Budget Volume, Actual Price, Actual Volume, Price Variance,
> > Volume Variance, (these are by row and months by columns).
> >
> > On a different sheet I want salt on a row with 1 months data for categories
> > above to be displayed by column. If possible 1 variable cell to change the
> > month.
> >
> > The rest of the workbook finds values by month because the sheets are
> > identical in their column discipline, but this is not the case on the front
> > summary.
> >
> > Hope this is clear!
> .
>
 | 
Pages: 1
Prev: sum if one or another criteria
Next: Additonal info into newly created Subtotal row