1. Home
  2. Electronics Design
  3. OT: Another Excel Question

OT: Another Excel Question

Suppose I have...

A1 B1 C1 D1 E1...

B1 C1 & D1 are populated, E1... are empty

I want the contents of D1 to appear in A1

In other words, right-most populated cell value appears also in A1

How do I do that? ...Jim Thompson

-- | James E.Thompson, CTO | mens | | Analog Innovations, Inc. | et | | Analog/Mixed-Signal ASIC's and Discrete Systems | manus | | Phoenix, Arizona 85048 Skype: Contacts Only | | | Voice:(480)460-2350 Fax: Available upon request | Brass Rat | | E-mail Icon at

formatting link
| 1962 | I love to cook with wine. Sometimes I even put it in the food.

Reply to
Jim Thompson
Loading thread data ...

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

Reply to
Marco Trapanese

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

Reply to
Winston

Thanks! ...Jim Thompson

--
| James E.Thompson, CTO                            |    mens     |
| Analog Innovations, Inc.                         |     et      |
| Analog/Mixed-Signal ASIC's and Discrete Systems  |    manus    |
| Phoenix, Arizona  85048    Skype: Contacts Only  |             |
| Voice:(480)460-2350  Fax: Available upon request |  Brass Rat  |
| E-mail Icon at http://www.analog-innovations.com |    1962     |
             
I love to cook with wine.     Sometimes I even put it in the food.
Reply to
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.

Reply to
CellShocked

INDEX, COUNT & COUNTA are _really_ useful!!

I also ran across this page...

formatting link
...Jim Thompson

--
| James E.Thompson, CTO                            |    mens     |
| Analog Innovations, Inc.                         |     et      |
| Analog/Mixed-Signal ASIC's and Discrete Systems  |    manus    |
| Phoenix, Arizona  85048    Skype: Contacts Only  |             |
| Voice:(480)460-2350  Fax: Available upon request |  Brass Rat  |
| E-mail Icon at http://www.analog-innovations.com |    1962     |
             
I love to cook with wine.     Sometimes I even put it in the food.
Reply to
Jim Thompson

VLOOKUP & HLOOKUP are very useful too. Art

Reply to
Artemus

Thanks, Jim. Great resource!

Reply to
John S

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

?-)

Reply to
josephkk

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.

Reply to
CellShocked

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

Marco Trapanese, in...

Message-ID:

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

--
| James E.Thompson, CTO                            |    mens     |
| Analog Innovations, Inc.                         |     et      |
| Analog/Mixed-Signal ASIC's and Discrete Systems  |    manus    |
| Phoenix, Arizona  85048    Skype: Contacts Only  |             |
| Voice:(480)460-2350  Fax: Available upon request |  Brass Rat  |
| E-mail Icon at http://www.analog-innovations.com |    1962     |
             
I love to cook with wine.     Sometimes I even put it in the food.
Reply to
Jim Thompson

ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.