Support NeoGAF

InsaneMonkey · Sep 5, 2017

I think this is probably simple, but I can't seem to figure it out...

Just with that fruit example again, how do I go about using conditional formatting to have it show an 'up' arrow if revenue increased from last month, a 'down' arrow if it decreased, and a 'side' arrow if it stayed within, say, 10% of the previous month?

cpp_is_king · Sep 5, 2017

Much simpler would just be to make the cell green if it's up from previous month and red if it's down

GodParticle · Sep 5, 2017

How knowledgeable are y'all on vba? I have report that's 100% vba where there's a single table that I want to apply a banded colors table style to different columns

Something along this line(this isn't legit code past the listcolumns bit)

listobjects("myTable").listcolumns("colName").tablestyle = "whatever"

If that isn't possible, is there a way to make them all individual tables and have them "linked" together so that if I sort 1, it sorts all of them together?

InsaneMonkey · Sep 6, 2017

cpp_is_king said:
Much simpler would just be to make the cell green if it's up from previous month and red if it's down

How would I go about doing this?

I tried creating two rules where one is <B2 to highlight red and >B2 to highlight green but it doesn't seem to be doing anything.

Quixzlizx · Sep 6, 2017

InsaneMonkey said:
How would I go about doing this?

I tried creating two rules where one is <B2 to highlight red and >B2 to highlight green but it doesn't seem to be doing anything.

I used "highlight cells rules" for that.

Edit: In case you're confused about how I got the yellow formatting:

Also, make sure not to use the $ if you're going to be copying the formatting from column C to the next, otherwise it'll be locked in at comparing itself to column B.

Stuggernaut · Sep 6, 2017

Subbed...

Also, just started playing around with array formulas. They can really slow shit down if you go crazy with them lol.

Quixzlizx · Sep 6, 2017

Stuggernaut said:
Subbed...

Also, just started playing around with array formulas. They can really slow shit down if you go crazy with them lol.

When I do an array MEDIAN formula on a year's worth of data, I have to leave my computer for at least an hour.

God Enel · Sep 6, 2017

Nice thread, subbed. I never understood what arrays are good for and how they work in VBA. When do I use them?

cpp_is_king · Sep 6, 2017

SecretOfEvermore said:
Nice thread, subbed. I never understood what arrays are good for and how they work in VBA. When do I use them?

If you're asking in response to all the discussion of arrays on this and previous page, it's two different things. I mean, deep down it's kinda the same, but "array in vba" is a different high level construct than "array formula in excel".

Are you asking specifically about arrays in VBA or array formulas in Excel?

Edit: Thanks to whoever finally moved this thread to OT

Quixzlizx said:
When I do an array MEDIAN formula on a year's worth of data, I have to leave my computer for at least an hour.

Do you just mean =MEDIAN(A1:A100000) or something like that? Because that's not an array formula

Also, leaving your computer for an hour seems pretty unusual. I've done some pretty gnarly things and I've never had anything take longer than a second or two.

Quixzlizx · Sep 6, 2017

cpp_is_king said:
Do you just mean =MEDIAN(A1:A100000) or something like that? Because that's not an array formula

Also, leaving your computer for an hour seems pretty unusual. I've done some pretty gnarly things and I've never had anything take longer than a second or two.

No, I don't mean that.

oggob · Sep 6, 2017

InsaneMonkey said:
I think this is probably simple, but I can't seem to figure it out...

Just with that fruit example again, how do I go about using conditional formatting to have it show an 'up' arrow if revenue increased from last month, a 'down' arrow if it decreased, and a 'side' arrow if it stayed within, say, 10% of the previous month?

I know this is been raised by some others already, and I would probably suggest to use one of the methods already outlined, but if you really want the "Arrow type look", the default Data/Colour/Icon tools doesn't allow the comparison you specifically want.

You can put in a touch more effort and get a similar result though.

The issue with this, is that you start adding in additional columns that aren't required.
The Arrows are just formatted text in "Wingdings 3" font (as Uppercase and it makes a difference, there is a whole suite of different types of arrows to test in Wingdings 2 and 3, both lower and uppercase values), but not all PCs might actually have Wingdings installed...

You can run any Conditional format off the Arrow Value then, as that cell is already doing the heavy lifting for the comparison between months... but it's just an alternate option if you want a look with some form of symbol.

XiaNaphryz · Sep 6, 2017

There was an older Excel/spreadsheets help OT a few years ago that died, so people can peruse that one for further info that may have come up before:

www.neogaf.com/forum/showthread.php?t=506115

thespot84 · Sep 6, 2017

GodParticle said:
How knowledgeable are y'all on vba? I have report that's 100% vba where there's a single table that I want to apply a banded colors table style to different columns

Something along this line(this isn't legit code past the listcolumns bit)

listobjects("myTable").listcolumns("colName").tablestyle = "whatever"

If that isn't possible, is there a way to make them all individual tables and have them "linked" together so that if I sort 1, it sorts all of them together?

Why is the fact that the sheet uses vba preventing you from using banded rows/columns in the table tools? Is the table being generated dynamically?

Earthpainting · Sep 6, 2017

This might be the place to ask. A few years ago I was sitting in a presentation and in the middle of it, I saw the presenter define a table in Excel, then open up a widow I wasn't familiar with, and used an SQL SELECT command to create a new table on the next sheet. To this day I have no idea how he did that, but it certainly seemed handy to use your active document as your datasource and using SQL to fetch and structure your data. I can only assume it was a plug-in.

InsaneMonkey · Sep 6, 2017

Quixzlizx said:
I used "highlight cells rules" for that.

Edit: In case you're confused about how I got the yellow formatting:

Also, make sure not to use the $ if you're going to be copying the formatting from column C to the next, otherwise it'll be locked in at comparing itself to column B.

Is there a reason I'm getting a "Cannot make changes to the conditional formatting" when I use more than one condition?

Edit: Nevermind, seems to have fixed itself after restarting. Thanks for that.

GodParticle · Sep 6, 2017

thespot84 said:
Why is the fact that the sheet uses vba preventing you from using banded rows/columns in the table tools? Is the table being generated dynamically?

The whole report is generated using VBA. The problem is if I apply a table style, it affects the entire table. For example, I want column 1 & 2 to be one pattern, column 3 & 4 to be a different pattern

thespot84 · Sep 6, 2017

GodParticle said:
The whole report is generated using VBA. The problem is if I apply a table style, it affects the entire table. For example, I want column 1 & 2 to be one pattern, column 3 & 4 to be a different pattern

I always stumble my way through that kind of vba stuff so I'm not much healp, but it seems like it would be trivial to identify the range of the entire report and then format from there.

It does sound like someone using excel when the report functionality of something like Access would be better suited, though.

edit:

instead of table styles, why not use the table generated to define a range, then change the columns as necessary within the range:

not positive on syntax: range(yourTable).columns(1,2).Interior.Color = vBlue

http://software-solutions-online.com/2014/03/26/vba-excel-colors/
http://software-solutions-online.com/vba-excel-colors/

y2dvd · Sep 7, 2017

INDEXMATCH > VLOOKUP

bytemovies · Sep 7, 2017

Hey, Excel newbie here. I want to use Excel to make a budget so I can manage my finances for the upcoming school year, what are some good learning resources available to me? Preferably like a YouTube channel or something, but open to whatever people here recommend.

I tried using the premade stuff but I want to be able to customize my spreadsheet to not only track my expenses but also log dates and times so I can track my cash over months and graph trends (like if my debt is actually declining, how my savings are growing, etc).

thespot84 · Sep 7, 2017

y2dvd said:
INDEXMATCH > VLOOKUP

ALL DAY EVERY DAY

bytemovies said:
Hey, Excel newbie here. I want to use Excel to make a budget so I can manage my finances for the upcoming school year, what are some good learning resources available to me? Preferably like a YouTube channel or something, but open to whatever people here recommend.

I tried using the premade stuff but I want to be able to customize my spreadsheet to not only track my expenses but also log dates and times so I can track my cash over months and graph trends (like if my debt is actually declining, how my savings are growing, etc).

Not that excel can't do that, because with time it can do anything, but it sounds like something along the lines of Mint or YNAB might be a better solution

Pinkuss · Oct 9, 2017

Excel and Access question:

I have an Access database which is pulling data from our debt database via a pass through query, this is then put into a table which is then exported to Excel for the end user to see a summary of this data.

I have one column which is days since allocation, this is simply a datediff between the allocation date and getdate()/the cancellation date should it be cancelled.

I'm however having an issue when this is importing into Excel it's coming through as dates, I've tried adding a copy of this column with a different name/manually changing this in Excel and this is still coming through as dates (manually changing it makes it look fine cosmetically but it's not grouping in a pivot table; if I take the values and paste special values then it groups fine).

The data is also set as numeric in the Access table.

Any ideas?

RustyNails · Oct 9, 2017

Pinkuss said:
Excel and Access question:

I have an Access database which is pulling data from our debt database via a pass through query, this is then put into a table which is then exported to Excel for the end user to see a summary of this data.

I have one column which is days since allocation, this is simply a datediff between the allocation date and getdate()/the cancellation date should it be cancelled.

I'm however having an issue when this is importing into Excel it's coming through as dates, I've tried adding a copy of this column with a different name/manually changing this in Excel and this is still coming through as dates (manually changing it makes it look fine cosmetically but it's not grouping in a pivot table; if I take the values and paste special values then it groups fine).

