Excel Question (or, the Holy Grail)

Hack your OS, customize your GUI, announce great software, and configure, configure, configure...

Excel Question (or, the Holy Grail)

Postby 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...
kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
 
Posts: 264
Joined: 01/17/02, 12:00 am

Postby 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."
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

Postby 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
Image
RedRage
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
 
Posts: 1541
Joined: 12/04/01, 12:00 am

Postby JohnT » 08/22/08, 6:27 am

"A man may be a fool and not know it, but not if he is married."
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

Postby 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

Postby 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...
kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
 
Posts: 264
Joined: 01/17/02, 12:00 am

Postby 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.
Image
RedRage
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
 
Posts: 1541
Joined: 12/04/01, 12:00 am

Postby 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.
kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
 
Posts: 264
Joined: 01/17/02, 12:00 am

Postby 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.
Image
RedRage
BIG GIANT HEAD I Get Free Beer
BIG GIANT HEAD I Get Free Beer
 
Posts: 1541
Joined: 12/04/01, 12:00 am

Postby 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...
kenray
Senior Member I Get Free Beer
Senior Member I Get Free Beer
 
Posts: 264
Joined: 01/17/02, 12:00 am


Return to Own The Machine

Who is online

Users browsing this forum: No registered users and 1 guest

cron