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

The Excel Questions Thread - Page 29

post #421 of 527
1/15/11 at 7:53pm
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
Reply
Reply

Featured Stories on Styleforum

Random fashion thoughts - aka community ramblings
Finamore at Pitti Uomo 83
Styleforum Watch Appreciation Thread: show, tell, debate.
post #422 of 527
1/15/11 at 7:56pm
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.
Reply
Reply
post #423 of 527
1/16/11 at 10:30pm
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!
Reply
Reply
post #424 of 527
1/18/11 at 2:24pm
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.
Reply
Reply
post #425 of 527
1/18/11 at 3:14pm
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.
Reply
Reply
post #426 of 527
1/18/11 at 5:17pm
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.
Reply
Reply
post #427 of 527
1/18/11 at 5:45pm
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..
Reply
Reply
post #428 of 527
1/18/11 at 6:22pm
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.
Reply
Reply
post #429 of 527
1/19/11 at 12:58am
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.
Reply
Reply
post #430 of 527
1/28/11 at 6:02pm
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
Reply
Reply
post #431 of 527
1/31/11 at 1:52am
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
Reply
Reply
post #432 of 527
2/1/11 at 3:18am
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?
Reply
Reply
post #433 of 527
2/1/11 at 1:01pm
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.
Reply
Reply
post #434 of 527
2/1/11 at 10:38pm
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.
Reply
Reply
post #435 of 527
2/2/11 at 9:56am
sorry if it's already been asked in this thread, but what's the best way to learn excel from scratch?
Reply
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat

Featured Stories on Styleforum

Random fashion thoughts - aka community ramblings
Finamore at Pitti Uomo 83
Styleforum Watch Appreciation Thread: show, tell, debate.
Styleforum › Forums › General › General Chat › The Excel Questions Thread