MS Excel / VBA help thread

If you select each bit of the fomrula and hit F9 you can see what it's evaluating to (then hit escape otherwise it overwrites that bit you selected with the evaluation though!)

If you have space to write in column F the values that you're looking for (1, 2, 3, 4, 5 etc) next to the values in G12, G14, G17 etc, you could replace the formula with " =vlookup($C$2, $F$12:$G$26, 2, false) " (no quotes)

RPM, the first formula looks ok structurally, but you are treating your numbers as strings (text), which might cause issues. Try this:

[code]
=IF(C2=1,G12,IF(C2=2,G14,IF(C2=3,G17,IF(C2= 4,G19,IF(C2=5,G22,IF(C2=6,G24,IF(C2="sm",G26, "")))))))
[/code]

Basically "2" is not the same as 2.

EDIT: Tiswas got there before me.

Thanks guys, it's worked a treat.

ahem:

For Each Cell In OneYR

With Cell
        If .Value = "" And (Range("CI" & .Row).Value = "4. Not applicable - " Or Range("CJ" & .Row) = "2. NA") Then
            .Interior.ColorIndex = 10

        Else

        If .Value = "" And (Range("CH" & .Row).Value <= (Range("CS" & .Row).Value + 365)) Then
            .Interior.ColorIndex = 5

        Else

        If .Value = "" And (Range("CH" & .Row).Value >= Now() + 365) Then
        .Interior.ColorIndex = 3

        End If
        End If
        End If


   End With
Next Cell

So, the idea of this is to colour code. It does/did.
I've added the second and third if's.

What's the best way to say "is the date in CH > than a year ago?" use a Now () or use a date that's entered into CS.
I'd like to compare to the date in the column and say, is the date in CH less than a year from CS? colour blue >ayear colour red.

I'll keep flicking around but logical expressions for dummies would help.
thanks.

Anyone got experience of cross compatibility with Excel and Numbers on Mac, or going between excel on PC to numbers on Mac?

OO can't handle a lot of Excel's things for example, I would like my excel workbooks to be usable on Mac and back to PC again without the whole thing going up shit creek.

Numbers for the iPad looks great, and in some faraway dreamland would open, edit, save and export excel docs without issue.

anyone got a simple yearly accounting template with monthly income and few expenses fields?
just downloaded pages and numbers from the app store and the free templates have lots of stuff i don't need and it fucks with the sums when you delete stuff.
i can make my own as it's a case of highlighting the column and making it all add up but that's as far as my knowledge goes and the free templates always look neater

There are a few templates on Google Docs if that helps at all.

google asks you to sign up to download anything.
i did a simple 12 month expenses one myself as you just need to highlight a column and hit the sum button, then do the same to get a total for the 12 months.
i don't use spreadsheets enough to want to learn anything beyond that, i'll just do the other bits in a word doc (pages as i'm on a mac)

I've been sick and lost my exel mind
Cells contain a date like this
A1 = 12/01/2011 09:57
I need to split this in columns, I've tried =left =right etc but it gives me gibberish
If I copy the columns and change the format to Date or Time it shows me the info I want
but all the crap is still there.
19/01/2011 23:35 23:35:00 19/01/2011 40562.9826

First thing is to understand how Excel treats dates:

Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt . This is called a serial date, or serial date-time.

(from http://www.cpearson.com/excel/datetime.htm)

With that in mind, you can format the date in any way you choose without changing the data itself. The easiest way to do this is to right click on the cell (or array of highlighted cells) and click "format cells" and pick the way you want the date presented.

Text to columns that shit. Split the time off.

What do you want to show in the other cells?

You can have the same values, just change the format to display different things.

e.g. A1 displays "12/01/2011" - format as "dd/mm/yyyy"
B1 displays "09:57" - format as "hh:mm"

That's what I'm after , well I can get it to Display that,
But if I want to Filter the dates it then only gives me 2010 and 2011

i have a similar thing.
I copy a range of date time data from one column. Split it into date and time.
Display it as mmm-yyyy. The time's still there though.
mmm. Not working exactly as you want?

Can you convert to text and then paste back?

What if you paste into notebook?
That'll turn it into plain text and drop off the time.

To remove the time, round the cell value to an integer using =int(). To remove the date, subtract the integer of the cell value to leave the fraction only.

Even if you change the format, if the number is :
40428.375
and not
40428
you'll still have the time, it just won't be displayed.

To remove that, I've found it useful to copy and paste into notepad, then paste back in. Change the format. And Ta DA. the .375 has gone.

Notebook. Making plain text.

What's the format of the cell?

(Right click - *F*ormat Cells...)

It was selected as date, but apparently not the correct date option!
sorted now, thanks guys.

I've been to lazy only dealing pivot tables lately

Or, more elegantly, use the int function like what he said.

Is there anyway we could do an MS Excel ride?

(note: i've just banged a big cup of coffee. right now this thread is full of win and i'm a bit jittery)

Even if you change the format, if the number is :
40428.375
and not
40428
you'll still have the time, it just won't be displayed.

To remove that, I've found it useful to copy and paste into notepad, then paste back in. Change the format. And Ta DA. the .375 has gone.

Notebook. Making plain text.

Jesus Damo this is like watching my Gran trying to program a VCR.

There's really no point in manually removing the time value when you can just hide it, right? Why do you want to remove it?

If you can't get cell formatting to work, then try this:

1. Put your timestamp in cell A1
   
2. In cell B1 put
   [code]
   =TEXT(A1,"mmm-yy")
   [/code]
   
3. In cell C1 put
   [code]
   =TEXT(A1,"dd/mm/yy")
   [/code]
   
4. in cell D1 put
   [code]
   =TEXT(A1,"hh:mm:ss")
   [/code]
   
   

See what's happening there? You're basically forcing the formatting.

The best thing to do is still just to format the original cell to only show the part of the timestamp you're interested in.

No but. yes. but. FFS.
I like plain text. Alright?

It's not the right way, no, but it worked. And I'm lazy. Like most humans. I know that cutting and pasting to notepad is a dirty way of forcing to plain text.

You're just creating extra work for yourself, removing precision from the data, and risking introducing errors from copy/pasting

Yes. I wouldn't recommend it all the time. I've used it previously to split dates and times.