or Connect
Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 28

post #406 of 534
Thread Starter 
Quote:
Originally Posted by MrG View Post
D'oh. Correction to the above post: It worked, but the link includes the file path, which means it will only work with documents that don't move. This one has to be sent out via email and returned.

Then the document would have to be open while the master file is updated. I've never referenced excel tables embedded in word docs, but it it works like other excel files do, then I would use INDIRECT reference only the filename without the path. The only way path-independent links will work is if the referenced book is open (otherwise the program wouldn't know where to look).
post #407 of 534
Quote:
Originally Posted by Connemara View Post
Ultimate n00b question: can anyone recommend a good Excel tutorial (online or maybe downloadable)? I only know the basics and want to learn more.

The problem you are going to run into is that you won't really learn anything unless you actually use it.

It all turns into subtle combinations of features and if you aren't actually using them to do things (even if it is just forced practice "assignments"), you won't develop the skills in a permanent manner.

You have to find a project or something to work on that requires you to do weird shit and then you can jump through random tutorials and stuff looking for ways to accomplish that weird shit in the most elegant way.
post #408 of 534
Quote:
Originally Posted by CunningSmeagol View Post
Then the document would have to be open while the master file is updated. I've never referenced excel tables embedded in word docs, but it it works like other excel files do, then I would use INDIRECT reference only the filename without the path. The only way path-independent links will work is if the referenced book is open (otherwise the program wouldn't know where to look).

I couldn't even get the links to keep updating after I closed and reopened the file, even without moving it (and by default they do seem to contain the full path), so I think it's not even getting to the problem you're mentioning.
post #409 of 534
Quote:
Originally Posted by Connemara View Post
Ultimate n00b question: can anyone recommend a good Excel tutorial (online or maybe downloadable)? I only know the basics and want to learn more.

I just took an excel class in b-school. this is the book hat was used.

Microsoft Excel 2007, Comprehensive," ISBN-13: 978-1-4239-0585-1

This book covers the material that is tested in the Microsoft Office Specialist Excel certification.
It covers all the most common functions, excel tables, pivot tables, single and 2 variable data tables, solver, some macros...

The book has a lot of cases that you can work through. It wont make you an excel expert but I think you can pick up a lot of the most useful items.
post #410 of 534
Thread Starter 
Quote:
Originally Posted by dshin View Post
I just took an excel class in b-school. this is the book hat was used.

Microsoft Excel 2007, Comprehensive," ISBN-13: 978-1-4239-0585-1

This book covers the material that is tested in the Microsoft Office Specialist Excel certification.
It covers all the most common functions, excel tables, pivot tables, single and 2 variable data tables, solver, some macros...

The book has a lot of cases that you can work through. It wont make you an excel expert but I think you can pick up a lot of the most useful items.

Ross, right? I'm applying.
post #411 of 534
Guys,
I'm trying to add a checkbox to a protected tab so that people can select/unselect a certain number of rows out of the total in the table.

I made sure the checkbox object is not locked but when i protect the sheet I get the 'this sheet is protected' pop up. I also tried unlocking the cell the checkbox is floating over and no better.