The data is also set as numeric in the Access table.

Any ideas?

Check the formula in Access. It should be DateDiff ("d", now(), allocation date). Check for column format too and make sure it's a number.

Pinkuss · Oct 9, 2017

Hey, thanks for having a look. The SQL itself is TSQL as it's a pass through query:

"CAST(CASE WHEN [Cancellation Date] IS NULL THEN DATEDIFF(DD,[DCA Allocation Date],GETDATE()) ELSE DATEDIFF(DD,[DCA Allocation Date],[Cancellation Date]) END AS DECIMAL) [AGE]"

The [AGE] column is showing as Number in the table view (This is imported via a Make Table).

Just re-named it age too (From allocation age) so Excel wasn't remembering any formatting and it's come through as date again by default.

BuddyTheBestPuppy · Oct 17, 2017

Hey guys I am working with an excel file with two dynamic worksheets. There are two columns for startDate in both worksheets. I want to compare values in each column.

I was wondering if I could use the DateDiff function like this: DateDiff("h", startDate, completed.Columns(2))!? will I need a for each loop to go through all the dates in the completed worksheet? Is there another way of doing it?

Pinkuss · Oct 19, 2017

Anyone got any ideas on how to do the below?

I'm trying to create a performance breakdown sheet; this pulls data from Access (which pulls data from a SQL server). The data is populated into a table in Excel automatically and I want it so the user can see a breakdown of their performance at each step.

