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

«‹›82 »

me your dad: Jul 25, 2006

TheAngryDrunk posted:

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

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.

# ? Nov 24, 2009 03:47

Adbot: ADBOT LOVES YOU

# ? Apr 26, 2024 13:42

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

Brown Moses: Feb 22, 2002

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.

What I'd like to do is see how many people were in a specific hotel on a specific day, so I can get an idea of what level of occupancy we had on each day for each hotel. I've tried several things, but I can't find a quick way to do it, has anyone got any suggestions?

# ? Nov 27, 2009 16:59

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

Brown Moses: Feb 22, 2002

Worksheet 1
11 columns, but the important info is:
Date Occupied (A)
Month Occupied (C)
Name (E)
Hotel Address (G)
Date Vacated (I)
Month Vacated (J)

One row per person.

I'd like to have a worksheet with one column for each hotel (5 in total), and a row for each day for the last 6 months, approx 183 rows in total. Each row would contain the number of people in each hotel on that specific day.

# ? Nov 27, 2009 17:50

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

Brown Moses: Feb 22, 2002

Thank works perfectly, thanks for your help.

# ? Nov 27, 2009 22:47

aslewofmice: Dec 22, 2003

I have a web service that emails me certain information regarding a customer inquiry. The email body is XML with the clients information. I need to build a "dashboard" in Excel that will retrieve the last 10 emails, parse certain information from the XML, and then display this data in a worksheet, automatically updating when a new email comes in.

Any ideas where to start? I've gone through MSDN documentation but it's a little overwhelming for me. I can do some basic programming in VBA but I've never written anything to interact with other Office applications or XML data...

# ? Dec 4, 2009 04:42

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I have a workbook that has 22ish worksheets on it. I want to use an input box that allows the user to select a range, and then get the sum of that range. After it does that I want to use that range for each worksheet. I'm using this(cutting out a lot):

Dim prevRow as Range

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

moveIN = WorksheetFunction.Sum(prevRow)

This works fine for the first worksheet, but I can't use the inputted range for any of the other worksheets. It just gives me the same values from the first worksheet. Is there something I'm missing.

# ? Dec 4, 2009 05:05

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

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

TheAngryDrunk posted:

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

Sorry, I'll give a better idea for my project. I have one excel file (excelfile1) that has 21 worksheets. There's another excel file (excelfile2) that also has 21 pages. I've set it up so that when I run the macro on excelfile1, it opens up excelfile2 and asks for a range to be selected and input on a worksheet. It then takes the sum of that data and puts it in excelfile1. What I want to do is then use that inputted range, and do the same thing but applied to the second worksheet on excelfile2.

The problem is, it seems to have set the range as let's say $U$51:$U$65 on the first worksheet it's inputted. Is that a little clearer?

# ? Dec 4, 2009 14:56

Sub Par: Jul 18, 2001; Dinosaur Gum

ABombInABowl posted:

I have a web service that emails me certain information regarding a customer inquiry. The email body is XML with the clients information. I need to build a "dashboard" in Excel that will retrieve the last 10 emails, parse certain information from the XML, and then display this data in a worksheet, automatically updating when a new email comes in.

Any ideas where to start? I've gone through MSDN documentation but it's a little overwhelming for me. I can do some basic programming in VBA but I've never written anything to interact with other Office applications or XML data...

I dislike using VBA in Excel to access data in Outlook. I have some reports that do similar things. I generally have incoming emails checked by outlook for the appropriate sender/subject/attachment name/whatever, and when it finds the email, it downloads the attachment to a known directory.

I then have the relevant Excel document execute a macro whenever it's opened to get the most recent X number of XML documents from the known location and do whatever operations are necessary. Hope that helps.

If you want to interface directly with Outlook, you'll need to add the Outlook objects reference, and it's easy enough, but you'll have to make sure to handle errors related to Outlook sucking/being unavailable/closed so your users don't get runtime errors when they do dumb things.

# ? Dec 4, 2009 18:07

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

TheAngryDrunk posted:

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

