Ask me anything about Microsoft Excel - Page 21 - Other Topics Forum - Discuss Pop Culture, Arts and Entertainment

Not really sure. I use the existing connections to open a query editor. In that editor I have to select the tables I want, connect the tables through keys and then pick the fields from the tables. Sounds more difficult then it is, the main challenge is knowing what to put into my excel and finding the one in a million field in the correct table.

Quote

12-27-2011 , 06:28 PM

#502

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Randiek

It's hard to say without seeing the tables/keys but you should be able to do something like

Code:

SELECT customerName, loadDischargeTable.mainColumn, sum(minutes) 
FROM customerTable, minutesTable, loadDischargeTable 
WHERE customerTable.ID = minutesTable.customer_id 
   AND customerTable.ID = loadDischargeTable.customer_id 
GROUP BY customerTable.customerName, loadDischargeTable.mainColumn

Quote

12-28-2011 , 12:13 AM

#503

goofball

Beat Nate Silver

Join Date: Oct 2003 Posts: 20,426

Quote:

Originally Posted by Peter Harris

discharge load, lol.

I use Excel at work and am totally embarrassed at how little i do with it.

I kid you not, I am considered the 'expert' as I can use 'countif'. Seriously. shudder.

wait till you show them countifs

Quote

12-28-2011 , 02:58 AM

#504

Randiek

veteran

Join Date: Sep 2007 Posts: 3,101

Quote:

Originally Posted by zomg

It's hard to say without seeing the tables/keys but you should be able to do something like

Code:

SELECT customerName, loadDischargeTable.mainColumn, sum(minutes) 
FROM customerTable, minutesTable, loadDischargeTable 
WHERE customerTable.ID = minutesTable.customer_id 
   AND customerTable.ID = loadDischargeTable.customer_id 
GROUP BY customerTable.customerName, loadDischargeTable.mainColumn

Thanks, I will look into that. Will also investigates these sumifs/countifs functions, never heard of em.

That is my current limit on excel usage; I don't know any more functions that I can use so I do not know how to improve my knowledge (besides learning to write macro's, but that is not for me atm since I can barely read some code, let alone write it). Any clue how I could learn. All the advanced excel stuff I can find online teaches how to learn IF(). Apparently knowing how to copy/paste and sum up counts as the basics nowadays

Quote

12-28-2011 , 05:44 AM

#505

PocketChads

guaranteed 42-10 finish

Join Date: Jul 2006 Posts: 22,451

are you ****ing serious this has over 500 threads you are ****ing serious jesus ****ing christ

Quote

12-29-2011 , 11:03 AM

#506

stanek

grinder

Join Date: Apr 2006 Posts: 452

Say I want to put a formula in D5 that uses A5 + B5 + C5.

Is there a way to write it so that it doesnt use the numbers? ie Sum( thisRow(A) + thisRow(B) + thisRow(C) ) ?

Quote

12-29-2011 , 11:28 AM

#507

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by stanek

Say I want to put a formula in D5 that uses A5 + B5 + C5.

Is there a way to write it so that it doesnt use the numbers? ie Sum( thisRow(A) + thisRow(B) + thisRow(C) ) ?

Code:

=SUM(INDIRECT("A"&ROW()&":C"&ROW()))

Although i'd be interested to hear why you need to do it like that?

Quote

12-29-2011 , 11:40 AM

#508

baumer

Pooh-Bah

Join Date: Oct 2004 Posts: 4,061

stanek are you trying to make a formula that is relative for each row?

Like for D4 it'd be A4+B4+C4
and for D6 it'd be A6+B6+C6?

If so just put =sum(A1+B1+C1) in cell D1, then highlight D1 and select every cell below it that you want that particular formula in and press Ctrl+D to "Fill Down" the formula and it will put in the correct numbers for each row.

This is probably the most basic Excel stuff so I'm sorry if this isn't what you meant but I thought it best to mention it.

Quote

12-29-2011 , 12:19 PM

#509

stanek

grinder

Join Date: Apr 2006 Posts: 452

Quote:

Originally Posted by zomg

Code:

=SUM(INDIRECT("A"&ROW()&":C"&ROW()))

Although i'd be interested to hear why you need to do it like that?

I found another method. There is an option called R1C1 that you can turn on in tools and it replaces the A B C across the top with 1 2 3's

Code:

=
IF(RC[-2] = "Hydro1"			,"HWE",
IF(RC[-2] = "Carpentry"			,"SubCont",
IF(RC[-2] = "Hambrg"			,"PayRoll",
""
)))

The problem with this method is it changes the way all formulas are displayed and is a little bit harder to read.

Using the Indirect method I get

Code:

