Return a value from a row in Excel

[RFeniuk]

I am looking for some help in the formula to have Excel return the value from another sheet that is in the cell prior to a specific event.

i.e. return the value from the array "C1:Z1" of "Sheet 1" that is in the cell prior to the first occurance of "na"

I appreciate everyones help on this.

Thanks
Ron

 

[luceze]

Not sure exactly what you are looking for but this code will return the value of the cell next to "na" as in you example.
Sub FindCellValue()

Sheets("Sheet1&quot.Select
Range("C1:Z1&quot.Select

Selection.Find(What:="na", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
MsgBox ActiveCell.Offset(0, -1)


End Sub

Just change "Sheet1" to the sheet that your data is in.

HTH,
Eric

 

[SkipVought]

Hi,

Here's a spreadsheet formula, as you requested...

=INDEX(MyRange,1,MATCH("na",MyRange,0)-1)

where MyRange is C1:Z1

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Try this:
[blue]

  =INDEX(Sheet1!C1:Z1,1,MATCH("na",Sheet1!C1:Z1,0)-1)

[/color]

 

[RFeniuk]

Thank you very much to Zathras and Skip for your advice. That is exactly what I was looking for. A big star to both of you.

Ron