Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Chinaski posted:

Can someone help me out in getting a formula together for this? I'd rather not do it in VBA but I can if necessary.

My first inclination would be to talk you out of doing it on multiple sheets.

Failing that, however, you can sum cells across worksheets. For example, this formula =SUM(Sheet1:Sheet3!A1) will sum all of the A1 cells on the worksheets that are in between Sheet1 and Sheet3 (inclusive).

# ¿ Nov 23, 2009 22:42

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 06:34

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Chinaski posted:

Thanks - that's a help. What reason is there for not doing it across multiple sheets? The goal I think was to have a sheet for each week.

You may not have been giving me all the info on the worksheet, but as I understand it you're to have three columns on a worksheet? Why not one sheet and a column for each week.

There's also less of a chance of a screw up somewhere down the road. For example, if you use the formula I gave you and someone inserts another worksheet in between the first and last sheets, the formula will include that worksheet.

I would put 'Rep Name' and 'YTD' in the first two columns and freeze those two columns. Starting in column C, you've got a column for each week.

In B1, put =SUM(C2:C55)

and so on in B2 & B3.

# ¿ Nov 24, 2009 04:22

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Brown Moses posted:

I was hoping if someone could help me with this one. I've got a small list of 5 hotels, and a spreadsheet of when 300 different people were staying in the hotels. Each person has the date they entered the hotel, the date they left, as well as the hotel name.

Can you describe the layout a little better (worksheets, columns, rows, etc.)?

# ¿ Nov 27, 2009 17:42

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Put each date that you want to track in columns, beginning at K1. For example: K1 = 1/1/2009, L1 = 1/2/2009, M1 = 1/3/2009.

Then, starting in K2 your formula is this: =(K$1>=$A2)*(K$1<$I2)

And copy that formula across the needed columns and rows.

Now the totals for reach column after K should give you the number of rooms occupied for that date.

# ¿ Nov 27, 2009 22:28

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

What do you mean by saying you "want to use that range for each worksheet." Are you trying to total up all of the cells across each worksheet?

If so, you could loop through each worksheet:

code:

Dim prevRow As Range
Dim wksht As Worksheet

Set prevRow = Application.InputBox(Prompt:="Please select the range to reference", _
Title:="Range selection", Type:=8)

For Each wksht In Worksheets
    moveIN = moveIN + WorksheetFunction.Sum(prevRow)
Next wksht

TheAngryDrunk fucked around with this message at 05:37 on Dec 4, 2009

# ¿ Dec 4, 2009 05:30

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Easiest and quickest way to do that is with a pivot table. Use you team names as a row field and also as the data item set to count.

Or you can use an Advanced Filter (Data-->Filter-->Advanced Filter) to return only unique team names (check the "unique records only" box) and then run your Countif on that list.

# ¿ Feb 13, 2010 07:06

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

=IF(MATCH(A2,$B$2:$B$26,0),A2,"")

Column A has your values and Column B is the list you want to check for column A values. If it evaluates to #N/A, that's not a match and you can delete it. Or you can use IFERROR to force #N/A to evaluate to 0 or something like that.

# ¿ Jun 10, 2010 17:33

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

ZerodotJander posted:

All that does is return the value if it is present in Column B.

Isn't that what he needs? Maybe I don't understand the question.

Edit: I think I see. You want to actually return the range where the match is found?

TheAngryDrunk fucked around with this message at 02:19 on Jun 11, 2010

# ¿ Jun 11, 2010 02:15

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Your table should look like this:

0 0%
80 5%
100 10%
120 15%
150 20%

Your formula is: =VLOOKUP(D1,A1:B5,2,TRUE)

Where D1 is the sales amount you want to look up.

This also assumes that the commission percentages apply to all sales once a new level is reached. For example, 5% on all $100 sold. Not 0% up to $80 and then 5% on the $81-100.

# ¿ Jul 11, 2010 17:48

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Not sure I follow. Can you provide real world example with the numbers you've listed?

# ¿ Jul 11, 2010 21:15

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Doesn't the formula I provided you do that?

# ¿ Jul 12, 2010 03:48

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

ufarn posted:

I tried doing this with this code: =IF((B4*B5)>5000, THEN 10%*B4*B5, OTHERWISE 15%*B4*B5), but I get a parse error.

Are you actually including THEN and OTHERWISE? I don't think you need that, if you are.

# ¿ Jul 17, 2010 16:36

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

SUMIF or a pivot table.

# ¿ Aug 29, 2010 00:47

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

factorialite posted:

I have a crazy question.

I've created an excel file with the each nfl team's schedule on it, and on each page I also have a ranking system. In this file, I've created a tab called Alpha Tab that has a column for each team and a column for ratings (so you can put your own rating system there). I want to use a function to lookup the name of whatever tab I'm in and pull the value to the right of that name in the tab "alpha tab."

For example, I am in the Miami tab. I want to write a function that says "I am in miami, let me go to the alpha tab and return the value just to the right of miami."

Is this possible? The spreadsheet makes heavy use of calling value from other tabs, so I know that can be done. However, I did it using brute force, and it'd be nice to learn how to do this.

Thanks in advance!

You need a custom function to spit out the worksheet name on a worksheet.

Here's how to do it: http://www.ozgrid.com/VBA/return-sheet-name.htm

Once you have that, you can use that in your lookup functions.

# ¿ Oct 9, 2010 00:32

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

factorialite posted:

false gives me the N/A error.

If you're getting an N/A with the last argument set to 'false,' but it works with 'true' then the exact value you're looking up isn't on the list.

Also, make sure your formula has expanded to include the new data.

# ¿ Oct 15, 2010 19:32

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

Not sure I understand the first question, but the answer to the second one is 'no.' You can stick a combo box on the worksheet and it will fill in the first item on the data validation list that starts with the same letter, but you can't do it with a cell.

# ¿ Nov 2, 2010 20:48

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 06:34

TheAngryDrunk: Jan 31, 2003; "I don't know why I know that; I took four years of Spanish."

I don't know if you can do that easily. You may be able to access the filter criteria via VBA and display it on the sheet.

# ¿ Nov 3, 2010 04:08

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread