determine which cell a value is returned from

How to determine which cell a value is returned from.  e.g.=MAX(D6:CC280) 
returns 525 - How do I find the cell where it occurs?
0
curiousg (1)
2/9/2005 4:23:11 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
621 Views

Similar Articles

[PageSpeed] 53

Hi

one way
=CELL("address",INDEX(D6:CC280,MATCH(MAX(D6:CC280),D6:CC280,0)))

Cheers
JulieD

"curiousg" <curiousg@discussions.microsoft.com> wrote in message 
news:07BEB420-D231-4A8A-A320-981F9F09F7C1@microsoft.com...
> How to determine which cell a value is returned from.  e.g.=MAX(D6:CC280)
> returns 525 - How do I find the cell where it occurs? 


0
JulieD1 (2295)
2/9/2005 4:28:06 PM
One way:

=ADDRESS(MAX(IF(MAX(D6:CC280)=D6:CC280,ROW(D6:CC280))),MAX
(IF(MAX(D6:CC280)=D6:CC280,COLUMN(D6:CC280))),4)

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>How to determine which cell a value is returned from.  
e.g.=MAX(D6:CC280) 
>returns 525 - How do I find the cell where it occurs?
>.
>
0
jasonjmorin (551)
2/9/2005 4:40:35 PM
Jason,
That works great!  The "array-entered" hint is especially appreciated.

"Jason Morin" wrote:

> One way:
> 
> =ADDRESS(MAX(IF(MAX(D6:CC280)=D6:CC280,ROW(D6:CC280))),MAX
> (IF(MAX(D6:CC280)=D6:CC280,COLUMN(D6:CC280))),4)
> 
> Array-entered, meaning press ctrl + shift + enter.
> 
> HTH
> Jason
> Atlanta, GA
> 
> >-----Original Message-----
> >How to determine which cell a value is returned from.  
> e.g.=MAX(D6:CC280) 
> >returns 525 - How do I find the cell where it occurs?
> >.
> >
> 
0
Garret1 (1)
2/9/2005 7:15:02 PM
Reply:

Similar Artilces:

cell automatically turns colour??
Dear Excel users, I have a spreadsheet containing names and dates......i would like to know if there;s any formula for the below: Let's say.......2 weeks before a due date....the cell with a name on it will turn "orange" colour.....2 week after.... on the due date....the cell will auto change to "red" colour..... we would need this notification very urgently ...... Pls advice me....Thank you very much.... see conditional formatting - set up a formual comapring today() to the due date "Kelly Lim" wrote: > Dear Excel users, > ...

How to create a scatter chart with 2 "X" values with common "Y"s
I Need to create a chart with lots of horizontal curves at different Y values. there are 128 to be precise and I would prefer not to create each as an individual series unless ... is there a way to copy a series mapping it tonew cells? (There are also a few diagonal curves so a bar chart wont work.) Thanks! Just copy the cells from your spreadsheet that contain your data series, then select the chart, Edit/ Paste Special, and choose the relevant options, including "new series". -- David Biddulph "M_LeDuc" <M_LeDuc@discussions.microsoft.com> wrote in message...

Open form based on combo box value w/error message for blank combo box
I am working in an Access 2000 database that I've inherited. I'm trying to clean up some of the non-working functions. I am trying to open a form using the OnClick property for a button. On a job order form, there is an unbound combo box [CmbContactID] that gets its list from querying Table!ContactInformation.[CustomerCodes]. When populated, [CmbContactID] should provide the value to open FrmContactInformation when [CmdOpenContactForm] (a button) is clicked. I want an error message to display if the field is blank otherwise it will open the form for the customer code that is displaye...