I worked on this more today, and this macro doesn't work for me. It just gives me the sum from the first worksheet.

# ? Dec 7, 2009 23:24

SURPRISE WITNESS: May 14, 2004; I am pretty sure this is how it is supposed to go.

Alright kids. I'm trying to spice up my College Bowl games contest sheet with some drop-downs to make entry easier.

I've already done the validation to pick between only 2 teams as the winner (to ensure things are spelled correctly for the standings sheet).

Now, the part I've always had trouble wrapping my brain around, code-wise.

There are 34 bowl games. Each player is to assign a point value, from 1-34, to each game, using each number only once. So, I'd like the 'Confidence Points' field of those 34 rows to hold a drop-down list of available numbers that remain, so that they don't select the same number twice. Say I select the first 2 bowl games point values of 1 and 2. I then want the drop-down lists in every field of that row to then only contain 3 through 34. And so on. Seems simple in theory...but code-wise it hurts my brain.

I think I'm ok on the logic of it all, but I'm a little too fuzzy on the inner workings of excel to do what I want. Just a little nudge in the right direction should get me going.

# ? Dec 8, 2009 15:47

Schleep: Apr 4, 2006; Frank, if your fat monkey heart is still beating, then congratulations.

I'm trying to calculate the Mean Profile Depth (MPD) of a chip seal surface texture. The data is given in one column (blue) and I need to calculate the MPD in another column (pink), as shown:

The original equation is:

MPD = [(Max of 1st Half + Max of 2nd Half)/2]-Average of Total

This needs to be a running average, but obviously the size and cell location of the first half and second half of data will change as I move down the column. So far, the only thing that is correct in my Excel equation is the last part, "AVERAGE(D$3:D12)". I need the other portion of the equation to look like "=(MAX($D$3:D7)+MAX(D$8:D12))/2", but this equation would change for each row:

=(MAX($D$3:D7)+MAX(D$8:D12))/2 - AVERAGE(D$3:D12)
=(MAX($D$3:D8)+MAX(D$8:D13))/2 - AVERAGE(D$3:D13)
=(MAX($D$3:D8)+MAX(D$9:D14))/2 - AVERAGE(D$3:D14)
=(MAX($D$3:D9)+MAX(D$9:D15))/2 - AVERAGE(D$3:D15)
=(MAX($D$3:D9)+MAX(D$10:D16))/2 - AVERAGE(D$3:D16)
=(MAX($D$3:D10)+MAX(D$10:D17))/2 - AVERAGE(D$3:D17)
=...

Obviously there is a pattern here, but I don't know if there is a way to make Excel see this pattern. Is there a way to code this equation so I don't have to go in and manually change the cells that MAX calls, for each row?

And I know you guys probably understand this way better than I can, but here's a screenshot of what the equation should look like:

Schleep fucked around with this message at 16:16 on Dec 15, 2009

# ? Dec 15, 2009 16:13

LuckySevens: Feb 16, 2004; fear not failure, fear only the limitations of our dreams

I have a spreadsheet with with rows consisting of a unique string, and to the right of each of the rows is a number (a percentage to be exact). There's around 150 rows and 10 columns.

I need to set up a formula that will search a string value that I input, find it in the data spreadsheet, then look across and compare the first value to another inputted value. If the inputted value is lower than the data value, the cell's column header at the top of the spreadsheet is returned to a cell near the input data. If the value is higher, the formula then checks the next value for the same test, and so forth, until either finding a value, or if it finds a null value (ie the end of the table), it returns a string like "INSUFFICIENT".

The logic is simple but I'm not quite sure how to use excel to do this, will I need VBA script or can I just do a formula?

LuckySevens fucked around with this message at 01:18 on Dec 16, 2009

# ? Dec 16, 2009 01:15

LuckySevens: Feb 16, 2004; fear not failure, fear only the limitations of our dreams

nm I just cooked up a vba script

# ? Dec 16, 2009 10:04

Schleep: Apr 4, 2006; Frank, if your fat monkey heart is still beating, then congratulations.