=
IF(INDIRECT("B"&ROW()) = "Hydro1"		,"HWE",
IF(INDIRECT("B"&ROW()) = "Carpentry"		,"SubCont",
IF(INDIRECT("B"&ROW()) = "Hambrg"		,"PayRoll",
""
)))

Reason:
I am trying to setup a spreadsheet that will automatically "categorize" a cheque when the name is typed in. If it doesn't auto populate - ie. its a rare cheque or someone new - you'll have to type it in manually and overwrite the formula.
I want to make this formula easy to use by being a copy and paste formula where you dont have the luxery to use the pull down fill method.

Question:

Is there anyway to take this one step further and say we put this formula in cell A1 on sheet 'Formula'. So that it can be called at anytime by using =formula!$a$1 ??

Last edited by stanek; 12-29-2011 at 12:28 PM. Reason: Thank you Btw

Quote

12-29-2011 , 12:54 PM

#510

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by stanek

I found another method. There is an option called R1C1 that you can turn on in tools and it replaces the A B C across the top with 1 2 3's

Code:

=
IF(RC[-2] = "Hydro1"			,"HWE",
IF(RC[-2] = "Carpentry"			,"SubCont",
IF(RC[-2] = "Hambrg"			,"PayRoll",
""
)))

The problem with this method is it changes the way all formulas are displayed and is a little bit harder to read.

Using the Indirect method I get

Code:

=
IF(INDIRECT("B"&ROW()) = "Hydro1"		,"HWE",
IF(INDIRECT("B"&ROW()) = "Carpentry"		,"SubCont",
IF(INDIRECT("B"&ROW()) = "Hambrg"		,"PayRoll",
""
)))

I personally hate RC referencing so I never use it.

A few things

1. Using =IF($B1 = "Hydro1","HWE",IF($B1="Carpentry","SubCont",IF($B1 = "Hambrg","PayRoll","")))

Will maintain the row when copy and paste anywhere in the sheet so if that formula was in cell F1 and you copy and pasted it to X100 or F100 or A100 it would change the $B1 to $B100

2. I'm pretty sure there is no way to reference a formula of another cell without using VBA, with that in mind if you are comfortable using VBA/User Defined functions there are a number of ways you could achieve this

a. Right click on any sheet tab and go "view code", go to Insert > Module then paste

Code:

Public Function myFormula() As String
    Select Case Range("B" & ActiveCell.Row)
        Case "Hydro1"
            myFormula = "HWE"
        Case "Carpentry"
            myFormula = "SubCont"
        Case "Hambrg"
            myFormula = "PayRoll"
    End Select
End Function

b. Then in any cell you can type =myFormula() and it will give you the result

Quote

12-29-2011 , 01:58 PM

#511

stanek

grinder

Join Date: Apr 2006 Posts: 452

Kind of surprised that with the $b1 reference it is copyable across sheets and such. It also has the advantage over the indirect method if you decide to insert a row before B it'll grow with it.

Unfortunately I am working with Excel Starter and VBA isn't an option.

Thanks again for your help.

Quote

12-29-2011 , 04:38 PM

#512

stanek

grinder

Join Date: Apr 2006 Posts: 452

I had about 120 records only to have excel shout at me "You're only allowed 64 nested statements"!!!

I ended up rediscovering VLOOKUP and it is also much easier to add additional entries.

Quote

12-29-2011 , 04:44 PM

#513

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by stanek

I had about 120 records only to have excel shout at me "You're only allowed 64 nested statements"!!!

I ended up rediscovering VLOOKUP and it is also much easier to add additional entries.

Haha you did 64 nested if statements?! Obviously you should use a VLOOKUP, I thought you only had 3.

Quote

01-03-2012 , 07:39 PM

#514

Redux

old hand

Join Date: Nov 2008 Posts: 1,414

Excel noob here. I have numbers running on column A on rows 1-50 and want to have the average of those numbers up to each row to the right of the number of that row. Ie. for row n I want the average of numbers of rows 1-n. I figured it'd be something like =AVERAGE(A1:An) but that doesn't work.

Quote

01-03-2012 , 07:48 PM

#515

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by Redux

=AVERAGE($A$1:A1) copied down

Quote

01-04-2012 , 02:40 PM

#516

splashpot

Carpal \'Tunnel

Join Date: Nov 2004 Posts: 8,203

For fantasy sports purposes. If I have a spreadsheet with several tabs, one for each position, say for NBA it's PG, SG, SF, PF, and C. Each tab has player name, expected fantasy points, and salary.

If I have a salary cap of $100,000 and need to build a roster of PG, PG, SG, SG, SF, SF, PF, PF, C, is there a way for excel to give me the optimal lineup? The only way I can think of is to write some kind of script that tries every possible combination. Can this be done in excel or do I need to do it in a database?

