Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Value from a Range (depends on input to determind month)
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Value from a Range (depends on input to determind month)
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Value from a Range (depends on input to determind month
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! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return value matching vertical and horizontal input | Excel Worksheet Functions | |||
List Box - For Input Range can I use named range in another workbo | Excel Worksheet Functions | |||
Return a value from a table using Column and row names as input? | Excel Discussion (Misc queries) | |||
Find and input value based on month | Excel Discussion (Misc queries) | |||
Date the input, then tell if over 7th off month | Excel Worksheet Functions |