INTERSECT in Excel

[Anne Troy]

Like a multiplication table, I need to find a value based on both the top row and left column. Excel doesn't have the INTERSECT formula anymore? Or maybe I just lost my head? Thanks for your help, guys.

[Bob Phillips]

It doesn't work off of just the headers, you specify the whole row and column, like

=A2:C2 B1:B10

or

=2:2 B:B

[Anne Troy]

Okay, so that doesn't appear to be my problem after all. I'm not sure how to design this...

We want a workbook where each user has their own worksheet.
The admin creates a specific workout regimen, such as 15 pushups, situps and squats.
That's for medium intensity. There is also Light and Heavy intensities.
Then, there is up to 100 different exercises.
He wants to, for instance, create a week's worth of cycles in advance. The user opens the workbook and chooses their intensity, and it's populated for the cycle for that day that he created. I can't work it out how I'm gonna get:
Name
Intensity
Exercise
Reps
Any suggests?

[JeffreyWeir]

Anne: If you post a sample spreadsheet with some examples of what you want to achieve, then I'm sure someone can help you out.

[Anne Troy]

Okay, I posted, but the file was incomplete. I'll fix it up and upload. Thanks!

[Zack Barresse]

Was it deleted again? I dont' see it now.. ?

[JeffreyWeir]

Something like this? 55
Or do you want the user to be able to select a different intensity each day?

[Zack Barresse]

This sounds like it should be in Access... ;)

[Anne Troy]

LOL That's what I told him. The thing is, he doesn't even have Excel. He originally wanted Googledocs (again), but it barely doesn't a data validation.

Thanks, guys. Sorry. I'm suggesting a web-based app with MySQL, which I may actually learn.

[Anne Troy]

And I heard you laugh just then, Zack. :lol:

[Ken Puls]

And I heard you laugh just then, Zack. :lol:

That might have been me. ;)

[Zack Barresse]

There is always Office Web Apps, which are free with a Live ID or Hotmail account. Although I don't think they support Data Validation either. It gets tough in a web browser. But I like them better than Google Docs personally. :)

[Ken Puls]

Although I don't think they support Data Validation either.

They don't, and personally I think this is the biggest miss of the webapp product. The whole point of the webapp is to consume information, yet we can't sanitize the data inputs.

I really hope Microsoft is working on this for the next release, as it's a big piece in the equation.

[Zack Barresse]

Definitely AGREED!!

[Simon Lloyd]

They don't, and personally I think this is the biggest miss of the webapp product. The whole point of the webapp is to consume information, yet we can't sanitize the data inputs.

I really hope Microsoft is working on this for the next release, as it's a big piece in the equation.It's possible that the missive was deliberate, getting such things to work cross browser compatible must be a huge headache, even plain old websites don't render the same in every browser, i would also imagine that allowing the use of either native or VBA code to manipulate the app may also pose a huge security risk, although that said i did spot somewhere on the net running excel on linux and directly in a website :), now thats something i would consider much more useful than webapp for training and problem solving purposes but not for the display and accessibility of a closed usergroup like SkyDrive enables.

[Zack Barresse]

Closed group? It's not a closed group. LOL! It's free for anybody.

But I think you're right about the web browser. I'm happy with what we have, but wish for more. :)

[Simon Lloyd]

Closed group? It's not a closed group. LOL! It's free for anybody.

But I think you're right about the web browser. I'm happy with what we have, but wish for more. :)You misunderstood me, i have files in SkyDrive but you can't see them, only the people i have allowed - thats what i meant :)

[Ken Puls]

It's possible that the missive was deliberate, getting such things to work cross browser compatible must be a huge headache, even plain old websites don't render the same in every browser
Fair enough, but it still doesn't mean it doesn't need to be built.


i would also imagine that allowing the use of either native or VBA code to manipulate the app may also pose a huge security risk
Worse than that even. Based on what I got from MS, it's not threadsafe for multi-threading. This would have the potential to bring down your server. To my understanding, VBA will never work on the web.


