INTERSECT in Excel

Anne Troy

New member
Joined
Mar 25, 2011
Messages
23
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Mar 25, 2011
Messages
23
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
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

New member
Joined
Mar 25, 2011
Messages
23
Reaction score
0
Points
0
Okay, I posted, but the file was incomplete. I'll fix it up and upload. Thanks!
 

Anne Troy

New member
Joined
Mar 25, 2011
Messages
23
Reaction score
0
Points
0
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.
 

Zack Barresse

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.

Simon Lloyd said:
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

Super Moderator
Staff member
Joined
Mar 16, 2011
Messages
112
Reaction score
0
Points
0
Location
Oregon, United States
Excel Version(s)
365, Online, iOS, iOS for iPad
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

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
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.
 
Top