I couldn't figure out a way to get my other question answered so I've decided to put each parameter in a separate column. All I need it to do now is recognize this pattern in one column:

ROW 5=MAX($D$3:D4)
ROW 6=MAX($D$3:D4)
ROW 7=MAX($D$3:D5)
ROW 8=MAX($D$3:D5)
ROW 9=MAX($D$3:D6)
ROW 10=MAX($D$3:D6)
ROW 11=MAX($D$3:D7)
ROW 12=MAX($D$3:D7)
=...ect

and this pattern in the other column:

ROW 5=MAX(D$5:D7)
ROW 6=MAX(D$6:D8)
ROW 7=MAX(D$6:D9)
ROW 8=MAX(D$7:D10)
ROW 9=MAX(D$7:D11)
ROW 10=MAX(D$8:D12)
ROW 11=MAX(D$8:D13)
ROW 12=MAX(D$9:D14)
=...ect

When I try to fill the column, Excel won't recognize the pattern. Is there a way to do this without having to change each equation by hand?

Edit: The "ROW #" isn't part of the equation, it's just to clarify what row each equation is on. Sorry for the confusion.

Schleep fucked around with this message at 00:21 on Jan 4, 2010

# ? Dec 21, 2009 17:18

Schleep: Apr 4, 2006; Frank, if your fat monkey heart is still beating, then congratulations.

Bump? Is this impossible to do in Excel?

# ? Jan 4, 2010 00:22

Sub Par: Jul 18, 2001; Dinosaur Gum

Schleep posted:

I couldn't figure out a way to get my other question answered so I've decided to put each parameter in a separate column. All I need it to do now is recognize this pattern in one column:

ROW 5=MAX($D$3:D4)
ROW 6=MAX($D$3:D4)
ROW 7=MAX($D$3:D5)
ROW 8=MAX($D$3:D5)
ROW 9=MAX($D$3:D6)
ROW 10=MAX($D$3:D6)
ROW 11=MAX($D$3:D7)
ROW 12=MAX($D$3:D7)
=...ect

This... Excel sucks at. I don't know of many ways around it, but I have a convoluted method that I use from time to time. If someone knows of a better method, by all means, let me know, but here's what I do.

I generally create a new sheet, and use it to generate the pattern I want. In your case, my goal will be to get a column that has values like this:

code:

D4
D4
D5
D5
D6
D6
...

In cell A1, I enter the number 4. I then select A1 and A2 (which is blank) and use the fill handle to fill down to the number of values I need. This will create a column of incrementing numbers with a blank cell between. I then select cell B2 and enter the number 4. Then select B2 and B3, and fill down. You will have two columns now with numbers incrementing, skipping a cell between each.

In C1, I enter the formula ="D"&A1 and in C2 I enter the formula ="D"&B2. Then fill down the C column. The result looks like this:

You will use the values in the C column in the range for your MAX formula, which is accomplished thusly:

=MAX($D$3:INDIRECT(Sheet1!C1))

Where Sheet1 is the name of the sheet where you did all that work. INDIRECT() tells Excel to use the value of Sheet1!C1 as a range parameter. You can then just fill down your formula from there. Modify this procedure to suit your purposes.

quote:

and this pattern in the other column:

ROW 5=MAX(D$5:D7)
ROW 6=MAX(D$6:D8)
ROW 7=MAX(D$6:D9)
ROW 8=MAX(D$7:D10)
ROW 9=MAX(D$7:D11)
ROW 10=MAX(D$8:D12)
ROW 11=MAX(D$8:D13)
ROW 12=MAX(D$9:D14)
=...ect

The dollar sign will prevent Excel from incrementing, so you should remove it, but that won't fix the issue anyway. I would do something similar to the above for this one.

You could also write a UDF if you need something more dynamic. Hope this helps.

# ? Jan 4, 2010 16:12

Schleep: Apr 4, 2006; Frank, if your fat monkey heart is still beating, then congratulations.

This is perfect. The INDIRECT command is what I've been looking for and I'm so thankful for your help

# ? Jan 4, 2010 16:20

Aredna: Mar 17, 2007; Nap Ghost

INDIRECT is what you want, but you can be a little fancier with the math.

This formula will give you the row number to stop at for each row your formula is on: INT((ROW()-3)/2)+3

So something like: =MAX($D$3:INDIRECT("D"&(INT((ROW()-3)/2)+3))) will work for your first formula.

For the second formula, the math to give you the variable row number is: INT((ROW()-4)/2)+5

So your second column would be: =MAX(INDIRECT("D"&(INT((ROW()-4)/2)+5))

7)

Word of warning regarding INDIRECT though: Excel (2003 at least, unsure of 2007) will recalculate it every time it recalculates any cell since it does not know if the formula INDIRECT is pointing to has changed or not. This means when you have lot (thousands) of formulas that Excel may start to operate very slowly with any change due to the massive number of recalculations required.

# ? Jan 5, 2010 00:16

hybr1d: Sep 24, 2002

I'm trying to figure out how to automatically calculate some fields in a power bill spreadsheet. The "simplified" box reduces the number of calculated fields, and is the one I am concerned with. H4 (the baseline kWh per month) varies each month. The 101-130%, 130-200%, and 200%+ fields of H4-H7 are all hand-entered. I need to calculate the 101-130, 130-200, and 200+ percentages of the baseline automatically. How can I do this? I have tried starting by subtracting the baseline first, then moving "up" in values but I always get confused. Any ideas?

# ? Jan 9, 2010 23:24

hybr1d: Sep 24, 2002

Nevermind, figured it out.

# ? Jan 10, 2010 03:33

Erwin: Feb 17, 2006

I have a user who had a one-sheet, one-page spreadsheet where the most complicated formula was subtracting four cells. The spreadsheet opened very slowly and changing any cell took up to 5 seconds, with the mouse cursor changing to the hourglass/busy circle. This even happened with auto calculation off.

I opened the sheet myself, on a much newer computer, and the same thing happened. The problem introduced itself when the user upgraded to Excel 2007. He opened an existing sheet, saved as... for the next month, and saved it as an xlsx file. The older files don't seem to have the problem, but the new one does.

I copied and pasted the entire sheet to a new sheet and the problem was still there. I did paste special -> all and the problem went away. This is how we fixed the problem, but this begs the question, what the gently caress is the difference between paste and paste special -> all?

# ? Jan 14, 2010 17:45

meatpath: Feb 13, 2003

My question:

I have a list of several hundred objects that are posted each week at work from two different outside vendors. Each list is of the same objects, but each vendor abbreviates some of them differently.

For example, vendor #1 sends me a list that looks like this in a column:

code:

Name A
Name B
Name C
Name D
Name E

While vendor #2 sends me a list of the exact same objects that looks like this:

code:

Name A
Nm. B
Nm. C
Name D
Nm. e

Each list has corresponding data in adjacent columns. Basically, I want to be able to combine both lists - since they are listing the same objects - and transfer all data into one worksheet that can be analyzed. The list of objects will be exactly the same each week, so static rules for cell changes can be utilized. The problem is that, when the second vendor abbreviates objects, it throws them out of sync alphabetically and does not allow for easy sorting and combining.

I have never really gotten too deep with macros in Excel before, but I'm thinking all I really need is basically a "Find and Replace" macro that can have a set of rules on what objects are to be replaced each week in list B to make it exactly the same as list A. In other words, every week I will need to change the exact same abbreviation into its full name. Something like "Change 'Nm. B' to 'Name B', then change 'Nm. C' to 'Name C', then change 'Nm. e' to 'Name E'" and so on.

I hope that makes sense. I'm not asking anyone to write the macro, I just don't really know where to start or if this is possible. Obviously, the easier solution would be to just implement consistency between the sources, but we don't really have any control of how the vendors setup their database.

# ? Jan 15, 2010 21:43

Sub Par: Jul 18, 2001; Dinosaur Gum

Can you disregard the "name" prefix or whatever prefix and just get the last X number of characters? If so, you can just use vlookup.

Edit: if I'm not understanding it right, you could probably just turn on the macro recorder and then manually do all the finding/replacing you want, and then stop the recorder and have a look at the code in the vba editor. It's pretty straightforward to adapt the recorded macro for your own purposes.

Sub Par fucked around with this message at 04:11 on Jan 16, 2010

# ? Jan 16, 2010 04:08

meatpath: Feb 13, 2003

Sub Par posted:

Edit: if I'm not understanding it right, you could probably just turn on the macro recorder and then manually do all the finding/replacing you want, and then stop the recorder and have a look at the code in the vba editor. It's pretty straightforward to adapt the recorded macro for your own purposes.

I actually started this process, but then before I got very far I realized that I could solve my problem by simply building a "corrected" list that I keep in a separate file and just copy and paste onto the lovely list every week before actually sorting it alphabetically, that way I can preserve the corresponding data during the sort. Not sure why I didn't think of this solution before, but whatever. Thanks for the response.

# ? Jan 16, 2010 05:03

do it: Jan 3, 2006; don't tell me words don't matter!

I have no real knowledge of Excel, but managed to hack this together:

=IF((G9-J9+K9)<>0,"",(G9-J9+K9))

on the basis that the IF function works like this: =IF(test, "action if test is true", "action if test is false")

It's supposed to check to see if (G9-J9+K9) is equal to 0, and if so, display nothing (not just 0 - important workaround for Google Forms) in the cell. If (G9-J9+K9) is not equal to zero, it's supposed to evaluate (G9-J9+K9). Can anyone see what's going wrong here?

do it fucked around with this message at 03:02 on Feb 13, 2010

# ? Feb 13, 2010 02:28

esquilax: Jan 3, 2003

do it posted:

I have no real knowledge of Excel, but managed to hack this together:

=IF((G9-J9+K9)<>0,"",(G9-J9+K9))

on the basis that the IF function works like this: =IF(test, "action if test is true", "action if test is false")

It's supposed to check to see if (G9-J9+K9) is equal to 0, and if so, display nothing (not just 0 - important workaround for Google Forms) in the cell. If (G9-J9+K9) is not equal to zero, it's supposed to evaluate (G9-J9+K9). Can anyone see what's going wrong here?

Your formula as written will only output 0 or nothing. It outputs nothing if it's not equal to zero ((g9-j9+k9<>0 is true), and if it is zero ((g9-j9+k9)<>0 is false) then it will output zero. You want to switch <> with =, so it will output nothing only when it's zero.

# ? Feb 13, 2010 03:31

do it: Jan 3, 2006; don't tell me words don't matter!

Great, thanks! I don't know where I found the <> operator, but it clearly wasn't the right one.

Another hopefully quick question: We're importing a list of user-defined "team names" and want to find out how many times each team is mentioned in the column to determine how many members it has. I know of =COUNTIF, but that seems to require that you know what you're looking for like =COUNTIF(A1:A100,"Red team"). In this case, because there are over 1500 rows of different user-defined team names that aren't preset, it's hard to give COUNTIF any real criteria. Is there a function that will output something like "X mentioned 2 times" "Y mentioned 10 times"?

do it fucked around with this message at 06:12 on Feb 13, 2010

# ? Feb 13, 2010 05:37

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

mistermojo: Jul 3, 2004

Ok, so I use this function to count across a row:

=(COUNTIF(A22:AB22, "E") + COUNTIF(A22:AB22, "S") + COUNTIF(A22:AB22, "U"))

what I want to do is use that kind of function to count across each row of x number of columns and sum it up. (so instead of A22:AB22 it would be A22:AB22, A23:AB23 etc)

How would I do that?

# ? Mar 25, 2010 19:39

esquilax: Jan 3, 2003

mistermojo posted:

Ok, so I use this function to count across a row:

=(COUNTIF(A22:AB22, "E") + COUNTIF(A22:AB22, "S") + COUNTIF(A22:AB22, "U"))

what I want to do is use that kind of function to count across each row of x number of columns and sum it up. (so instead of A22:AB22 it would be A22:AB22, A23:AB23 etc)

