Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mj
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mj
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mj
 
Posts: n/a
Default returning header row as a result...

Thanks a lot!!! it's working...

mj

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert number into words Blackwar Excel Discussion (Misc queries) 4 December 2nd 05 01:05 PM
I NEED HELP with the SPELLNUMBER Function vag Excel Worksheet Functions 0 June 21st 05 08:17 AM
EXCEL:NUMBER TO GREEK WORDS vag Excel Worksheet Functions 1 June 15th 05 05:57 PM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
Is there a formula to spell out a number in excel? Sha-nay-nay Excel Worksheet Functions 2 December 18th 04 10:25 PM


All times are GMT +1. The time now is 10:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"