determine mail flow problem!!!!!!!!
Help! :-( Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the outbound queue (which is set to always run delivery). Messages stay in there from 10 to 120 minutes before being sent to our gateway MTA and then sent off site (to hotmail.com for example). However when I telnet from a backend server to the gateway over port 25 and send a test message it goes right away. I can't for the life of me figure out why. One example when viewed in message tracking history says: 10:16am - smtp: store driver, message submitted from store 10:16am - smtp: message submitted to ...

cell with only a single quote
I need to delete rows with cells containing only single quotation marks. How can I replace the single quotation mark with a blank so FIND/GO TO/SPECIAL/BLANKS can find them? Or is there a better way? P.S.: The single quote is displayed in the Formula Bar but not in the cell. >-----Original Message----- >I need to delete rows with cells containing only single >quotation marks. How can I replace the single quotation >mark with a blank so FIND/GO TO/SPECIAL/BLANKS can find >them? Or is there a better way? > > >. > Hi gary Assuming you want to delete al...

Determine columns used
I have 100+ spreadsheets which i have to edit into a certain format. The spreadsheets currently have 100 columns (from 1-100). From there i have to reduce the number of columns set to 8 columns. E.g: 1 2 3 4 5 6 7 8 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 (from column 9 onwards, it goes to the 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 next rows' column 1) 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 How do i do that without altering the order of the value of the cell? Let me make sure we understand the question. You have ...

Chart Axes Linked to cell values
Hello Everyone. I have some sort of a problem with linking chart axe (min and max values) to cell values in a worksheet. I am using th macro that I found in Mr Peltier' site: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html It worked just fine for a simple test I was doing.. BUT, I came up wit the idea of assigning to the cells (the cells that contain the scal params) some worksheet functions, to determine the values from a set o data. When I did this, the macro didn't work anymore. It didn't pop u any errors or such things, it just didn't adjust the chart anymo...

MsQuery returning one record + one empty record
Hi all, In the same excel file, i have one sheet containing data and one running a MSQuery to retrive some of the data with a parameter. The problem is that when only one record should be returned, I have that record and a blank record. Because I have formulas that are auto-extended, I have two lines of formula. In MsQuery UI, there's only one record. I hope this is clear, Thanks Pierre-André ...

! removing initials and titles from name cell !
Hi, I'm sure what I'm asking must have a simple solution, but I can't work out how to achieve it so thought I'd ask the experts. I have an addess list. It has names and some with initials, and titles. I'm looking for a macro, or some simple way to remove these characters from each name cell. Example: Bob A Lastnam & Jenny B secondname Jr Fred Blogs & John J Ashton ii What I want as the result is: Bob Lastname & Jenny secondname Fred Blogs & John Ashton Any suggestions on an elegant way to acheive this. I note that doing a replace on " "A&...

if i select a cell then i want row containing that cell selected
i want to copy all rows that contain a cell with certain value. Do a Data > Filter > Autofilter on the key col, filter out that certain value, then copy the filtered rows, and paste special as values/formats elsewhere as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Maaz" wrote: > i want to copy all rows that contain a cell with certain value. ...

Fill the below blank cells
Hi, I just copied an pivot data and pasted it in different Sheet. I needed to fill the cells that was blank with the data above each set of blank cells.. Day VendorName VehicleType VehicleNo StartTime 1 ATL B 1246 18:00 22:38 1558 12:30 1815 4:27 6:00 7:00 7:30 12:55 7915 16:00 20:30 22:30 8037 1:00 5:30 7:00 9583 0:00 3:45 I can solve this by dragging the data down but I have do it at least 4000 times... Is there any formula to this...?! Thanks in advance Vinod Highlight the columns A to E by clicking ...

hide a section depending on bit value...
Hi 2 sections on a form: 'pers' and 'pers_kat' On the 'pers' section I have a bit (yes/no) radio button that I would like to be the trigger to hide/show the 'pers_kat' section incl. the attributes On the pers_kat there's 6 different attributes - the attributes name are: hw_pl_komp1 hw_pl_komp2 hw_pl_komp3 hw_pl_komp4 hw_pl_komp5 hw_pl_komp6 the: //hide section crmForm.all.ap_pl_komp1_c.parentElement.parentElement.style.display='none'; //show section crmForm.all.ap_pl_komp1_c.parentElement.parentElement.style.display=''; won't work....

conditional formatting #value!
Hi Is it possible to create a conditional formatting condition that can detect the condition of #VALUE! ? That is NOT the text string but the "error condition". What I am trying to highlight is that certain calculated values have not been correctly calculated on the sheet and need further investigation. Thanks Clive Hi Clive, Use the 'Formula Is' option on the conditional formating dialog and enter the following, assuming the cells is A1. =ERROR.TYPE(A1)=3 You can also use the Go To dialog (CTRL+G), Special... to locate cells with Errors. Cheers Andy Clive Long...

keep getting zero instead of empty cell
=INDEX(TABLE.xls!$A$1:$AQ$500,MATCH(B1,TABLE.xls!$A$1:$A$500,),MATCH("Drawin Number",TABLE.xls!$A$1:$AQ$1,)) This is the formula that I've been using to pull the matching value i the column "Drawing Number" from TABLE.xls, based on what I type i cell B1. This all works fine until the corresponding cell for Drawin Number is blank. I want to be able to specify what shows up in th cell in my new sheet when the other cell is blank. Even if it's just blank space that's what I want to show up. So I did some research on this board and am using the following formu...

leave a cell blank
My spreadsheet serves 2 purposes. Printed and manually filled out plus data entry. The cells with formulas show a 0 (zero) if the referenced cells are empty. The problem is the user has the option to ignore the formula and hard code a figure. If a formula = 0, is there a way to hide that zero so when I print the form it's blank for the user? thanks! You could turn off viewing zeros at Tools>Options>View. Or you could trap for the 0 and leave cell looking blank. =IF(A1*B1=0,"",A1*B1) If you are interested in keeping the user from over-writing a formula, see hel...

Cannot see all characters in cell
I have workbook that contains column for text/comments. I am unable to see all of the text in the cell no matter how I size it. All the text is displayed in the formula bar when the cell is selected, however not all of the text is displayed in the cell itself I have Excel 97 and I believe there is a 256 character limit. Is there a way to get around this limit, or can I do something to enable all text to be seen Thanks EU I am having the same problem on a form that I use every month. At the top there are 4 words that show up in the formula box, but don't show up in the cell or when I p...

coping with empty excel cells in the xml document #4
Hi I have an xml document that has been created from an excel spreadsheet. In the s/s there are some empty cells however these are not captured in the xml file. All that is added is for the cell after where the empty cell should be, it has an extra attribute: "ss:Index="6", where it is the 6th cell in the row, and cell 5 had no entry. I'm wanting to pick out a particular cell from a particular column however just iterating through the cells is going to give me invalid values where there should have been empty cells. Does anyone know how to handle this? I've tried ch...

Not able to access format settings for cell
I have a spreadsheet in 2003 that I have been using for quite awhile. The other day I think I hit some key combination that now does not allow me to access the format capabilities of the cells. If I trying clearing contents, still no luck. If I copy the format from some other cell that does allow it then Im OK. I thought maybe I accidently protected some cells, but when I check the protection tab under tools it does not show anything. Anyone have any ideas? You seem to have the cure to your problem:- "copy the format from some other cell". =?Utf-8?B?RGV2bw==?= <Dev...

Counting Cells #3
I want to be able to count the number of cells that have coloured text from a range of cells. Is their a way to do this, I tried "countif" but was unable to get it working. Thanks See http://www.xldynamic.com/source/xld.ColourCounter.html for a solution. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Missile" <Missile@discussions.microsoft.com> wrote in message news:A3FCE1A8-D5FE-49CD-B7B8-8B08C493F9F2@microsoft.com... > I want to be able to count the number of cells that have coloured text from a > range of cells. Is their ...

Move cells down to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7...

how to determine the size of the sheet
Hi I had a collegue who told me once a way to dtermine the number of the rows in a sheet of the workbook I am working at. It is known that when you open a new workbook each sheet has maybe 65365 or something like that rows. MY QUESTION IS: HOW TO MAKE THE SHEET 1000 ROWS OR WHATEVER NUMBERS OF ROWS I ONLY NEED? Thanks in Advance, Ahmed Hi Ahmed The number of rows and columns are fixed. Your workaround would be to hide the ones you consider unneeded. HTH. Best wishes Harald "Ahmed SHEBL" <ahmad.shebl@hotmail.com> skrev i melding news:%231uFO3IcHHA.4720@TK2MSFTNGP0...

Formattinb cells
Hi Is it possible to change the background color of a column of cells depending on the value of the cell at the top of the column. I have written a macro to do this but can't find a statement to do this: worksheets(:Sheet1").cells(cl,rw).backcolor = QBColor(14) Error message method not supported and nowhere in the help can I find an answer. Suggestions would be appreciated Thanks Charles Why not just use format>formula is>conditional formatting =a2=$a$1 and format as desired>copy format using format painter. -- Don Guillett SalesAid Software dguillett1@austin.rr.com...

Macro/keyboard shortcut to increment a cell value
I would like to set up a keyboard shortcut (e.g., Ctrl+I) that would increment the value of the selected cell by 1. Can someone get me started by pointing me in the right direction? I have some experience creating simple macros in Word, if that is what's needed. Thanks -- PC: HP Omnibook 6000 OS: Win 2K SP-4 (5.00.2195) LAN: P2P with an HP Vectra workstation Email: Usenet-20031220 at spamex.com (11/03/04) One way is to make a macro and assign the shortcut key to it: Option Explicit Sub AddOneToActiveCell() With ActiveCell If IsNumeric(.Value) Then .Value = .Val...

Excel automatically changes the formatting of the cell to "Time"
Hello, I have a long column of numbers [dates in the YY:DD format]. I wanted to replace ":61" to ":59". Even though the cells are initially formatted as Text, as soon as I make the change, Excel changes the formatting to Time, and the cell with the change now has text ":59:00" in it. Is it possible to force Excel to keep the cells formatted as Text? Thank you! Sam, If the cells are truly formatted as text (Format - Cells - Number - Text), the formatting should never change, and you should always see exactly what you've typed. Give us an example...

Reversing name values in a field
Hi, I have a field called CustomerName that has values that are last name, first name. So there is a value of Smith, John which is John Smith. I want to preserve the value in this field but create a new field called CustomerName1 that has John Smith in it. It would need to somehow reverse the values based on the comma delimeter and then get rid of the comma. Can someone help? Thanks, -- Chuck W LN = Left$(CustomerName, InStr(CustomerName, ",") - 1) FN = Right$(CustomerName, Len(CustomerName) - InStr(CustomerName, ",")) -Dorian "ChuckW" wrote:...