Any ideas? Perhaps there is a better way to do this?
post #412 of 534
Weird question... How come this formula works in Excel 2010 but doesn't work in Excel 2003 (It has a compatibility pack)? =((IF('Rig Mat Pricing'!E15=2,IF('Rig Mat Pricing'!E14<20,5.8,IF('Rig Mat Pricing'!E14<24,6.4,IF('Rig Mat Pricing'!E14<33,6.7,IF('Rig Mat Pricing'!E14<40,6.9,IF('Rig Mat Pricing'!E14<46,7.3,IF('Rig Mat Pricing'!E14>45,11.3,0)))))),IF('Rig Mat Pricing'!E15=3,IF('Rig Mat Pricing'!E14<20,6.88,IF('Rig Mat Pricing'!E14<24,7.4,IF('Rig Mat Pricing'!E14<33,8,IF('Rig Mat Pricing'!E14<40,8.6,IF('Rig Mat Pricing'!E14<46,9.3,IF('Rig Mat Pricing'!E14>45,12.4,0)))))),IF('Rig Mat Pricing'!E15=4,IF('Rig Mat Pricing'!E14<20,8,IF('Rig Mat Pricing'!E14<24,8.6,IF('Rig Mat Pricing'!E14<33,9.3,IF('Rig Mat Pricing'!E14<40,11.1,IF('Rig Mat Pricing'!E14<46,11.8,IF('Rig Mat Pricing'!E14>45,13.6,0)))))),0)))+IF('Rig Mat Pricing'!E17="yes",1.9))+(IF('Rig Mat Pricing'!E13>8,'Rig Mat Pricing'!E13*0.25,IF('Rig Mat Pricing'!E13<8,'Rig Mat Pricing'!E13*-0.1,0))))
post #413 of 534
Quote:
Originally Posted by Stazy View Post
Weird question... How come this formula works in Excel 2010 but doesn't work in Excel 2003 (It has a compatibility pack)? =((IF('Rig Mat Pricing'!E15=2,IF('Rig Mat Pricing'!E14<20,5.8,IF('Rig Mat Pricing'!E14<24,6.4,IF('Rig Mat Pricing'!E14<33,6.7,IF('Rig Mat Pricing'!E14<40,6.9,IF('Rig Mat Pricing'!E14<46,7.3,IF('Rig Mat Pricing'!E14>45,11.3,0)))))),IF('Rig Mat Pricing'!E15=3,IF('Rig Mat Pricing'!E14<20,6.88,IF('Rig Mat Pricing'!E14<24,7.4,IF('Rig Mat Pricing'!E14<33,8,IF('Rig Mat Pricing'!E14<40,8.6,IF('Rig Mat Pricing'!E14<46,9.3,IF('Rig Mat Pricing'!E14>45,12.4,0)))))),IF('Rig Mat Pricing'!E15=4,IF('Rig Mat Pricing'!E14<20,8,IF('Rig Mat Pricing'!E14<24,8.6,IF('Rig Mat Pricing'!E14<33,9.3,IF('Rig Mat Pricing'!E14<40,11.1,IF('Rig Mat Pricing'!E14<46,11.8,IF('Rig Mat Pricing'!E14>45,13.6,0)))))),0)))+IF('Rig Mat Pricing'!E17="yes",1.9))+(IF('Rig Mat Pricing'!E13>8,'Rig Mat Pricing'!E13*0.25,IF('Rig Mat Pricing'!E13<8,'Rig Mat Pricing'!E13*-0.1,0))))
I didn't parse through the whole formula, but how many "nested ifs" do you have? I know that in Excel 2003 the limit is either 9 or 10. Not sure about Excel 2010 as I haven't used it much. Also, in Excel 2003 there is a limit on how many characters you can program into a single cell for a formula. Again, not sure how this applies in 2010 but something to consider.
post #414 of 534
Quote:
Originally Posted by Neo1824 View Post
Guys,
I'm trying to add a checkbox to a protected tab so that people can select/unselect a certain number of rows out of the total in the table.

I made sure the checkbox object is not locked but when i protect the sheet I get the 'this sheet is protected' pop up. I also tried unlocking the cell the checkbox is floating over and no better.

Any ideas? Perhaps there is a better way to do this?
Are you using the check box from the "Forms" toolbar or from the "Control Toolbox" toolbar? It sounds like the former. If, so you need to make sure that any cells on your sheet that have a dependence on the cell linked to the check box are also not locked. If doesn't work, post again and we'll see what we can figure out.
post #415 of 534
Quote:
Originally Posted by Biggskip View Post
I didn't parse through the whole formula, but how many "nested ifs" do you have? I know that in Excel 2003 the limit is either 9 or 10. Not sure about Excel 2010 as I haven't used it much. Also, in Excel 2003 there is a limit on how many characters you can program into a single cell for a formula. Again, not sure how this applies in 2010 but something to consider.
That's what I feared. I have probably at least 10 "nested ifs". I guess I'll have to try and figure out another approach to getting what I want. Shittttttt.
post #416 of 534
Quote:
Originally Posted by Stazy View Post
That's what I feared. I have probably at least 10 "nested ifs". I guess I'll have to try and figure out another approach to getting what I want. Shittttttt.
Also, just looking at your formula again, you may want set up a small table and use the "Match" function. You may be able to set this up so that depending on the value of 'Rig Mat Pricing'!E14 it would return the appropriate value.
post #417 of 534
Quote:
Originally Posted by Biggskip View Post
Also, just looking at your formula again, you may want set up a small table and use the "Match" function. You may be able to set this up so that depending on the value of 'Rig Mat Pricing'!E14 it would return the appropriate value.
That's a good idea. Thanks
post #418 of 534
Thread Starter 
Quote:
Originally Posted by Stazy View Post
That's a good idea. Thanks
Yeah dude. Look into setting up arrays within a SUMPRODUCT function. Nested IF's are almost never necessary.
post #419 of 534
Is there a way to reference a cell within a formula in a truly relative fashion? By this I mean say you had SUM(B1:B7), but what you really wanted to do is not call cell B1 as the start of the range, but just call it in some way that means "from the cell in this row, but six columns to the left." 'm sure I've done that in VBE in R1C1 back in the day, but not from a cell formula. My boss needed to do that, and I figured out a way to do it with INDEX, but it was inelegant. ~ H
post #420 of 534
Quote:
Originally Posted by Huntsman View Post
Is there a way to reference a cell within a formula in a truly relative fashion? By this I mean say you had SUM(B1:B7), but what you really wanted to do is not call cell B1 as the start of the range, but just call it in some way that means "from the cell in this row, but six columns to the left." 'm sure I've done that in VBE in R1C1 back in the day, but not from a cell formula. My boss needed to do that, and I figured out a way to do it with INDEX, but it was inelegant.

~ H

You need the OFFSET function
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread