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

The Excel Questions Thread - Page 29

post #421 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))))
one VLOOKUP( lookup, table, column, true) would solve this problem Rig Mat Pricing Column 2 24 6.4 33 6.7 40 6.9 46 7.3 ...etc =VLOOKUP(cell you want value returned, $A$1:$B$20, 2, true) or click excel help on how to use this
post #422 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
SUM ( INDIRECT ("C3:C7")) or SUM ( INDIRECT ("defined_range") ) you just need a indirect function within the outer statement.
post #423 of 534
Quote:
Originally Posted by CunningSmeagol View Post
Ross, right? I'm applying.

Havent checked this thread in a while! Yep its Ross. Good luck with your application!
post #424 of 534
what's the best way to get gud at excel without taking classes? i'm reading Excel 2010 Power Programming with VBA, but was wondering if I could supplement this with anything else.
post #425 of 534
Quote:
Originally Posted by uNiCoRnPriNcEsSx View Post
what's the best way to get gud at excel without taking classes? i'm reading Excel 2010 Power Programming with VBA, but was wondering if I could supplement this with anything else.
One way is just experience. The more things you work on, and the more varied thing you work on the better. Another way is to have somebody send you the most complicated and complex spreadsheet they have available and to go through it cell by cell and understand how the whole thing works.
post #426 of 534
Quote:
Originally Posted by Biggskip View Post
One way is just experience. The more things you work on, and the more varied thing you work on the better.

Another way is to have somebody send you the most complicated and complex spreadsheet they have available and to go through it cell by cell and understand how the whole thing works.

Agreed. I got good because I used it at work for 8 hours per day. I also learned VBA from a book and was able to use it at work.
post #427 of 534
Quote:
Originally Posted by uNiCoRnPriNcEsSx View Post
what's the best way to get gud at excel without taking classes? i'm reading Excel 2010 Power Programming with VBA, but was wondering if I could supplement this with anything else.

one way is spelling, VBA does look favorably on slang..
post #428 of 534
Quote:
Originally Posted by dsgNYC View Post
Agreed. I got good because I used it at work for 8 hours per day. I also learned VBA from a book and was able to use it at work.
That's what I did too.
post #429 of 534
Thread Starter 
If anyone's interested, I'm happy to show you how to do things privately in exchange for clothes (ties and shit) you don't want. By that I mean you can send me a sheet you need something done in, and I'll sign a confidentiality agreement and go to town. Then I'll type up an explanation of what was done, so you can impress your boss.
post #430 of 534
Pivot tables pissing me off

Whenever I add a new field to the values it defaults to "sum"

I want it to default to "average"

How the fck do I change this... I am looking at like 20 fields and it's driving me nuts
post #431 of 534
Quote:
Originally Posted by Cool The Kid View Post
Pivot tables pissing me off

Whenever I add a new field to the values it defaults to "sum"

I want it to default to "average"

How the fck do I change this... I am looking at like 20 fields and it's driving me nuts

I'm don't think you can change the default setting from Sum or Count.

Here's a link to an add-in that may help (it's under 'Data Fields'):

http://www.contextures.com/xlPivotAddIn02.html
post #432 of 534
So I've got a model template that I've built. I want to add a print feature to the table of contents page that would allow my boss (or whoever) to select a particular tab from a drop down menu and hit a button that would print off the pre-determined print area from the tab they desire info from. I'm pretty decent in excel, not very experienced with VBA. Recording a macro to accomplish this has been proven very slow and inconsistent. Anyone have a good solution/recommendation for this?
post #433 of 534
Quote:
Originally Posted by RedScarf7 View Post
So I've got a model template that I've built. I want to add a print feature to the table of contents page that would allow my boss (or whoever) to select a particular tab from a drop down menu and hit a button that would print off the pre-determined print area from the tab they desire info from. I'm pretty decent in excel, not very experienced with VBA. Recording a macro to accomplish this has been proven very slow and inconsistent. Anyone have a good solution/recommendation for this?
Let's say that you have three sheets that you want the drop down box to be able to select from, plus a title page where your drop down box is located. Say the three sheets are cleverly titled Sheet1, Sheet2, and Sheet3. Say the title page is titled Title. On the Title Page create a Combo Box from the Form Toolbar. In a 3 Row by 2 Column group of cells, say B2:C4, type the numbers 1-3 into cells B2:B4 and the name of each sheet into cells C2:C4. Right click on the newly created Combo Box and goto Format Control. In the area for "Input Range" select the range C2:C4. In the area for "Cell Link" select cell B5. In cell C5 input the following formula: =VLOOKUP(B5,B2:C4,2,FALSE) Now create a macro and input the following code: Dim Cell_Selector As String ' Sheets("Title").Select Range("C5").Select Cell_Selector = Selection Sheets(Cell_Selector).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub Now go back to the Combo Box, right click, and choose Assign Macro. Assign the Combo Box the macro you just created. Augment as necessary.
post #434 of 534
Quote:
Originally Posted by Biggskip View Post
Let's say that you have three sheets that you want the drop down box to be able to select from, plus a title page where your drop down box is located.

Say the three sheets are cleverly titled Sheet1, Sheet2, and Sheet3. Say the title page is titled Title.

On the Title Page create a Combo Box from the Form Toolbar.

In a 3 Row by 2 Column group of cells, say B2:C4, type the numbers 1-3 into cells B2:B4 and the name of each sheet into cells C2:C4.

Right click on the newly created Combo Box and goto Format Control. In the area for "Input Range" select the range C2:C4. In the area for "Cell Link" select cell B5.

In cell C5 input the following formula:

=VLOOKUP(B5,B2:C4,2,FALSE)

Now create a macro and input the following code:

Dim Cell_Selector As String

'
Sheets("Title").Select
Range("C5").Select
Cell_Selector = Selection
Sheets(Cell_Selector).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub


Now go back to the Combo Box, right click, and choose Assign Macro. Assign the Combo Box the macro you just created.

Augment as necessary.

Will try this, thank-you.
post #435 of 534
sorry if it's already been asked in this thread, but what's the best way to learn excel from scratch?
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread