Excel Question (or, the Holy Grail)

Hack your OS, customize your GUI, announce great software, and configure, configure, configure...
Post Reply
kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
Posts: 264
Joined: 01/17/02, 12:00 am

Excel Question (or, the Holy Grail)

Post by kenray » 08/20/08, 5:55 pm

if anyone knows excel, or knows someone who does, please ask them about this, thanks!

So, in my job i have this spreadsheet

Image

The x's represent pages in a book.

I would really love to have the function of saying that the highlighted cell is "1" and then make the program sequentially number the rest of the page (the Holy Grail).

Sometimes these books go into hundreds of pages, then some fart comes in and adds a page at page 3, or removes one at page ten, and i FREAK OUT!!!

I would be awash with joy to be able to do this...

JohnT
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
Posts: 2486
Joined: 12/03/01, 12:00 am
Location: Vladivostok, Russia
Contact:

Post by JohnT » 08/21/08, 9:27 pm

While I don't understand your question fully.....you might find something HERE that would help you on your way.
"A man may be a fool and not know it, but not if he is married."

RedRage
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
Posts: 1542
Joined: 12/04/01, 12:00 am

Post by RedRage » 08/21/08, 11:54 pm

if i know what your talking about... each cell will need a function in E2 for example something like =E1+1.or something like that.. E2 will always be one more than E1

if you plan it right then changing lesser the beginning will change everything after it. will be a huge pain in the butt to set up the first time but after that, you should be set

but i'm prolly not understanding correctly in which case ignore me

JohnT
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
Posts: 2486
Joined: 12/03/01, 12:00 am
Location: Vladivostok, Russia
Contact:

Post by JohnT » 08/22/08, 6:27 am

"A man may be a fool and not know it, but not if he is married."

kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
Posts: 264
Joined: 01/17/02, 12:00 am

Post by kenray » 08/22/08, 4:07 pm

John T - thanks for chipping in...but...not every possible page is used in each sequence, so the autofill wont work.

In the pic example above, each X represents use of a type of page to be used at a certain time.

What i am hoping to find is a way to say:

The first X = 1.

All X occurrences thereafter are the previous value + 1.

1+1=2
2+1=3

and so on, but ONLY where there is an X (not just every cell in the column).

Holy Grail.

kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
Posts: 264
Joined: 01/17/02, 12:00 am

Post by kenray » 08/22/08, 4:15 pm

RedRage wrote:if i know what your talking about... each cell will need a function in E2 for example something like =E1+1.or something like that.. E2 will always be one more than E1

if you plan it right then changing lesser the beginning will change everything after it. will be a huge pain in the butt to set up the first time but after that, you should be set

but i'm prolly not understanding correctly in which case ignore me
You are so on it (comprehension), but the goal would be something like:

if "X" is found, then set cell value to previous numeric value plus one

Dunno how to word that in excel...

RedRage
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
Posts: 1542
Joined: 12/04/01, 12:00 am

Post by RedRage » 08/22/08, 4:42 pm

Assuming the highlighted cell is Cell B2 and you want the cell just to the right of it (C2) to always be one higher number then click in Cell C2 and type in =B2+1

if you want cell D2 (the one to the right of C2) to always be 1 number more you just continue doing the +1.. so in D2 you type in =C2+1

you can do this anywhere you don't have to be in the same column at all. so if i wanted in cell J9 I could type in =D2+1 and J9 would then always be one number higher than D2.

you can also add 2 cells to geather using the same idea =D1+D2 would give the sum of the 2 cells.
I am using OpenOffice but Excel is the same idea.

kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
Posts: 264
Joined: 01/17/02, 12:00 am

Post by kenray » 08/22/08, 7:39 pm

Hey Red,

Yeah, i see the logic in that, but...imagine doing that 206 times. plus, that would be at least three typestrokes for every cell. Monumental amount of work.

I am looking for a short out.

Holy Grail.

RedRage
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
Posts: 1542
Joined: 12/04/01, 12:00 am

Post by RedRage » 08/22/08, 7:59 pm

if you have to do it many times (different files) it could be handy as a template.

there is a way to point and click it too... i find it slower for me.

maybe this will speed things along I'm guessing you could prolly do 5 fields every 10 seconds after you get used to it. LOL but i'm not sure this actually works.

Code: Select all

setup the first field with the starting number
copy +1 to the clip board
click the destination field and hit the = sign
click the field you want to add too (should put the field cords in for you)
hit ctrl+v
hit enter
I'm not an excel function guru, i bet there is a way to shorten the amount of work involved I just don't know what it is.

kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
Posts: 264
Joined: 01/17/02, 12:00 am

Post by kenray » 08/26/08, 1:36 pm

thanks for the efforts red.

I am still praying for some knight in shining armor to come thru with a automagic solution for the problem...

Post Reply