Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 01-07-2009
Member
 
Join Date: Dec 2008
Posts: 112
How to get Excel forumla

I am seeking to get a cell reference (row and column) for the cell in which particular text is found. I need an excel formula to find the part of the text in the range of cells and display the values. Essentially, I would like to be able to do a vlookup() but instead of getting the value from a different column, I'd like to get the row number. How do you make the silly thing count to the next number....from 1 and the next time you open it up it will be on 2.
Reply With Quote
  #2  
Old 01-07-2009
Member
 
Join Date: Oct 2008
Posts: 167
Re: How to get Excel forumla

You can use the MATCH formula for that. It returns the position of the matched result on the searched range, so if you use the full column (or a range starting on the first row), it will return the row number where the match is. For example:
Quote:
=INDEX(A1,B:B,0);=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))
you can try to call the method with with an empty/dummy workbook reference, it MAY work for formulas without Names or 3D references, but be sure to catch all exceptions if you do that.
Reply With Quote
  #3  
Old 01-07-2009
Member
 
Join Date: Jan 2009
Posts: 143
Re: How to get Excel forumla

I used the following formula,It seems to be working but only to that cell if i try to put the same formula in another sheet or another cell ,I don't see the values.

My Formula is as follows.
=IF((OR(A1:A25="ARRA 123",A1:A25="NoN ARRA 123")),B1:B25,0)

If you want to copy a range of formulas without changing the references, you can use "Replace With", CNTRL H.
Reply With Quote
  #4  
Old 01-07-2009
Member
 
Join Date: Feb 2009
Posts: 105
Re: How to get Excel forumla

However, since formula's contain references to other parts of the file, you'll need a reference to a workbook to get a string representation. I'm not sure if you're interested at all, but I found out what the problem was. When you specify the formula with the dynamic range you must put the dynamic range in quotes so instead of having ActiveCell.Formula = "=MEDIAN(Patientrange)" you must have ActiveCell.Formula = "=MEDIAN(" & Patientrange & ")". But now you are saying that you want values in B so it means you want to see column A values in B then write below formula in B.
Reply With Quote
  #5  
Old 03-07-2009
BertieStan
 
Posts: n/a
Re: How to get Excel forumla

The best way to do this, assuming your explanation is not a vast over simplification of your real problem, would be with a pivot table.Select your range (columns must have unique labels) go to pivot table (data->pivot table in excel 2003) and put height on the left axis and count of height in the data area, click finish and that should do you.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to get Excel forumla"
Thread Thread Starter Forum Replies Last Post
Getting Error while opening Microsoft Excel 2000 file in Excel 2003 AbiaA MS Office Support 6 17-02-2012 01:18 PM
Cell format errors appears in Excel 2003 but not in Excel 2000 @nki$achi MS Office Support 1 24-01-2012 05:00 PM
Excel 2007 file fails to get permission to open in Excel 2011 Raju Chacha Windows Software 6 13-01-2012 09:17 PM
How to check data validation compatibility of excel 2010 on excel 2007 Zoello Windows Software 6 17-05-2011 10:00 PM
Configuring Internet Explorer 8 to open Excel file in to Microsoft Excel Aaghaz Technology & Internet 4 13-02-2011 10:37 AM


All times are GMT +5.5. The time now is 07:44 AM.