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

G-Dub: Dec 28, 2004; The Gonz

Do you work for Brembo?

Open it in AutoCAD I presume, or embedded in Excel somehow?

Google tells me you're not the only person with this problem: click here for a horrible website

G-Dub fucked around with this message at 20:46 on May 7, 2009

# ¿ May 7, 2009 20:43

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 06:50

G-Dub: Dec 28, 2004; The Gonz

The Mechanical Hand posted:

Originally, mistakenly, posted in the general programming thread, but anyway...

Need some help with an excel problem I've come across.

I have a drop down list with names of cities in it.

I want to set this up so when you pick a city from that list another field will populate with an address associated with that city. I know it has to do with formulas and all that but I am totally lost. Any help?

You probably want to look at the VLOOKUP function within Excel. I presume there is one address per city? I would have a table of City|Address and then your VLOOKUP would look like:

VLOOKUP(<Cell ref of dropdown>,<range of table>,2)

I presume everything recalculates when one cell is changed, therefore the address will update when the drop down changes.

# ¿ Dec 9, 2010 21:34

G-Dub: Dec 28, 2004; The Gonz

Is it the same cell in each workbook you are wanting to get? If you can post some sample source data and the desired sample format I will take a stab at it.

# ¿ Jan 5, 2011 20:05

G-Dub: Dec 28, 2004; The Gonz

Could you not just use ActiveRange? I'm sure such a thing exists

# ¿ Jan 12, 2011 21:00

G-Dub: Dec 28, 2004; The Gonz

Thel posted:

Related, are there any useful resources for debugging VBA in excel?

I don't mean to sound cheeky, but do you know about breakpoints?

# ¿ May 11, 2011 22:40

G-Dub: Dec 28, 2004; The Gonz

Thel posted:

Yes, I couldn't get them to work for a while (I don't know why).

e: Given that I just spent the last ten minutes being a blithering idiot, it's entirely possible that when breakpoints weren't working I'd managed to completely miss Excel's macro security warning.

Also, they don't work very well for Worksheet_Open()

Very true about Worksheet_Open(), so I will share with you my proudest discoveryin Excel programming (I do not do Excel programming). Call an undefined function at the start of Worksheet_Open(). Essentially just type a word that doesn't mean anything. Exel should then give you the option to end execution or debug. Choose debug and you have a break at the start of the event.

# ¿ May 14, 2011 15:06

G-Dub: Dec 28, 2004; The Gonz

Goodpancakes posted:

I am not the best Excel user ever, and I have a few questions I can't find answers for.

I have two databases of information. In both of these databases I have Latitude, Longitude, "Heat Flow" and a bunch of other columns. Now I need to compare both of these databases with each other, and find which points have different heat flow numbers. Currently I have both databases mixed into one huge one, and I figured out how to sort the Latitude and Longitude so both databases are lined up by location. This way I see that one database reports one number for heat flow, and the other one reports another one (usually the same) at the same co-ordinates of lat and long.

However there are a bunch of unique entries that do not occur in both data sets. Is there an easy way for excel to locate these and remove them? I am not sure how this works since both the latitude and longitude define one point. So if lat is x and long is y AND no other points have BOTH lat x and long y sort of thing.

I think this would be easier done in a database. What database is everything stored in?

# ¿ Sep 4, 2011 12:36

G-Dub: Dec 28, 2004; The Gonz

Why don't you just generate a quote for each bandwidth and term? It doesn't seem like a lot of data.

# ¿ Jan 19, 2012 21:05

G-Dub: Dec 28, 2004; The Gonz

Cancer Wad posted:

Couple of reasons: I intend to add quite a few additional bandwidth increments once the framework is in place. Also the stretch between bandwidth increments is going to be large enough (10M - 1G) that only a small portion will be of interest to a given customer. I also plan to add other services down the road, so I need this chunk to be a template for other products that will demand a little more customization.

If you are going to be adding new bandwidths then checkboxes are a bit static. Look in to a multi-select listbox (sorry for making things more complicated). This means to add new bandwidths all you need to do is update the source for the listbox and not actually amend the layout of the form. It's then quite straightforward to establish all the selected items in the listbox and iterate through them to do your calculations.

EDIT: Off the top of my head something like...

code:

Dim varItem as variant

For Each varItem in lstBandwidths.ItemsSelected
' Do something with lstBandwidth.ItemData(varItem)
Next

G-Dub fucked around with this message at 21:23 on Jan 20, 2012

# ¿ Jan 20, 2012 21:17

G-Dub: Dec 28, 2004; The Gonz

Cancer Wad posted:

Still working on this pricing table (original summary of project) and learning a ton about Excel and enjoying wading through this stuff. Couple of (hopefully) straightforward questions:

I've opted to attempt doing this via defined names. There's a separate worksheet with a pricing table, and since Excel 2010 seems to automatically define names for the ranges in that table, I guess I'd like to just reference those defined names to obtain pricing.

My form takes user selections on bandwidth and term and populates the proposal worksheet thus:

Is it possible for the pricing to be done by code that references cell contents for a defined name lookup? For example, for E13 take whatever bandwidth has been populated from C13 and the term from E11 and just leverage the named ranges that Excel already has in place?

I know there are plenty of ways to pull that pricing and have tried Application.Vlookup, but these named ranges seem awfully convenient; this works as a formula (= _10M _24Months), but can VBA reference whatever those cells' values are in the current (active) row? If this is a really dumb way to approach, tell me. God, I hope this question makes sense; I'm even shittier at talking about this stuff than I am at doing it. For the record, they didn't hire me to do this or anything, I'm just working on it as a personal project to make my job easier.

