Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
hello guys,
i just want to know what function will i use to return a header row as a result. I have a worksheet consisting of lists of images arranged in columns by subfolder; the name of the subfolder serves as my header. Now, i have another list of images and i want to crosscheck this list to my worksheet to know where these images are located or in what subfolder/s are they included. my table looks like this: 1 Sub1 Sub2 ... Sub20 (header row) 2 img1 img101 img201 . . . . . . img100 img200 img300 what i want is: if i search for img150...the result would be Sub2(header row) please help me... thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Assumptions:
A1:C1 contains your headers A2:C10 contains your data E2 contains the image of interest, such as img150 Formula: =INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,COL UMN(A2:C10)-COLUMN(A2), ,1),E2)0,0)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article .com, "mj" wrote: hello guys, i just want to know what function will i use to return a header row as a result. I have a worksheet consisting of lists of images arranged in columns by subfolder; the name of the subfolder serves as my header. Now, i have another list of images and i want to crosscheck this list to my worksheet to know where these images are located or in what subfolder/s are they included. my table looks like this: 1 Sub1 Sub2 ... Sub20 (header row) 2 img1 img101 img201 . . . . . . img100 img200 img300 what i want is: if i search for img150...the result would be Sub2(header row) please help me... thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
sir,
i tried to make a dummy table to try the formula you gave me, however, it returns the wrong header. when i search for 150, it returns header "sub3" instead of "sub2".i wonder what seems to be the problem? sub1 sub2 sub3 1 158 2 3 146 6 5 197 8 100 150 10 121 136 11 130 147 9 56 12 21 14 35 26 19 36 28 {=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,CO LUMN(A2:C10)-COLUMN(A2),1),E2)0,0))} |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
There's a comma missing in your formula. This part of the formula...
OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),1) should be OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),,1) Hope this helps! In article .com, "mj" wrote: sir, i tried to make a dummy table to try the formula you gave me, however, it returns the wrong header. when i search for 150, it returns header "sub3" instead of "sub2".i wonder what seems to be the problem? sub1 sub2 sub3 1 158 2 3 146 6 5 197 8 100 150 10 121 136 11 130 147 9 56 12 21 14 35 26 19 36 28 {=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,CO LUMN(A2:C10)-COLUMN(A2),1),E 2)0,0))} |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Does not work either. I wonder why!
-- AP "Domenic" a écrit dans le message de ... There's a comma missing in your formula. This part of the formula... OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),1) should be OFFSET(A2:C10,,COLUMN(A2:C10)-COLUMN(A2),,1) Hope this helps! In article .com, "mj" wrote: sir, i tried to make a dummy table to try the formula you gave me, however, it returns the wrong header. when i search for 150, it returns header "sub3" instead of "sub2".i wonder what seems to be the problem? sub1 sub2 sub3 1 158 2 3 146 6 5 197 8 100 150 10 121 136 11 130 147 9 56 12 21 14 35 26 19 36 28 {=INDEX(A1:C1,MATCH(TRUE,COUNTIF(OFFSET(A2:C10,,CO LUMN(A2:C10)-COLUMN(A2),1) ,E 2)0,0))} |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
In article ,
"Ardus Petus" wrote: Does not work either. I wonder why! Are you getting an incorrect result? Or are you getting an error value? Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, enter the formula but instead of pressing just ENTER hold the CONTROL and SHIFT keys down, then while they're pressed down, press the ENTER key. Excel will automatically place braces {} around the formula indicating that you've entered the formula correctly. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Incorrect result.
Here is my sample sheet: http://cjoint.com/?desg77Wwr1 HTH -- AP "Domenic" a écrit dans le message de ... In article , "Ardus Petus" wrote: Does not work either. I wonder why! Are you getting an incorrect result? Or are you getting an error value? Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, enter the formula but instead of pressing just ENTER hold the CONTROL and SHIFT keys down, then while they're pressed down, press the ENTER key. Excel will automatically place braces {} around the formula indicating that you've entered the formula correctly. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
The 'match type' or third argument for the MATCH function is missing
and, as a result, defaults to 1. You need to set it to 0 for an exact match. Also, the 'height' specified for the OFFSET function will add a row to your reference/range, i.e. A2:D11. So, your formula should be amended as follows... =INDEX(A1:D1,MATCH(1,COUNTIF(OFFSET(A2:D10,,COLUMN (A:D)-COLUMN(A:A),9,1), $F$2),0)) But, personally, I prefer the following... =INDEX(A1:D1,MATCH(TRUE,COUNTIF(OFFSET(A2:D10,,COL UMN(A2:D10)-COLUMN(A2), ,1),$F$2)0,0)) Two reasons: 1) If by mistake, there's a duplicate entry for one of the columns, the formula will still return a correct result. 2) By omitting the '9' and leaving the 'height' for the OFFSET function empty, it automatically defaults to the height of the reference, i.e. A2:D10 --- 9. 3) Personally, I think it looks better. But to each his/her own... :) Hope this helps! In article , "Ardus Petus" wrote: Incorrect result. Here is my sample sheet: http://cjoint.com/?desg77Wwr1 HTH -- AP "Domenic" a écrit dans le message de ... In article , "Ardus Petus" wrote: Does not work either. I wonder why! Are you getting an incorrect result? Or are you getting an error value? Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, enter the formula but instead of pressing just ENTER hold the CONTROL and SHIFT keys down, then while they're pressed down, press the ENTER key. Excel will automatically place braces {} around the formula indicating that you've entered the formula correctly. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Still does not work!
-- AP "Domenic" a écrit dans le message de ... The 'match type' or third argument for the MATCH function is missing and, as a result, defaults to 1. You need to set it to 0 for an exact match. Also, the 'height' specified for the OFFSET function will add a row to your reference/range, i.e. A2:D11. So, your formula should be amended as follows... =INDEX(A1:D1,MATCH(1,COUNTIF(OFFSET(A2:D10,,COLUMN (A:D)-COLUMN(A:A),9,1), $F$2),0)) But, personally, I prefer the following... =INDEX(A1:D1,MATCH(TRUE,COUNTIF(OFFSET(A2:D10,,COL UMN(A2:D10)-COLUMN(A2), ,1),$F$2)0,0)) Two reasons: 1) If by mistake, there's a duplicate entry for one of the columns, the formula will still return a correct result. 2) By omitting the '9' and leaving the 'height' for the OFFSET function empty, it automatically defaults to the height of the reference, i.e. A2:D10 --- 9. 3) Personally, I think it looks better. But to each his/her own... :) Hope this helps! In article , "Ardus Petus" wrote: Incorrect result. Here is my sample sheet: http://cjoint.com/?desg77Wwr1 HTH -- AP "Domenic" a écrit dans le message de ... In article , "Ardus Petus" wrote: Does not work either. I wonder why! Are you getting an incorrect result? Or are you getting an error value? Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, enter the formula but instead of pressing just ENTER hold the CONTROL and SHIFT keys down, then while they're pressed down, press the ENTER key. Excel will automatically place braces {} around the formula indicating that you've entered the formula correctly. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Works for me. (using your sample file)
Biff "Ardus Petus" wrote in message ... Still does not work! -- AP "Domenic" a écrit dans le message de ... The 'match type' or third argument for the MATCH function is missing and, as a result, defaults to 1. You need to set it to 0 for an exact match. Also, the 'height' specified for the OFFSET function will add a row to your reference/range, i.e. A2:D11. So, your formula should be amended as follows... =INDEX(A1:D1,MATCH(1,COUNTIF(OFFSET(A2:D10,,COLUMN (A:D)-COLUMN(A:A),9,1), $F$2),0)) But, personally, I prefer the following... =INDEX(A1:D1,MATCH(TRUE,COUNTIF(OFFSET(A2:D10,,COL UMN(A2:D10)-COLUMN(A2), ,1),$F$2)0,0)) Two reasons: 1) If by mistake, there's a duplicate entry for one of the columns, the formula will still return a correct result. 2) By omitting the '9' and leaving the 'height' for the OFFSET function empty, it automatically defaults to the height of the reference, i.e. A2:D10 --- 9. 3) Personally, I think it looks better. But to each his/her own... :) Hope this helps! In article , "Ardus Petus" wrote: Incorrect result. Here is my sample sheet: http://cjoint.com/?desg77Wwr1 HTH -- AP "Domenic" a écrit dans le message de ... In article , "Ardus Petus" wrote: Does not work either. I wonder why! Are you getting an incorrect result? Or are you getting an error value? Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, enter the formula but instead of pressing just ENTER hold the CONTROL and SHIFT keys down, then while they're pressed down, press the ENTER key. Excel will automatically place braces {} around the formula indicating that you've entered the formula correctly. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
In article ,
"Ardus Petus" wrote: Still does not work! Would you like me to send you a sample file? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Here is my file: http://cjoint.com/?devmAKL3mn
-- AP "Domenic" a écrit dans le message de ... In article , "Ardus Petus" wrote: Still does not work! Would you like me to send you a sample file? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Sorry I misunderstood: yes, please send me a sample file.
-- AP "Domenic" a écrit dans le message de ... In article , "Ardus Petus" wrote: Still does not work! Would you like me to send you a sample file? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
It returns "sub4" for all items in col sub1
Windows 2000 + Office 2000 HTH -- AP "Domenic" a écrit dans le message de ... The 'match type' or third argument for the MATCH function is missing and, as a result, defaults to 1. You need to set it to 0 for an exact match. Also, the 'height' specified for the OFFSET function will add a row to your reference/range, i.e. A2:D11. So, your formula should be amended as follows... =INDEX(A1:D1,MATCH(1,COUNTIF(OFFSET(A2:D10,,COLUMN (A:D)-COLUMN(A:A),9,1), $F$2),0)) But, personally, I prefer the following... =INDEX(A1:D1,MATCH(TRUE,COUNTIF(OFFSET(A2:D10,,COL UMN(A2:D10)-COLUMN(A2), ,1),$F$2)0,0)) Two reasons: 1) If by mistake, there's a duplicate entry for one of the columns, the formula will still return a correct result. 2) By omitting the '9' and leaving the 'height' for the OFFSET function empty, it automatically defaults to the height of the reference, i.e. A2:D10 --- 9. 3) Personally, I think it looks better. But to each his/her own... :) Hope this helps! In article , "Ardus Petus" wrote: Incorrect result. Here is my sample sheet: http://cjoint.com/?desg77Wwr1 HTH -- AP "Domenic" a écrit dans le message de ... In article , "Ardus Petus" wrote: Does not work either. I wonder why! Are you getting an incorrect result? Or are you getting an error value? Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. In other words, enter the formula but instead of pressing just ENTER hold the CONTROL and SHIFT keys down, then while they're pressed down, press the ENTER key. Excel will automatically place braces {} around the formula indicating that you've entered the formula correctly. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Sample file sent...
In article , "Ardus Petus" wrote: Sorry I misunderstood: yes, please send me a sample file. -- AP |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Thanks, it works!
Sorry for the annoyance... -- AP "Domenic" a écrit dans le message de ... Sample file sent... In article , "Ardus Petus" wrote: Sorry I misunderstood: yes, please send me a sample file. -- AP |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
In article ,
"Ardus Petus" wrote: Thanks, it works! You're very welcome! Glad I could help! Sorry for the annoyance... No problem at all... :) Cheers! |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
returning header row as a result...
Thanks a lot!!! it's working...
mj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert number into words | Excel Discussion (Misc queries) | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
Is there a formula to spell out a number in excel? | Excel Worksheet Functions |