Who's good with MS-Excel? Options

[Rob Hood]

need a formula written, have been searching through the MS-help and can't seem to find a way to write the formula I want. I need a formula that shows the next number in column A when I enter data in column B.

For instance, if A2 says "1," then when I click on B3, I want A3 to show "2." I also need the number to show in five digits, e.g., 00001, 00002, etc.

Where's the enguhneers out there??? (IMG:http://www.frrax.com/rrforum/style_emoticons/default/smile.gif)

Rob

[RedHardSupra]

First part is either trivial or I'm not getting the question.
if you mean:

CODE

1      2
2      3

then B1=1 and B2=2 are just numbers, but where 2 is, you just put =B2+1

The second part you can most probably get through formatting, excel is good with that, click through all the numeric formats, it might just have what you need. if not, do whatever you need in normal numeric form, then you have another table of all this, but mangled into text, and 'padded' zeros.

In OpenOffice (excel contender from OpenSource side of things) you can just click on the cell, 'format cells' and type in number of 'leading zeros' and v'oile. I haven't used Excel in ages, but I assume they got something similar.

Hope this helps, if not, keep on posting, or catch me on AIM, bushmannt is the name.

[mitchntx]

=(cell number)+1

in the target cell should get it done.

However, this kind of throws me off ...

then when I click on B3, I want A3 to show "2."

You want to just click in the cell and the next cell over to ratchet up one number? Like a counter? No data entered? hmmmm ...

For the number formatting

Format>Cell>Number>Custom

Click on the single digit "0" and add the number of digits you want shown.

[Jeff94TA]

For the number formatting

Format>Cell>Number>Custom

Click on the single digit "0" and add the number of digits you want shown.

It doesn't look like he's talking about decimal places but placing zeros in front of the number.

Format>Cell>Custom

In the box next to "Type:" just erase what's there and type in 00000 to show four zeros. This will show three zeros when you get into double digits and so on.

On Edit: After reading yours again Mitch I believe we are saying the same thing. Sorry about that.

[Rob Hood]

ok, I'm getting there - thanks for all the input!! (IMG:http://www.frrax.com/rrforum/style_emoticons/default/smile.gif)

Mitch, once I figured out the "target cell" was not the SAME cell.... (IMG:http://www.frrax.com/rrforum/style_emoticons/default/rant.gif) (IMG:http://www.frrax.com/rrforum/style_emoticons/default/banghead.gif) it all fell into place. Also got the numbers to display all five characters, thanks. Ihad looked at that earlier but wasn't able to make it display properly before.

Still working on making the number in Column A increase when I click on or enter data in Column B; I think that involves a function feature.

Rob

[y5e06]

edited to add if statement (always check your work, right?):


ok, I don't know how to get your counting column (A column) to increment just by clicking on your data cell (B column). However you can use a count function 'counta' to return a value if there is any data of any kind (integer, character, etc.) entered in your data column
in your first column, first cell (A1) enter: =IF(COUNTA(B1)>0,1," ")
in the first column, second cell (A2) enter: =IF(COUNTA(B2)>0,A1+1," ")
once you do that, click on the second cell (A2) and drag all the way down for as many counting cells as you want, then hit 'control' and 'd' at the same time.
that will fill down that formula to the lower cells control+d is the short cut.
(or you could pick 'Edit' -> 'Fill' -> 'Down' from the pull down menu.

After that when you enter data in B1, '1' will appear in A1
if you then enter data in B2, '2' will appear in A2.

you could make it more complicated using more logic statements if you wanted to skip data cells but continue counting. say if you wanted to not enter data in B3 but you do want enter data in B4 and still have column A incriment.

This post has been edited by y5e06: Oct 18 2004, 12:46 AM

[Rob Hood]

Morgan, thanks for the input. I did try your suggestion, but got a "circular reference" when I tried to input data in B3. Also, a number did not show in A1 until I put data in B2, not B1. However, this is much further than I got...(IMG:http://www.frrax.com/rrforum/style_emoticons/default/smile.gif)

Rob

[mitchntx]

OK ... now I'm curious ...

What the hell are you doing?

(IMG:http://www.frrax.com/rrforum/style_emoticons/default/unsure.gif) (IMG:http://www.frrax.com/rrforum/style_emoticons/default/banghead.gif) (IMG:http://www.frrax.com/rrforum/style_emoticons/default/wink.gif) (IMG:http://www.frrax.com/rrforum/style_emoticons/default/cool.gif)

[Rob Hood]

pretty much banging my head like that emoticon...(IMG:http://www.frrax.com/rrforum/style_emoticons/default/wink.gif)

I was trying to make a "simple" sheet to log text data in a column format, but needed (wanted?) a column that assigned a number each time I inserted data in the other column.

So far, your counting method works for what I need in a basic format, and I've already gone ahead and built the page. However, I would like to be able to get that second function working. This is kinda like entering data for a job request, and when you enter that data, a job number gets assigned. It's just that my format requires the number column be first in a left-to-right format. (Not really "my" format either, just another Navy thing...)

[felton316]

If you are just generating a auto numbering sequence for column A:

=IF(B1<>"",COUNTA($B$1:B1)&".","")

Paste that into A2, then copy it into all cells in the row that you want to auto number. Every time a entry is made into column B it will number column A. If you were to delete the info im column B Cell 50 out of 100 Cells, it will automaticly renumber Column A. Now to format to 5 digits, I have no clue....... I'll have to play around with that one.

[mitchntx]

I am convinced that is I wanted to know how a black hole exists, the meaning of life or how it is the geekiest looking guy can be with the hottest woman (went to the IRL race today and personally witnessed this freak of nature), someone here could tell me.

[trackbird]

I am convinced that is I wanted to know how a black hole exists, the meaning of life or how it is the geekiest looking guy can be with the hottest woman (went to the IRL race today and personally witnessed this freak of nature), someone here could tell me.

Yea, and I think I may need to create a new forum for those questions (IMG:http://www.frrax.com/rrforum/style_emoticons/default/rotf.gif) .

I'm just not sure what to call it......

Can we get Stephen Hawking for a moderator?


And remember Mitch:

When a great looking guy is with an ugly woman. That's love.

When a great looking woman is with an ugly guy. That's money.

When two ugly people are together. That's just the way it works.

(I take no credit or blame for the above "wisdom").

[rmackintosh]

I am convinced that is I wanted to know how a black hole exists, the meaning of life or how it is the geekiest looking guy can be with the hottest woman (went to the IRL race today and personally witnessed this freak of nature), someone here could tell me.

THAT....is simple....

$$$$$$$$$$$$$$

(IMG:http://www.frrax.com/rrforum/style_emoticons/default/laugh.gif)