And maybe something with dollar signs I don't know

May 16, 2012 11:15 AM

Need help with (possibly?) "indirect" in Excel.

I keep track of some things daily at work in an Excel file. The data is set up like this:

[DATEx]__QuantityA__QuantityB__QuantityC__...__QuantityI
Thing1
Thing2
Thing3
...
Thing21

[DATEx-1]__QuantityA__QuantityB__QuantityC__...__QuantityI
Thing1
Thing2
Thing3
...
Thing21


I have this going back every day for months.

And I also have several (nine) different pages of the same thing with the same structure to keep track of these numbers for several different individuals.


I need to be able to keep a running total on a summary page (in that same format) BOTH for each individual AND everyone added together for the following things:

1) Today
2) Average of the previous week (previous 5 days irrespective of date)
3) Average of the previous month (previous 20 days irrespective of date)
4) Total of a particular, dated month (e.g. January 1-January 31)
5) Grand total

And I need it every day. (Which means 1-3 and 5 will change, and 4 will be static.)

I have no idea how to do this. Each new day adds to the top of the sheet, shifting the previous day(s) down each time. I'm pretty sure I need to use the "indirect" function somehow to make sure that A1 always refers to cell A1, even if the data in A1 shifts down to A2. I just don't know how to use it on so large a scale, taking 100s of cells into account (and summing/averaging them).

I've read pretty much every googlable thing on this and I can't seem to jigger something that works. I am scared and alone in a useless field of data. HELP.

posted by phunniemee to computers & internet (21 answers total)

Are we trying to sum the rows columns?
posted by shothotbot at 11:27 AM on May 16, 2012

No. Each piece of data is on its own. I need to be able to add Quantity A for Thing 1 on Date1 with Quantity A for Thing 1 on Date2 (and Date3 and 4 and so on forever), and then right next to it see Quantity B for Thing 1 on Date1 plus Date2, etc.

Does that make sense?
posted by phunniemee at 11:34 AM on May 16, 2012

Somehow I'm thinking converting the data into rows, then using a pivot table to get your Averages by time frames.

The rows would look like this:

Date___Thing 1
Date___Thing 2
Date___Thing 3

The columns would look like this:

Quantity 1____Quantity 2_____Quantity 3______


Then you could do pivots to your hearts content.

You'd re-do your pivots for the previous 20 days, daily. But they take 1 minute so who cares?

If you do a pivot by date, across the columns, if you right click, then select Group, you can group by day, week, month, quarter, year, etc.

Getting the data organized is the first step, then you can use Pivot Tables like a mo-fo to slice, dice and julienne it all up.
posted by Ruthless Bunny at 12:00 PM on May 16, 2012

Check out absolute references - $A$1 will always point to A1 regardless of insertions or deletions.

Are the number of Things the same each day? If so it will be very easy, otherwise you'll need to either change the format slightly so each row is dated and then use sumifs and averageifs. Or I'm sure there is a classy solution in VBA.
posted by ChrisHartley at 12:01 PM on May 16, 2012

Check out absolute references - $A$1 will always point to A1 regardless of insertions or deletions.

If I understand the situation, this will not work because somehow new rows are inserted on the top every day with the day's new quantities filled in. It depends on how the new data is added, but it could easlly be the case that your reference to January 1 will roll down as days accumulate, when you really want to be pointing to the more recent date.

The 5 and 20 day totals are possible via indirect though arduous. for example =SUM(INDIRECT("B5"),INDIRECT("B6"),INDIRECT("B7")) will sum the cells referenced no matter how things shift around.

To do the totals for all nine people you can make each quantity/thing total a named cell and then some those on you summary page.

But you are really reaching the limits of using excel as a database here. This is a trivial database application but rough (and very fragile) in excel. My vote: hire a programmer for a week.
posted by shothotbot at 12:15 PM on May 16, 2012

Yeah, the way you've described it it looks like your dates are mixed in with the rest of your data, which is not ideal. If you can get the date in a separate column and entered for all values, this will be much easier.

Absolute references will not work with insertions or deletions.
If a column is inserted in front of row B, a reference to $B$1 will change to $C$1.
posted by hot soup at 12:15 PM on May 16, 2012

I uploaded a screen shot of what I have here. The data is exactly the same every day, in exactly the same format, for every person (though some days, some rows/columns might be empty, counting (I hope) as 0).

I know I tried using absolute references once before, but seem to remember it not playing nicely large-scale. I'll definitely give those another go.
posted by phunniemee at 12:17 PM on May 16, 2012

The 5 and 20 day totals are possible via indirect though arduous. for example =SUM(INDIRECT("B5"),INDIRECT("B6"),INDIRECT("B7")) will sum the cells referenced no matter how things shift around.

Yeah, I think this is what I need to do. If there's an easier way (since that would require me going in ten bazillion times to add INDIRECTS and quotes and cells to everything) to do it/copy it over large-scale, that's what I'm hoping to find.

