Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to search a sheet using row and column headers
There may be an easy solution to this query as it seems quite simple but i'm
tying my brain in knots trying to figure it out! I have a table in a sheet which i want to populate with data from a table in another sheet. the table i want to populate has a range of age groups down the column and some different types of households (eg, 1 person male, 1 person female, 2 person 2 adult, 2 person 1 parent,etc) along the row. There is a cell above it for the year. The table i want to use to fill this one has the age ranges in column B, the household types in column C and the years from 2006 to 2031 along row 2. Ideally i would like to be able to write a formaula in the cells of the table i need to fill so that if i change the cell at the top of it with the date then all the data in the table will change accordingly. I dont mind putting in the complicated work just now if it will save me time in future years. Vlookup and Hlookup only let me search one row or column. I cant believe there is no way of searching a table using more than one variable but i cant figure it out! can anybody help with this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to search a sheet using row and column headers
I'm not sure exactly how your source table is laid out - where does
2006 start, and what is contained in the year columns? Is it, as I suspect, a summary table, and you just want to pick out one particular year's values and transpose them into another table for that year? Pete On May 9, 2:40*pm, shell96 wrote: There may be an easy solution to this query as it seems quite simple but i'm tying my brain in knots trying to figure it out! I have a table in a sheet which i want to populate with data from a table in another sheet. *the table i want to populate has a range of age groups down the column and some different types of households (eg, 1 person male, 1 person female, 2 person 2 adult, 2 person 1 parent,etc) along the row. *There is a cell above it for the year. The table i want to use to fill this one has the age ranges in column B, the household types in column C and the years from 2006 to 2031 along row 2. Ideally i would like to be able to write a formaula in the cells of the table i need to fill so that if i change the cell at the top of it with the date then all the data in the table will change accordingly. *I dont mind putting in the complicated work just now if it will save me time in future years. Vlookup and Hlookup only let me search one row or column. *I cant believe there is no way of searching a table using more than one variable but i cant figure it out! can anybody help with this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to search a sheet using row and column headers
Hi Pete,
below is an example of my source table as i cant get it to copy and paste very well. Basically i wanted a function that would check the year, age group and household type and return the number. i've tried some suggestions left on posts by others and have got it almost how i want but not quite. It seems happy enough to search more than one column but doesnt seem to like to look through both columns and rows so i'm starting to suspect maybe it just cant be done in which case i'll have to settle for what i've done so far. i can get it to search based on the column for one year only (rather than searching through the years) which i guess is easy enough to amend each year without much work. B C D E F G H 2006 2007 2008 2009 2010 age group household type 16-24 1 person female 0.2 0.4 0.1 0.1 0.6 16-24 1 person male 0.5 0.6 0.4 0.4 0.8 16-24 2 person 1 parent 0.2 0.4 0.1 0.1 0.6 25-29 1 person female 0.2 0.4 0.1 0.1 0.6 25-29 1 person male 0.3 0.4 0.8 0.5 0.6 25-29 2 person 1 parent 0.5 0.6 0.4 0.4 0.8 Thanks for your quick response :) "Pete_UK" wrote: I'm not sure exactly how your source table is laid out - where does 2006 start, and what is contained in the year columns? Is it, as I suspect, a summary table, and you just want to pick out one particular year's values and transpose them into another table for that year? Pete On May 9, 2:40 pm, shell96 wrote: There may be an easy solution to this query as it seems quite simple but i'm tying my brain in knots trying to figure it out! I have a table in a sheet which i want to populate with data from a table in another sheet. the table i want to populate has a range of age groups down the column and some different types of households (eg, 1 person male, 1 person female, 2 person 2 adult, 2 person 1 parent,etc) along the row. There is a cell above it for the year. The table i want to use to fill this one has the age ranges in column B, the household types in column C and the years from 2006 to 2031 along row 2. Ideally i would like to be able to write a formaula in the cells of the table i need to fill so that if i change the cell at the top of it with the date then all the data in the table will change accordingly. I dont mind putting in the complicated work just now if it will save me time in future years. Vlookup and Hlookup only let me search one row or column. I cant believe there is no way of searching a table using more than one variable but i cant figure it out! can anybody help with this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to search a sheet using row and column headers
Here's one possible approach,
illustrated in this sample: http://www.freefilehosting.net/download/3h27g Search Table.xls With selections/inputs for age group, hsehold type, year in A14:C14, Array-enter* in D14, copy down: =IF(COUNTA(A14:C14)<3,"",INDEX($C$3:$G$8,MATCH(1,( $A$3:$A$8=A14)*($B$3:$B$8=B14),0),MATCH(C14,$C$2:$ G$2,0))) *Press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference tabs in a summary sheet as column headers | Excel Discussion (Misc queries) | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
How to search column, copy row, and copy to another sheet in same | Excel Discussion (Misc queries) | |||
make column values column headers | Excel Discussion (Misc queries) | |||
Excel - returning column headers in a seperate column | Excel Discussion (Misc queries) |