I'm not 100% on what you are getting at but yes, you can use named ranges in VBA. I think you probably have access to ThisWorkbook.Range("NamedRangeName") and there is also the .Names collection where you might be able to use .Names("NamedRangeName") however that may be more about definition of named ranges than using the values. I am currently installing Excel so I can't say for sure.

# ¿ Feb 18, 2012 10:57

G-Dub: Dec 28, 2004; The Gonz

I have noticed this but it has worn me down enough to always Ctrl-S, close, 'No'. It would be interesting to find out why. I might investigate tomorrow.

# ¿ Mar 14, 2012 22:57

G-Dub: Dec 28, 2004; The Gonz

Tortilla Maker posted:

Words about contracts

The problem here isn't with Excel but with the naming convention (or lack thereof) within your contract files. I would personally be renaming the files to contract number only.pdf. Excel can be used to do this. If you don't want to rename the files then there is still a solution using the same principal.

You want to look in to a function called Dir(). If you do something like the below you will get the full name of the file - it's up to you if you then just want to launch it or you want to run a one-time script to rename them all.

This is done direct in the reply so excuse any syntax errors.

code:

Dim strFilename as string
    
    strFilename = Dir("C:\Path\to\contract\files\*" & ActiveCell.Value & "*")

It should then be easy to either launch the file or even better have your script rename the file then launch the renamed version (or even better just loop this script and rename them all the first time around).

# ¿ Mar 18, 2012 14:46

G-Dub: Dec 28, 2004; The Gonz

GregNorc posted:

So I'm trying to count all the instances in a range that do not contain one of two values.

What version of Excel are you using? 2007 onwards has a COUNTIFS and SUMIFS function where multiple criteria can be specified, which might help you out.

Regarding your syntax error, in your first statement are you not missing a comma? I am sure the syntax for COUNTIF is =COUNTIF(range,criteria). You seem to have it all rolled in to one.

I also don't think you can just fire things together like "coding_final!K2:K4(NOT" together as it will think the "(NOT" is part of the range. It should be something like

=IF(NOT(OR(coding_final!K2:K4=categories!A10,coding_final!K2:K4=categories!A11)),1,0)

and even then I am not sure off the top of my head how Excel will evaluate the range=value bit. You might need to do K2, K3 and K4 separately.

EDIT: Beaten

# ¿ Apr 9, 2012 17:53

G-Dub: Dec 28, 2004; The Gonz

This sounds like a job for a database. Is Access an option?

# ¿ May 16, 2012 20:54

G-Dub: Dec 28, 2004; The Gonz

Swink posted:

I've got a dumb request from a client that I cant solve.

He wants to track a loan repayment, where the repayment amount can vary, as well as the date he pays. Each time a payment is made, he wants to add on the interest for the days since the previous payment.

He wants to know exactly how much is left on the loan at any given time. He intends to pay semi-regularly with some large lump payments but also some periods where he pays nothing.

I cant figure out how to keep a running total of the balance outstanding. I need to add x days of interest to the balance, and subtract the principal paid each time a payment is made. He intends to update the sheet himself with the date paid and the amount.

I've not worked with finance stuff in Excel, and this request sounds pretty stupid, but if someone could take a quick look and where I'm at so far I'd appreciate it.

http://dl.dropbox.com/u/58703/runninginterest.xlsx

At a glance, in F6 you want "=F5 - J6" and then fill it down that column. I think you have all the component parts correct.

Do you want the top-left to display the balance as of the current date?

# ¿ May 22, 2012 19:31

G-Dub: Dec 28, 2004; The Gonz

Are you using 2007? Countifs is only available in 2007 onwards

# ¿ May 24, 2012 07:16

G-Dub: Dec 28, 2004; The Gonz

You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls.

You have two methods of doing this - one is having your script mimic user inputs in to form fields and the other is doing direct POST calls to the relevant pages. For online banks I imagine their site security will mean option 1 is the only feasible one. I can try and help you out but there is quite a bit involved.

# ¿ Jul 14, 2012 19:04

G-Dub: Dec 28, 2004; The Gonz

I haven't seen any tutorials. I learned how to use the IE object by instancing one and loving around.

Start off with the following (not written in the editor so sorry if it doesn't compile) and have a play around with the different IE methods. Remember to reference Microsoft Internet Controls

code:

Public Sub Test()

Dim objIE as New InternetExplorer

objIE.Visible = True ' Normally you would have this as false but this lets you watch it work

objIE.Navigate "http://forums.somethingawful.com"

End Sub

This seems to cover some form stuff, which you will need for logins

# ¿ Jul 16, 2012 20:14

G-Dub: Dec 28, 2004; The Gonz

A pivot table will do exactly this.

# ¿ Jul 24, 2012 22:18

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 06:50

G-Dub: Dec 28, 2004; The Gonz

DukAmok posted:

Microsoft needs to sell a version of Excel named PivotTables Express or something and have it just be exactly the same program, just to get some more visibility on those awesome things in the general business world. I've seen so much time and effort wasted when people just simply weren't aware of the option.

I am rewriting a bunch of code right now where I have seen so many Excel functions replicated in the most inefficient fashion - the biggest culprits being SUMIF and COUNTIF. It gets even easier now with SUMIFS and COUNTIFS in 2k7 onwards. So many macros have been completely removed to leave basic workbooks, which takes them out of being classed as apps, and this makes me happy.

# ¿ Jul 25, 2012 07:22

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