Excel Question (1 Viewer)

[Anthony]

Excel numbering autofill question.

image I have this in a cell: 001.22. I want the cell on the row below to read 002.22, and so on.....

How can I quickly fill hundreds of cells with numbers?
Easy to do and have the next cell become 001.23, 001.24 etc But I just want the first number to change

you follow?

Come on, this must be possible?

 

[therealjohnny]

@Scientician 0.8 s Excel

 

[flashback]

I haven't really used Excel much, but I opened it up and put this formula in and I think it works?
- Set A1 as your starting val (1.22)
Next cell with this:
=A1+0.001
then just drag that down with the mouse.


or wait, you want the series to be more like:

1.001
2.001
3.001..
?

Then just put the formula in B1 like

=A1+1

Or whatever you need the increment to be.

 

[Anthony]

That makes sense.... brb

 

[Anthony]

The +1 method works for simple 1.22, 2.22, 3.22 etc and I can live with that.

But

Is it possible to do with with a more "complicated" catalogue number, for example

ANTO.001.22
ANTO.002.22 and so on

 

[flashback]

So you want to cat a string onto the front of the floating point number?

First pass I'd do the numberical calc in one col, eg "B",
Then I'd make another col ("C"), and set the formula as

=CONCATENATE("ANTO.", B2)


So, I'm doing the maths in one place, and cat-ing on another string afterwards?

Might be missing the point here though.

 

[Anthony]

That also sounds like it'll work. I'll try it out and then hide the column with the maths. Thanks.

 

[flashback]

The col that you do your calcs in can be all the way over in Z I suppose. Doesn't have to be B.

You can probably just ref another sheet even?

But, yeah, I'm not much of an excel person.

 

[Anthony]

This works and is deadly. Thanks @flashback

 

[flashback]

This works and is deadly. Thanks @flashback

Nice!

Just looking at Excel here, it's kind of cool. I never thought it was as powerful as that. I always just write something to do this work, but Excel can do loads of stuff.

 

[Anthony]

yes, the place I work excel is basically used as a big ledger for writing in. I'm trying to cut down the work one keystroke at a time.

 

[ann post]

To view this content we will need your consent to set third party cookies.
For more detailed information, see our cookies page.

Accept third party cookies

 

[pete]

my Excel question is why did they call it Message In The Bottle

To view this content we will need your consent to set third party cookies.
For more detailed information, see our cookies page.

Accept third party cookies

 

[seanc]

HORN THREAD for this.

I would've suggested something similar to flashbacks idea, which seems to work so grand.
I wish I could understand how pivot tables work. One of lifes great regrets, and I don't even understand what they are actually for.

 

[Cornu Ammonis]

Yeah, +1 for @flashback’s suggestion. I do that all the time for ID numbers for studies.

 

[flashback]

HORN THREAD for this.

I would've suggested something similar to flashbacks idea, which seems to work so grand.
I wish I could understand how pivot tables work. One of lifes great regrets, and I don't even understand what they are actually for.

Are pivot tables an excel thing yeah?

Pivot. OK. It's probably excel trying to join some value on another sheet? Basically re-inventing a relational DB or something?

I suppose I could learn what a pivot table is.

 

[flashback]

I suppose if any of you lot want simple programming jobs done you can ask me.

Having seen Excel though I'm probably almost out of a job.

I have to be able to do it in about 60 seconds, otherwise there's trouble. Preferably waiting for a conference call to start.

 

[seanc]

Are pivot tables an excel thing yeah?

Pivot. OK. It's probably excel trying to join some value on another sheet? Basically re-inventing a relational DB or something?

I suppose I could learn what a pivot table is.

I think so, but when I was learning all this I think my brain just said "nah, that's a stupidly complicated way of doing things, I don't see how this makes things better or easier, so seanc brain will go BBBBRRRZZZZZZZZRRRRZZZZ when it comes to pivot tables".

I'm not that smart.

 

[pete]

people who get pivot tables ALWAYS do pivot tables

 

[seanc]

people who get pivot tables ALWAYS do pivot tables

As I said, one of lifes great regrets.