=IF(LEFT(A1;2)="I8";TRUE;FALSE)

A1 here is the cell you want to test.

Edit: Oh yeah, I'm using ; instead of ,

=if(LEFT(A1,2)="I8",TRUE,FALSE)

Pick your poison.

I'm pretty sure he was asking to check whether the formula in the cell started with "=I8" but i could be wrong. If so you need to use a UDF with some VBA.

e.g.

in A1: =I8+J8
in B1: =myFunction(A1)

right click on sheet tab and go view code then right click on the left on VBAProject and go Insert > Module then paste:

I didn't actually test that but it should work

also on left functions (and right, len functions too), prob missing something obvious here but when the result of one of those functions is a number my graphs aren't working. then if i do something like a1+1-1 (or whatever returns the same number) it does. what am i missing here? not a major issue but somewhat annoying

The result of a left and right function is always text, even if that text happens to be a number. Usually Excel is pretty good at interpreting it as a number when it needs to (which is why a1+1-1 works, in that context it realizes a1 needs to be a number), but sometimes you need to specifically tell Excel that the result should be a number and that's how you want it interpreted. You can also use the VALUE function for this. =VALUE(RIGHT("ABC123",3)) will give you the number 123 as opposed to a text string that says "123".

you can also use -- which forces the result as a number i.e.

=--RIGHT("ABC123",3)

but if you do

=--RIGHT("ABC123",4)

it will give a #value error

If I am using 2 monitors, how do I get one excel window in each monitor? Until now I have just stretched one window over the 2 monitors.

set up the monitors correctly? that's not really an excel question

losing my mind here, can't figure out why this won't work.

i'm trying to use indirect, i guess if your filename has spaces in it you need to put quotes round the filename?

so i have :

in A1 - ["my original - test file.xlsx"]
in A2 - [test.xlsx]

they're the exact same file and have been opened in excel in the same way.

using this formula :

=INDIRECT(CONCATENATE(A1,"Results!K1"))

it will work for cell A2 but not A1. why?!

Have you tried putting ' around the Filename and sheet (I think)?

And, go ahead and do it manually once and then identically replicate, you should see the error.

yepppppp you're correct.

'[Filename with no quotes required]Sheet'!A1

tytyty

I never knew about this!

Thanks for your post.

And thanks for starting this thread. Very useful.

When I have to do this I open the first work, and then on the second workbook I right click the excel icon on the toolbar and click the excel icon. Now I have two separate windows and can place one on each screen.

Yeah, you have to do two instances of Excel for it to work.
Or a seperate program that can do it.

I have an excel sheet that I use for work, which is a list of names with several values in the columns next to them. All of the names are at most 17 characters long, and end in _F, _R, _qF or _qR.
Is there any way that I can get all the names that end in _qF and _qR, copy the rows they're in and paste that into a new excel sheet?
Preferably if possible in a way that I can update, instead of doing it all over again anytime I add additional rows.

Hmm for some reason I can only get this wokring with one criteria at a time so if you are happy with having two lists on new sheet one for _qF and one for _qR you can use this:

you can put in cell B1 then copy down, as soon as you add more data to column A1:A100 it should automatically update

edit: it's an array formula so you need to press ctrl+shift+enter when entering it

You could just make a field that indicates which of those 4 categories the row falls in and use a filter. That way it's always updated.

^Does that just return the _qF cell or the whole row?

I would highly recommend getting a program or add on to have multiple instances of excel. It's extremely convenient.

Anyone try Excel on a Surface tablet? I'm curious to see how well it can handle a computationally intensive spreadsheet. (Assuming it's 100% compatible with Office 2007 minus macros)

hi guys, can someone please help me with this...

I have a cell with a drop down list which has the names of all my clients at the top of my sheet. I then have a load of data from all my clients in the same sheet . What I am trying to work out is if I change the drop down to one account, the filter matches what's in the drop down cell to show me the relevant data.

what i am trying to do is if I select client x, the data automatically filter for client x. Then if i change the drop down to client y, the data then shows client y.

many thanks.

If anyone could contact me on Skype: joetallio, I have basic-ish question for Excel, most appreciated.

Thanks,
Joe

Anyone got any knowledge about exporting from Baan to Excel?

Got to cover some work, and have never used Baan before. They seem to spend most of their time keying paper reports into Excel - seriously,who does that?

Bit of googling suggests it might be a case of changing some export settings but any advice would be welcome.

Don't need anything advanced. I can manipulate the data if I can get it into excel.

I think I have a rather complex problem. I have a query running to retrieve my personnel's hours. They often mess up the use of the entry system (a boardcomputer in their truck). I get the data back in bunches (say 1500 rows per month per person, about 45 people atm). One issue is that I want to combine different measurements.

Say it measures as follows
Entry # location duration activity person
123 XX 100 1 A
124 XX 2 2 A
125 XX 15 1 A

The data is mixed between different people but should normally get in more or less in order. I want to combine all the time into 1, in this case 117 minutes of activity 1, person A. I have now made a series of vlookups/ifs to combine the above into 1 duration, which works. However, if the person has 2 really short activities in between it doesn't work and some other minor problems.

I assume that this can be quite easily solved with VBAs? Ideally afterwards I would like to run some different reports on it, change the hours (for example when getting gas they only get paid 10 minutes as they often get a sandwich afterwards and have a half-hour break, while still on the REFUELING activity, so to say.

If people don't know (or this is too difficult to help me with just like this), how do I go about finding a local expert to help me with these things? The best I know are barely any better than me...

SUMIF? And SUMIFS, you should be able to do this fairly easy that way.

I don't just want to sumifs, I want to have individual actions with a certain duration so I can deduct the correct amount form each individual remaining action.

I feel like SUMIFS are still the solution, but I cant really say it without really knowing how you achieve the end result.

As I understand it, you got activity A e.g. unloading truck, but your drivers go eat a sandwhich in between that (activity B), so you want to show the sum of all activities A and deduct all activites B, correct?

lazy drivers and their sammiches