Quote

01-04-2012 , 02:49 PM

#517

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by splashpot

I would probably write some sort of script to compare them. In saying that, I thought most fantasy NBA leagues were roto scored so you need to have a good mix of pts, assits, rebounds, threes, steals etc etc or does each player have one 'expected points' that is equal among all positions?

if you can write out in words how you would do it, i can help convert it to VBA

Quote

01-04-2012 , 02:55 PM

#518

microstakesrave

old hand

Join Date: Mar 2010 Posts: 1,719

Love the fact that zomg is back and active in this forum! Anyway, got a question and hopefully it's doesn't involve a script ...

Note that this is google docs, but is sooo similar to excel

I have a log for the month with tabs labeled - 1st, 2nd, 3rd, etc. On tab last tab is labeled 'All'. I want all the data from the 1st-31st listed in the final sheet. Currently I have an array setup just for the 2nd ..

=arrayformula(filter('2nd'!B5:O19; '2nd'!O5:O19>0))

Any easy way just to say '1st'-'31st'? Or am I going to have to list it all in the formula??

Quote

01-04-2012 , 02:57 PM

#519

stinkypete

Carpal \'Tunnel

Join Date: Jul 2004 Posts: 15,783

splashpot,

the optimal lineup problem is NP-hard and excel doesn't have any built-in mechanisms for a good solution. with that said, discussing this stuff here clearly isn't in our best interest so stfu!

Last edited by stinkypete; 01-04-2012 at 03:07 PM.

Quote

01-05-2012 , 02:26 AM

#520

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by microstakesrave

Ah thanks for the support, i don't have much experience with google docs but i know in excel you can do something like =sum('1st:31st'A1) I just got home and am not in the mindset to look at this but i'll set up a google doc tomorrow and have a look.

In words what is the formula trying to accomplish? i'm not familiar with the 'filter' command in google docs

Quote

01-05-2012 , 02:27 AM

#521

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by stinkypete

splash, feel free to PM me if you want a private solution

Quote

01-05-2012 , 10:43 AM

#522

microstakesrave

old hand

Join Date: Mar 2010 Posts: 1,719

Quote:

Originally Posted by zomg

I was thinking of something like that, but I thought that they would need some sort of numbering system. i.e. Sheet1, Sheet2, Sheet3, etc.

What I'm trying to accomplish is a list of all the information in tabs 1st to the 31st on the one tab. So instead of looking at every single tab for information. You have it on one. The 'filter' function is saying that I only want B5:O19. All the other stuff is gibberish.

Make sense? I can share a doc with you if you e-mail me a gmail account.

Quote

01-05-2012 , 11:47 AM

#523

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by microstakesrave

I was able to get a list of all the customers by doing

Code:

=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE((CHAR(32)&IFERROR( FILTER('1st'!B5:B19;NOT(ISBLANK('1st'!B5:B19))) ; "" ) )&CHAR(13) ;(CHAR(32)&IFERROR(FILTER('2nd'!B5:B19;NOT(ISBLANK('2nd'!B5:B19))) ; "" ) )&CHAR(13) ;(CHAR(32)&IFERROR(FILTER('3rd'!B5:B19;NOT(ISBLANK('3rd'!B5:B19))) ; "" ) )&CHAR(13) )) ; CHAR(13)))

it should be possible to extend that to get all the information (you would probably need to get rid of the transpose/split functions and increase the range to B5:O19 and also add all the other sheets

Quote

01-06-2012 , 02:03 PM

#524

willy85

centurion

Join Date: Jan 2007 Posts: 101

Does the Combo Box feature in excel allow users to start typing a word which excel would then auto complete? I use data validation to let users choose an item from the list, but instead of having them search through the drop down list I want them to have the option of typing in the first few letters of the item and excel auto completing the rest.

Quote

01-06-2012 , 04:00 PM

#525

zomg

Excelling at Flash

Join Date: Nov 2006 Posts: 1,502

Quote:

Originally Posted by willy85

Short answer: no

There are several workarounds you can do though

1. Put the list in the cells above the drop down and hide those rows then go to tools > options > enable autocomplete (or in 2007+ click the office button in the top left and go to excel options). This works because excel's default autocomplete looks at the cells in the rows above and autocompleted based off that

2. If you can't do that you would need to right click on the ribbon and go to "customize access ribbon" go to the developer options and add "Insert Controls" from here you can add a combo box control which has an option for matchEntry

Quote

Page 21 of 120

First

11 16 17 18 19 20 21 22 23 24 25 26 31 41 71

Last

Post Reply Subscribe

...

Page 21 of 120

First

11 16 17 18 19 20 21 22 23 24 25 26 31 41 71

Last