Return Value from a Range (depends on input to determind month)

[Tony]

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!

 

[Roger Govier]

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.

 

[Tony]

Thanks Roger,
Took a little understanding, but managed to follow through.