I was originally trying to use pivots with slicers linked to all tables but I can't get the percentages to work.

What I need is a total allocated table at the top (Volume allocated, Volume Cash Collected, %Cash Collected, Debt Allocated, Cash Collected (£) and Cash Collected%.

I then need to break this down by customers called/not called, call outcomes, exclusion reasons etc in a flow diagram with each stage shown on one sheet. I can do this easily using pivots and it looks good but I need to do the % at each stage of the overall total (and this being in another pivot I can't get them to talk)

LordPezix · Jun 29, 2018

Any mega excel wizards out in GAF-land?

My issue.

I am trying to start a counter in VBA that is associated with a table.

I want the counter to count 1's and 0's in rows but I need the counter to start on the 3rd column of the table and continue until the last column -1.

As it is a table the number of rows and columns are always changing due to the data source the table is pulling from always being updated.

Any help on this is much appreciated.

Thank you

Vicetrailia · Jun 29, 2018

I'd rather have a Access thread. It's unfortunate that were using it.

Support NeoGAF

The Excel Help Thread - Pure Excellence

Neo Member

Member

Member

Neo Member

Member

Grandma's Chippy

Member

Member

Member

Member

Member

LATIN, MATRIPEDICABUS, DO YOU SPEAK IT

Member

Member

Neo Member

Member

Member

Member

Member

Member

Member

Member

Member

Member

Member

Member

Member

Similar threads