I know a database would be better suited for this. I'm working with what I've got. (And half my office is full of programmers, but they're doing important things. What I'm doing isn't vital...it's more of a "what was the such and such last week, oh hey that's interesting" thing, except I'll need to be able to have it ready at a moment's notice.

Thanks for the suggestions so far.
posted by phunniemee at 12:22 PM on May 16, 2012

A simple way to do this would be to name as a range the column with the dates (if they're not in excel format, then make another column translating them), make a duplicate table elsewhere, have the top left cell be today's date (using the Today function), define everyother date as above cell plus one, and use a lookup function to fill in the info. That way it will change as the daily totals are added and you can easily get the five year and 20 day totals. Automatically.
posted by carmicha at 12:31 PM on May 16, 2012

Note: My idea assumes that there's data for every day of the month including weekends and holidays. If not, then define the other rows as match down x rows. That will get you the most recent 5- or 20- days of data.
posted by carmicha at 12:33 PM on May 16, 2012

What you're planning on doing is definitely workable but messy.
I'm not sure what I'm suggesting here is a good idea, but I'm going to throw it out there.

You can create intermediate sheets that pull only the information you need using indirects, then build your summary sheets off of those. (In other words, using indirects, build a sheet for each user that always displays the last 20 days of information, and build your summary sheets off of the intermediate sheet instead of the original sheet, which you can thing of as the "input sheet". These intermediate sheets will look exactly the same as the originals, but will have the advantage of never being tampered with - no row/column insertions to throw off references.

To make the building of the intermediate sheet easier, you can use temporary spreadsheets to concatenate together the formula text and build up your indirects by piecing together the formula.
posted by hot soup at 12:40 PM on May 16, 2012

My bad-- I started my comment before you posted the screen shot, took a phone call, and then finished it and I mis-imagined your data. Ignore me.
posted by carmicha at 12:45 PM on May 16, 2012

since that would require me going in ten bazillion times to add INDIRECTS and quotes and cells to everything

Well once you do it once, you should be able to make your life easier with copy / replace, and after you do it for one tab you can copy over to the others. In terms of time of implementation vs importance of project, I am guessing its your best option (xkcd).
posted by shothotbot at 12:54 PM on May 16, 2012

To add to my last post - the disadvantage of using indirects is that it can be really messy. One way to mitigate that is to not include the indirect inline in the formula like this:
=SUM(INDIRECT("B5"),INDIRECT("B6"),INDIRECT("B7"))
Creating intermediate sheets makes it easier to build summary sheets, and also makes it easier to spot when something has gone wrong (which it shouldn't, ideally).

It also makes it easier to build your indirect formulas programmatically, without dealing with all the changing text that indirects demand. (It's hard to create indirects because Indirect(b2) doesn't become indirect(b3) when you autofill down).

Assuming your first data sheet is called 'Person1':

1) Create a new excel file.
2) In Sheet1, enter the formula
="Person1!"&CHAR(64+COLUMN())&ROW())
and drag this down to cover all the cells you need.
3) In Sheet2, enter the formula
="indirect("&Sheet1!A1&")"

This should yield a cell that contains the text indirect(Person1!A1), which is the formula you need for your summary sheet. You can click and drag this to get indirect(Person1!A2),etc etc.

To build your working sheet, copy/paste values this into your original workbook, then do a replace all on "indirect" with "=indirect".
You should be able to reuse this for Person3,Person4, etc.

After you've built these sheets, make your summary sheets referring to these sheets, which shouldn't get messed up when new data is inserted into the Person1/Person2 sheets.
posted by hot soup at 1:03 PM on May 16, 2012

