Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 6th 05, 01:09 AM
 
Posts: n/a
Default inserting data from a row to a cell, when the row number is specified by a formula in a cell

in cell H3, I have a match formula that searches my spreadsheet for a
string of information. when it finds the row with the matching string
it displays the row number in cell H3 example '63'. In cell C3, I want
to display the full text that displays in the row specified in cell H3
'63'. I can do that by simply entering =r63 in the cell C3. I want
the info to display, what I would like to do is to automate this so I
do not manually have to enter =R63,for each cell. example of my
formulas

in Cell H3 the formula is =MATCH(I2,(Q$1:Q$30001),0) the result is 63
meaning row 63. in row 63 the text reads 'Byrd Brush'. I want cell C3
to display the text 'Byrd Brush' with out having to enter =R63, is
there a formula the will display the description of row 63
automatically? Thanks, JB


  #2   Report Post  
Old January 6th 05, 05:09 AM
Max
 
Posts: n/a
Default

One way is to use INDEX()

Put in C3: =INDEX(R:R,H3)
where in H3 is: =MATCH(I2,(Q$1:Q$30001),0)

Perhaps directly,

Put in C3: =INDEX(R:R,MATCH(I2,(Q$1:Q$30001),0))

--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----
wrote in message
ups.com...
in cell H3, I have a match formula that searches my spreadsheet for a
string of information. when it finds the row with the matching string
it displays the row number in cell H3 example '63'. In cell C3, I want
to display the full text that displays in the row specified in cell H3
'63'. I can do that by simply entering =r63 in the cell C3. I want
the info to display, what I would like to do is to automate this so I
do not manually have to enter =R63,for each cell. example of my
formulas

in Cell H3 the formula is =MATCH(I2,(Q$1:Q$30001),0) the result is 63
meaning row 63. in row 63 the text reads 'Byrd Brush'. I want cell C3
to display the text 'Byrd Brush' with out having to enter =R63, is
there a formula the will display the description of row 63
automatically? Thanks, JB



  #3   Report Post  
Old January 6th 05, 08:18 AM
Max
 
Posts: n/a
Default

Another play is to try INDIRECT() ?

Put in C3: =INDIRECT("R"&H3)
where in H3 is: =MATCH(I2,(Q$1:Q$30001),0)

--
Rgds
Max
xl 97
---
GMT+8, 1 22' N 103 45' E
xdemechanik <atyahoo<dotcom
----




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
how do I make a formula NOT change when the data range is moved? Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 10:02 PM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
How do I add a date formula to a cell but hide the contents with . Emzy Wemzy Excel Discussion (Misc queries) 2 December 12th 04 02:48 PM
Aligning Two Lists in Excel Rich Excel Discussion (Misc queries) 2 December 4th 04 06:44 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 12:29 PM


All times are GMT +1. The time now is 09:16 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017