MS Excel / VBA help thread

Got a stacked column chart somewhat like this with positive and negative series:

Any easy way to do a stacked waterfall that isn't a horrendous monstrosity of fudged helper series that no one else in the team will be to maintain?

Will this be expected to grow as the data grows? Or will it always be a fixed period of time on the x axis?

(does that make sense)
(are they expected to just plonk some numbers in a table and it'll churn out the graph vs are they expected to "curate" the data and show it between whatever time someone asks for?)

I entirely forgot about this. That works, cheers.

@chez_jay I don't disagree with that (also worked with too many Americans) and actually it seems that VBA happily exports in that format. Trying to convince other people is the challenge though.

X axis won't grow but the number of series might which is what makes me reluctant to build it using a stacked column with transparent segments to get the steps in the right place as, as soon as you start adding in more serious the calculations to deal with variances to work out the size of each segment will change

Get someone else to do it?

I don't think you'll need a series?
I've been mucking around and you should be able to make it a combo chart.
have your line series as your line series and then the other 4 series as stacked column?

How would I sum up values depending on a value in a related cell?

I want to be able to add and change the values in columns A and B and have the amounts appear (and sum, if for the same Age) in the right row in column F:

Some kind of array formula with COUNTIF?

SUMIF will do that, if you already use Countif then SUMIF will be easy to use

Lol, thank you! I must not have been Googling the right terms. :D

Q:

Workbook A pulls data from Workbook B, which can't be tinkered with too much as it feeds the bosses workbook C.

To get the data in a form that is useful to workbook A I had to create some summary Pivot tables in workbook B.

=iferror(getpivotdata(blah,blah,blah,blah)

When these refresh, all the links in workbook A break and the cells give #ref error.

This takes ages to fix each time and my only workaround will be to manually copy the pivots data and paste as values into a new sheet in Workbook A from time to time, using that sheet as the lookup.

Am I overlooking something simple as a fix or could I be approaching this differently?

You can turn off the Getpivotdata aspect of the lookups, not sure if that might help.

How are you linking? Normal references or GETPIVOTDATA? I find the latter always causes issues.

You can probably replicate those summary pivot tables using SUMIFS or a FILTER formula as an alternative.

EDIT: I see it's getpivotdata. I would turn that off and see what happens. Although I'd be tempted to use formulas instead (unless there is loads of data) to avoid having to refresh.

Thanks both @Sam_w & @aggi by turn off getpivotdata do you mean that is a setting (edit: Google tells me it is) I can disable, or replace it with direct cell? ref e.g.

='[workbook]sheet'!cell

Which could work until new rows appear mid pivot I think?

I inherited Worksheet B with lots of nested SUMIF's which I considered to be a very complex way of doing what Pivot Table does, but I guess it might have some advantages afterall. FILTER looks like it might help too.

getpivot is a total fucking pain in the arse.

It's a setting... https://www.excelcampus.com/pivot-tables/turn-off-getpivotdata/

I hate getpivotdata, as I can never see what it is actually doing.

If workbook B is standalone and you cannot fuck with it, can you pull data into A using power pivot/ power query ?
then you can reshape your data to your heart's content, using group by etc.

Alternatively, fuck excel into the sun, link to the table your boss is creating in R and generate an html document that allows you to shine and get out of working there.

Yes, turn off as sam_w has linked.

It depends exactly what you're picking up. It sounds like you really want to be using formulas that are selecting something to pick up the correct cells in your links so new stuff appearing won't screw up your links. Hard to tell without knowing what you're linking to/from though.

Thanks all. Ashamed I never knew it could be turned off.

I'm simply counting within categories and looking to compare.

Worksheet B is monthly report which I've done stuff like tweeked column order to make it so admin just had to copy and paste data in as a 2 min job instead & added in some pivots that are meaningful in the context of my actual work vs the information senior managers want (Worksheet C)

Worksheet A is future forecasting for the same but now includes comparison to the reporting for reflection, hence needing to pull the data through.

Alternatively, fuck excel into the sun, link to the table your boss is creating in R and generate an html document that allows you to shine and get out of working there.

Sounds like a good plan!

Update: looks like Sumif and Countif summary tables will be the tidiest solution.

I'm trying to create a table in summary page tab. The aim is to show, amongst other things, this month's figures for different teams.

I get this month in a cell with =text(today(), "mmm")

What I am struggling with is how to work out is how I then source the correct figures based on that date.

I am after a formula that says if the text in cell range D21:L21 = C2 then C3 = the corresponding figure in the range D23:L23

...if that makes sense?

Lookup 🤦

It sounds like you just want an xlookup but it's not quite clear. It's definitely going to be something doable if you have an example.

Cheers.

I'll check our xlookup too.

Just having a bit of a mental blank. It's one of these annoying things where I'm trying to make everything auto update, but actually it's not that big and could be just as easily done semi automatically.

Seriously what is going wrong here?
I'm following this: https://www.exceldemy.com/calculate-ytd-year-to-date-in-excel/
to work out the sum of the y2d for each team.
see eg: https://docs.google.com/spreadsheets/d/1S8I1DwLBInGWNBN9yLSOf9h2rxqyhwjlNJlbmeChKag/edit?usp=sharing

D9 gives the value you'd expect.
But then E9 and F9 give the same value, rather than totaling their respective rows. How come?

I probably need to make proper table headings(?), but ideally I wanted to have all the headings = the headings from another workbook.

D9 is still wrong, it's £100 too low. I don't really know what your offset formula is doing, not sure why you have the rows part in there which is offsetting it by 1 to the right or why you are picking up b1 as your reference cell rather than the row you want to sum.

I've added in a corrected offset formula and a sumif formula which seems the more obvious way to do it.

Are you also trying to add in a second condition for the team or will it always be in the same order?