although that said i did spot somewhere on the net running excel on linux and directly in a website :), now thats something i would consider much more useful than webapp for training and problem solving purposes
I can actually do this today using a published Excel application on citrix (although you'd have to clear login hurdles and such.) It basically runs over RDP.

I guess the thing to me here is that I don't see the webapp isn't really designed as a tool for training purposes like we're doing. I see it as a tool designed for consuming reports interactively, and also for allowing live files on websites. (Say build your own quote, or work with your canned data to show sales for a day.) Ultimately, that data validation needs to be there. At a bare minimum, we need to have the ability to use Excel's native data validation rules/lists.

Just my 2 cents though. ;)

[Zack Barresse]

I think they're great for probably the majority of Office users. I don't know what the percentages are of what features people utilize the most, but I'd be willing to bet that almost everybody could find a user for OWA. And if they're looking for either a cheap or free alternative, I think it beats OpenOffice and the likes. It's somewhat painstaking because it's free and not many people know about it. Sure you can't do everything you do in the full-blown app, but surely that has to be understandable. ?

[Simon Lloyd]

Zack, i'd give yionou the "....surely that has to be understandable.." but as Ken points out it is severely lacking in native functions, sure it's pretty, you can display data (well most of it), can use formulae but the interface doesn't allow for the kind of collaboration that it should without downloading or opening the workbook doesn't email do that? :)..........I know i'm splitting hairs.

[Zack Barresse]

I don't know if you're splitting hairs, I think it's more greedy than anything (for want of more - although I can't blame you!). The way I look at it, there are thousands of users on other applications which do the same thing as OWA. What we do know is that Microsoft (or at least someone finally kicked them in the butt to jumpstart them) is finally on the bandwagon with this type of development. It's not the full-blown version, sure, but it does a ton of the basics, which is what most people need. We're the minority when it comes to the people using Excel (for example). The number of people who use 'advanced' options in Office are less in number than those who don't. It makes perfect business sense to offer them a free alternative, not with all the bells and whistles, but enough to get by. If they want more, buy the full app. It's beautiful strategy and marketing really. Of the people I know here geo-locally, 100% of them could get by with not ever buying Office and just using what is currently offered in OWA. I am literally the only person, probably within a 500 sq mi radius (probably more), which uses anything beyond what OWA delivers.

I think the topic of "what collaboration should be" is another topic altogether though. I don't see it as having much to do with OWA though, at least not real-time collaboration anyway. But the argument that email does that is craziness. At least once a week I have to tell users, "don't open a file right from your email, it is only a temporary file, save it first, THEN open it." Constantly. And that doesn't even cover different versions. Attaching files to emails is just a headache. Put it to the cloud? Yes please!! Google started it first with Google Docs, Microsoft is lagging behind but catching up I think. The cloud keeps it as a central repository. It's a poor man's Sharepoint in a sense (I know, not-really-kinda-maybe). I <3 OWA. :love:

[Simon Lloyd]

.....But the argument that email does that is craziness. At least once a week I have to tell users, "don't open a file right from your email, it is only a temporary file, save it first, THEN open it." Constantly. And that doesn't even cover different versions. Attaching files to emails is just a headache. ...... I <3 OWA. Again i have to conceed that, i've had many people tell me they "downloaded" the file edited it saved it and can't find it anywhere.....etc, i guess that was just a flippant throw away statement but you got my drift. MS may well be on to the perfect marketing of the app, but if the full version does actually make better use of the functions...etc wouldn't they advertise that right along with the free app? unless of course that is part of the marketing stratedgy, kind of "less is more" :)

And as for your "greedy" remark.........DAMN right!, i was spoon fed as a baby and nothing much has changed, when i want something i want it all ;), my wife can testify to that, if a new gadget or piece of tech comes out and i want it (and can afford it) then i want the ultimate in it, not the base or entry model, if i'm having it then it must be fully loaded, my bedside drawer is full of "gotta have" gadgets n stuff :D
EDIT:

my bedside drawer is full of "gotta have" gadgets n stuff Just realised how that sounds, hopefully you're not smutty minded and my virtue is safe ;)

[Zack Barresse]

ROFL!!! Too funny.

And yes, I'm VERY greedy that way too. ;)