How would I do that?

Do you know array formulas? Type in:
=sum(if(A22:AB24="E", 1, 0))
then hit ctrl+shift+enter, and it will count for rows 22 through 24.

# ? Mar 25, 2010 19:43

mistermojo: Jul 3, 2004

Ok I spaced out there for a second and forgot its as easy as A22:AB26 so never mind

but what does 1, 0 in your function do?

# ? Mar 25, 2010 19:45

esquilax: Jan 3, 2003

The function adds 1 every time each cell is "E", so it's just another way of counting. When the cell is not "E", it adds 0.

And as it turns out you don't need my formula. I thought countif only worked on a single row or column but I was wrong. Your way is simpler.

# ? Mar 25, 2010 19:47

unixbeard: Dec 29, 2004

i manually enter a bunch of date, number values across 2 colums, e.g.

3/1/2010 | 3
2/1/2010 | 7
1/1/2010 | 1

then on the 4th it becomes

4/1/2010 | 2
3/1/2010 | 3
2/1/2010 | 7
1/1/2010 | 1

in another cell i have say the sum of the top 3 rows in the value column. When its the next day and i insert a new date, value observation, i cut the existing data and paste it one row down, then insert the new data above it. As expected Excel tracks all the cell refs, so what was SUM(B5:B7) becomes SUM(B6:B8) and the new value gets ignored.

How do i either make excel always use the values in B5:B7 even if data gets moved around, or is there some other way I could do it? I guess I could always write a macro that looks for the first number in column B then returns the range from that number + 3, but it seems like this is something excel might already deal with.

# ? Apr 5, 2010 16:34

esquilax: Jan 3, 2003

unixbeard posted:

i manually enter a bunch of date, number values across 2 colums, e.g.

3/1/2010 | 3
2/1/2010 | 7
1/1/2010 | 1

then on the 4th it becomes

4/1/2010 | 2
3/1/2010 | 3
2/1/2010 | 7
1/1/2010 | 1

in another cell i have say the sum of the top 3 rows in the value column. When its the next day and i insert a new date, value observation, i cut the existing data and paste it one row down, then insert the new data above it. As expected Excel tracks all the cell refs, so what was SUM(B5:B7) becomes SUM(B6:B8) and the new value gets ignored.

How do i either make excel always use the values in B5:B7 even if data gets moved around, or is there some other way I could do it? I guess I could always write a macro that looks for the first number in column B then returns the range from that number + 3, but it seems like this is something excel might already deal with.

=sum(indirect("B5:B7")) treats "B5:B7" as text, so it will always use that range and keep it from updating.

# ? Apr 5, 2010 16:40

unixbeard: Dec 29, 2004

esquilax posted:

=sum(indirect("B5:B7")) treats "B5:B7" as text, so it will always use that range and keep it from updating.

oh beautiful, thanks.

# ? Apr 5, 2010 17:40

Adbot: ADBOT LOVES YOU

# ? Apr 26, 2024 13:42

Xile: Nov 25, 2005; On the Seventh Day he made the Whopper.

I'm using openoffice but it is close enough to excel.

I run a big festival and I have 1000's of vendors listed in my sheets.. one for outside, inside, and camping.

I have zero training in this stuff..

Basically what I have is a row for each of my vendors.
Columns are labeled:
Lastname _ Firstname _ Address _ City _ State _ Zip _ Phone _ Email _ TAXID _ $Paid _ #ofTables _ Campingpaid _ Campingspot _ Datepaid _ spot#

I do 3 shows a year so I just keep adding columns on the end for $paid-spot#.

I use zero forumulas in this database so far.. because I have no idea what I'm doing.

My question: How can I make a graph that will have the dates along the X and the # of people that sent in their paperwork on that date along the Y.

Dates are formatted: 4/10/2010

Is there a way for the graph to count how many 4/10/2010s there are and put that # above the date? so I can see the average number of dealers sending in on a timeline?

Thanks!

# ? Apr 13, 2010 00:11

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

«‹›82 »