OT: Another Excel Question

Il 20/10/2011 19:45, Jim Thompson ha scritto:

A1=INDICE(B1:H1;1;CONTA.VALORI(B1:H1))

Where:

INDICE should be INDEX in the English version CONTA.VALORI shoud be COUNTA in the English version H1 is the last cell you may populate. Change as you need.

Marco

That works in Open Office as well. Equation for cell A1: =INDEX(B1:H1,1,COUNTA(B1:H1))

It breaks if any cell in the index range has no value, but works if a value of zero is used for an otherwise unfilled cell within the index range.

Thanks, Marco!

--Winston

Thanks! ...Jim Thompson

You can give the D1 cell a "named range" moniker. In the upper left corner of the sheet, the cell name is indicated as "D1". You enter that location, and it is automatically highlighted. You type in whatever name you want to give the cell. Say you call it "CellD1". Now, whenever you go up to that indicator (upper left) and type in a declared named range, it will take you there immediately. So, if it is a single cell, it will go to that sheet and that cell. If it is a named range, it will go there and highlight the entire range.

Then, in A1, you would type the formula =CellD1 and it will follow the contents of D1. You will get an error if the named range covers more than one cell as a range cannot be presented in a single cell.

The other method is to type =D1 in cell A1.

INDEX, COUNT & COUNTA are _really_ useful!!

I also ran across this page...

VLOOKUP & HLOOKUP are very useful too. Art

Thanks, Jim. Great resource!

I can do that with a mother of nested if(isempty(),,) stuff. I'd like to see if anybody does better.

?-)

You are dumb. Nested IFs fail, and you should know why and not knowing means you don't know enough.

He needs to define the criteria better.

If all he wants is A1 to reflect what is in D1, that is simple. If there are conditionals, they need to be declared before we could proceed with a solution.

Late to the game... as always ;-)

Marco Trapanese, in...

Message-ID:

Provided the easy way to do it. ...Jim Thompson