The Excel Questions Thread

[ginlimetonic]

Originally Posted by Stazy

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

 

[ginlimetonic]

Originally Posted by Huntsman

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.

 

[dshin]

Originally Posted by CunningSmeagol

Ross, right? I'm applying.

Havent checked this thread in a while! Yep its Ross. Good luck with your application!

 

[uNiCoRnPriNcEsSx]

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.

 

[Biggskip]

Originally Posted by uNiCoRnPriNcEsSx

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.

 

[dsgNYC]

Originally Posted by Biggskip

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.

 

[ginlimetonic]

Originally Posted by uNiCoRnPriNcEsSx

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..

 

[Biggskip]

Originally Posted by dsgNYC

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.

 

[CunningSmeagol]

If anyone's interested, I'm happy to show you how to do things privately in exchange for clothes (ties and ****) 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.

 

[Cool The Kid]

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

 

[dsgNYC]

Originally Posted by Cool The Kid

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

 

[RedScarf7]

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?

 

[Biggskip]

Originally Posted by RedScarf7

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.

 

[RedScarf7]

Originally Posted by Biggskip

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.

 

[iceypain]

sorry if it's already been asked in this thread, but what's the best way to learn excel from scratch?