Crikey, I just tried to test my instructions out...
="indirect("&Sheet1!A1&")"
should be
="indirect("""&Sheet1!A1&""")"
which makes your text indirect("Person1!A1")

Still not positive this is the best way to make this work though.
posted by hot soup at 1:53 PM on May 16, 2012

hot soup, could you possibly explain the logic behind these various things? I don't know any programming, and only enough Excel to get by.

(Also, did you know that there's another user called hot soup girl?)
posted by phunniemee at 2:30 PM on May 16, 2012

OK, I've solved this in what may be far too complex a way for what you want, but here's a link to the spreadsheet I created to do this, and I'll try to explain it.

So, I replicated your data on sheets I've named after The Beatles, but with only 3 things and 4 quantities. The worksheet where all the magic happens is the one called Index. I guess I called it that because it looks up all the data, before it's put in a pivot table to look at, like Ruthless Bunny suggested.

I've set up a few constants in cells B1:B4, and turned each of them into a named range: People, Things & Quantities. I've also put one in called Gap, which allows for the two extra spaces between each table of daily quantities.

Then, what I want to do is have a nested for loop to loop across all the tables within tables within worksheets. What I want to do is to find the value for each quantity of each thing for each person for each day, and as long as we do this methodically, we can scan across every value in each table within each worksheet and turn that into a row which says which quantity, thing, person and day the value belongs to, as well as what month it's in, whether it's in the previous week or month, and then we can summarise that back up in a pivot table.

So, Excel doesn't do for loops, but what we can do is some clever stuff with numbers and using them as offsets. So I have 4 people in the example sheet. I use column D to loop through those 4 people. I start with the number 1 in D2, and then the row below is a simple IF() function: =IF(D2=People,1,D2+1) (People is a named range for B1, which contains the number of people). So this says, if the cell above me has reached the number of people, then start counting from 1 again, otherwise add 1 to the cell above.

For the next column, I want to go through thing 1 for all 4 people, so I put 1 in E2, then in E3, I put =IF(D3>=D2,E2,IF(E2=Things,1,E2+1)), so I only change the particular thing I'm interested in if I just went back to 1 for people. I can do the same for the quantity column, only incrementing that when I reset the thing column, and then I put the days column in, which only increments when quantity resets.

Now for my lookup, I want to build the address of the top left corner of each subtable within each worksheet (the subtable being a day's things of interest), so I use a lookup in the range PeopleTable to get the name of the sheet, and append "!" to that to show it's referencing a sheet and then "A" to show I'm interested in column A, and then I multiply the day* I'm looking at (as in how many days ago the subtable I'm looking at is) by the number of things + the gap between each subtable, and with a little adjustment, that equals the row where the next subtable starts. But I keep that reference in text in column K because I'll use it a lot.

I use it in column L to look up the date.

And here's where the magic happens: I use the OFFSET() function from the cell named in column K, and offset thing rows and quantity columns to get that value for thing and quantity, and put that in column M.

Then I do some calculations on the date of this activity, and label it with its month, and whether it's in the Previous Week, the Previous Month or Today. (Note the smart-arse way I figure out previous month, which should roll over fine from December to January).

I put it all in one Pivot table (on worksheet Analysis) which shows the average of the values for each period under analysis, but it's probably easier to use one pivot table for each of the 5 criteria.

I hope that all makes sense. It took a lot longer to write up than to figure out. But I enjoyed doing both.

*I labelled the column "Time Ago", which seems sensible enough.
posted by ambrosen at 5:01 PM on May 16, 2012 [1 favorite]

And yes, that kind of technique is somewhat hacky and fairly complex, but actually quite generalisable for Excel programming and can be applied to other times when you need to roll up rows into columns and vice versa.

Offset is an amazing function, too. You can also usefully combine it with Match for good numberwrangling.
posted by ambrosen at 5:15 PM on May 16, 2012

On preview:
ambrosen, that's really awesome and a much better solution than what I proposed before, but I'll just go ahead and post it anyway since I already started explaining it.
For the record, though, I'm pretty sure you should use ambrosen's sheet.

The bulk indirects solution:

There are two components to this - your main issue, which is that inserting new rows means that you can't maintain static references to the first day/first 20 days of data in your summary sheet(s). This problem can be solved, as you suggested, using indirects.
The second challenge is actually writing the indirect formulas.

A possible (inelegant) solution to this is to add 9 more sheets that duplicate the needed data from the original 9 sheets (say, the first 20 subtables from each one). The trick is that if you create these intermediate sheets using indirects, they'll basically be the exact same thing as your original sheets, minus the problem of rows being inserted and throwing off cell references.
This way, you can treat the intermediate sheets as a source table, and you can use these to create your summary tables.
You can do also do what was shown above with the =sum(indirect(),indirect()) kind of nested structure, but that's much harder to write (and read).

Wrapped up in this is the secondary annoyance of writing the indirect formulas to create the intermediate sheets. Since indirects take cell references as text strings, there's no simple way (that I know of) of writing indirects in bulk, because when you write =indirect("a1") that "a1" is just text and Excel has no way of knowing that you want it to change to a2 or b1. The instructions I provided in the last post was just how I'd use Excel to manipulate the text to write the indirect formulas for you.

Here's a google doc demonstrating what I mean (there are 2 sheets):
https://docs.google.com/spreadsheet/ccc?key=0Apq0Q8fqlhiUdHkxYmtOUXNIVEdwRmxFRy0tTmFqdlE

Basically, I'm just using the spreadsheet to put together the formulas. The contents of Sheet2 are the indirect formulas you'd use to create a copy of a worksheet titled "SheetName", just without the equals sign.
By replacing "SheetName" in Sheet1 with your actual worksheet names, you can create these indirects in bulk. You'd just copy and use "paste values" to get the text into a spreadsheet, then use Find (ctrl F) and Replace All, and replace the word indirect with =indirect .

This gets you a spreadsheet that won't get moved around when new data comes in.
Now when you're putting your summaries together, you should reference the data in this sheet instead of the base sheet.
posted by hot soup at 6:24 PM on May 16, 2012

Oh yeah - re: username... I lurked here for two years, finally created an account without putting much thought into a username, and five minutes later saw a post by hot soup girl. So much for originality.

Anyway, if you're interested in bettering your Excel skills, reading carefully through ambrosen's explanation above and working through how that 'Index' tab works (and reproducing a simplified version of it) will get you most of the way to being able to handle 95% of the common problems that come your way in Excel. For everything else, there's AskMeFi....
posted by hot soup at 6:42 PM on May 16, 2012

Hi, guys! I just wanted to say thanks for all the answers...it looks like there was a whole lot of work put into trying to solve this for me, and I really appreciate it. I still haven't had time yet to really get into it at work, but I'm sure this will be tremendously helpful when the time comes.

Thank you!
posted by phunniemee at 6:30 PM on May 31, 2012