Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection of row and column based on input
I have a problem. I am trying to get a function to return a value from a
table on a different worksheet. Here is a small example of my spreadsheet: Table with Data Keyed in called Input Sheet: May-06 Jun-06 Jul-06 Aug-06 Rev S&W T&E Promo My Output sheet is called By Month. The column and row headings are the same. What I am looking to do is have the output sheet find the data on the input sheet based on what month I specify. For instance, on the output sheet, cell A3 is where I put in what month I want the formula to reference. So if I type in Jan-06, I want the formula to see that and return the value where Rev interesects with Jan-06 from the input sheet. Hopefully you can help me out. -- Thanks, Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection of row and column based on input
You'll probably need to use VLOOKUP and MATCH to get this to work. I don't
have time to describe it more. Maybe someone else will. "CMWalsh" wrote: I have a problem. I am trying to get a function to return a value from a table on a different worksheet. Here is a small example of my spreadsheet: Table with Data Keyed in called Input Sheet: May-06 Jun-06 Jul-06 Aug-06 Rev S&W T&E Promo My Output sheet is called By Month. The column and row headings are the same. What I am looking to do is have the output sheet find the data on the input sheet based on what month I specify. For instance, on the output sheet, cell A3 is where I put in what month I want the formula to reference. So if I type in Jan-06, I want the formula to see that and return the value where Rev interesects with Jan-06 from the input sheet. Hopefully you can help me out. -- Thanks, Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection of row and column based on input
Assumptions:
Input!B1:E1 contains the date Input!A2:A5 contains the category, such as Rev, S&W, etc. 'By Month'!A3 contains the date of interest 'By Month'!B3 contains the category of interest Formula: On your sheet named 'By Month'... =INDEX(Input!B2:E5,MATCH(B3,Input!A2:A5,0),MATCH(A 3,Input!B1:E1,0)) Hope this helps! In article , CMWalsh wrote: I have a problem. I am trying to get a function to return a value from a table on a different worksheet. Here is a small example of my spreadsheet: Table with Data Keyed in called Input Sheet: May-06 Jun-06 Jul-06 Aug-06 Rev S&W T&E Promo My Output sheet is called By Month. The column and row headings are the same. What I am looking to do is have the output sheet find the data on the input sheet based on what month I specify. For instance, on the output sheet, cell A3 is where I put in what month I want the formula to reference. So if I type in Jan-06, I want the formula to see that and return the value where Rev interesects with Jan-06 from the input sheet. Hopefully you can help me out. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection of row and column based on input
I tried these prior to posting in the group.
-- Thanks, Chris "Barb Reinhardt" wrote: You'll probably need to use VLOOKUP and MATCH to get this to work. I don't have time to describe it more. Maybe someone else will. "CMWalsh" wrote: I have a problem. I am trying to get a function to return a value from a table on a different worksheet. Here is a small example of my spreadsheet: Table with Data Keyed in called Input Sheet: May-06 Jun-06 Jul-06 Aug-06 Rev S&W T&E Promo My Output sheet is called By Month. The column and row headings are the same. What I am looking to do is have the output sheet find the data on the input sheet based on what month I specify. For instance, on the output sheet, cell A3 is where I put in what month I want the formula to reference. So if I type in Jan-06, I want the formula to see that and return the value where Rev interesects with Jan-06 from the input sheet. Hopefully you can help me out. -- Thanks, Chris |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection of row and column based on input
Domenic,
This seems as if this should have worked. However, I am still geting the N/A error message. -- Thanks, Chris "Domenic" wrote: Assumptions: Input!B1:E1 contains the date Input!A2:A5 contains the category, such as Rev, S&W, etc. 'By Month'!A3 contains the date of interest 'By Month'!B3 contains the category of interest Formula: On your sheet named 'By Month'... =INDEX(Input!B2:E5,MATCH(B3,Input!A2:A5,0),MATCH(A 3,Input!B1:E1,0)) Hope this helps! In article , CMWalsh wrote: I have a problem. I am trying to get a function to return a value from a table on a different worksheet. Here is a small example of my spreadsheet: Table with Data Keyed in called Input Sheet: May-06 Jun-06 Jul-06 Aug-06 Rev S&W T&E Promo My Output sheet is called By Month. The column and row headings are the same. What I am looking to do is have the output sheet find the data on the input sheet based on what month I specify. For instance, on the output sheet, cell A3 is where I put in what month I want the formula to reference. So if I type in Jan-06, I want the formula to see that and return the value where Rev interesects with Jan-06 from the input sheet. Hopefully you can help me out. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Intersection of row and column based on input
If you'd like, I can take a look at your file. Or, if you'd prefer, I
can email you a sample file... In article , CMWalsh wrote: Domenic, This seems as if this should have worked. However, I am still geting the N/A error message. -- Thanks, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|