Posts by hiddennomad13

I messed up right here:

=IF(COUNTA(A$1:A$1000)<ROWS(A$1:A1); ""; INDEX(A$1:A$1000;COUNTA(A1:A$1000)))

The formula was highlighting A1 in blue when it really needed to be A1... Fixed it now.

Here’s a quick breakdown on INDEX (shortened from Help) ...

Returns the value of an element in a table selected by the row and column number indexes.

INDEX(array,row_num,column_num)

Row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.

Column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.

=INDEX(A2: B3,2,1) returns the value at the intersection of the second row and first column in the range

Well, there you go. Just tweak the columns or rows however works best for you..

Everything is laid out pretty clearly in that link, too...
If I can just go slightly off-topic for a second—instead of using the current formula where it would return a #VALUE error if you typed "Springfield" instead of "Springfield City," I’d probably swap in this version to make sure it just returns the word itself: =TRIM(RIGHT(A1;LEN(A1)-FIND(" ";A1&" ")+1)&" "&LEFT(A1;FIND(" ";A1;1)))

My bad, I didn't go through the whole thing super carefully—I just jumped straight to your solution.

But, if you’re looking to drag this formula to the right, you can just tweak those column references like this: =INDEX(A$1:A$1000,COUNTA(A1:A$1000))

Basically, if you type your target word into each column, the result will follow suit (the logic works for rows, too).

The only little hiccup is that it'll spit out a zero for any empty cells. To fix that, we need to add a check to see how many characters are actually in the column:

=IF(COUNTA(A$1:A$1000)>ROWS(A$1:A1), "", formula)

Kyle Johnson2 said:Let’s say you have the word PISMO typed out in the first row across these columns:
A1=> P
B1=> I
C1=> S
D1=> M
E1=> O

Now, just drop these formulas into the next available cells in that same row:
G1: =E1
H1: =D1
I1: =C1
J1: =B1
K1: =A1

It's basically the same logic for writing formulas... but here's an automated way to do it:

=INDEX($A$1:$A$1000,COUNTA(A1:$A$1000))

wanderingraven14 said:Hi everyone,

I'm trying to set up a staff schedule in Excel. Ideally, I want to input the shift times and have a formula automatically calculate the hours worked so I can track when someone hits their 40-hour week. It works fine for standard shifts (like 2:00 PM to 10:00 PM), but I'm hitting a wall whenever there's an overnight shift (like 10:00 PM to 6:00 AM). I'll admit 😢, I'm still pretty new to Excel and mostly just stick to the basics. Formulas beyond simple subtraction like (=G4-G5) have me a bit stumped,🙄 so I could really use some help.
I would be so grateful if someone could point me toward the easiest formula to handle this. I tried using =TEXT(G5-G4;"H") and playing around with the TIME function... but nothing is working (or maybe I'm just doing it wrong).

Thanks so much,

ammod🙂

Just try this: =MOD(A1-B1,1)

nimbleraven said:I pretty much just know the basics. I'm looking to pick up some more advanced tricks.

Check out YouTube...

http://www.youtube.com/watch?v=XmSp2-Fa4rg

Just skip ahead to where you feel comfortable, then take it step by step from there...

format C: 😁

So, besides just not believing her... you'd actually go as far as turning to criminal activity? 🙂

Since you’re going to run into issues once you start deleting rows, this approach might be a smoother way to handle it:

Just pop this formula in: =1000+ROUNDUP(ROWS(A$1:A1)/2;0) and then drag it down as far as you need.

Here you go:

http://uploading.com/files/349c6d6m/a.zip/

I've attached a document containing the first 3 or 4 pages without numbering, after which it starts back at page 1. You'll find instructions on how to set it up at the bottom.

Honestly, you can just use this file—copy and paste your stuff in there and you're good to go.

Splitting everything into different pages is a headache. Not only does it mean managing multiple files for a single text, but you also lose the ability to auto-generate a table of contents. Plus, you’ll end up fighting with printer settings constantly trying to figure out if you have an even or odd number of pages. It's just not worth the hassle.