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 »

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Alastor_the_Stylish posted:

At work I'm sort of the fixer of everything, Point of Sale, hardware, moving boxes, inventory management, and this time it's working with mountains of excel workbooks to pull out data.

I'm trying to create an Excel macro which would open a file browser and enable selection of multiple workbooks titled "Daily Report for (The Date)".

Then it will look for a sheet in each of the books called "Deposit Sheet" and take the numerical value in cells A1, B2, and C3 in each sheet across the workbooks and create a sheet which returns the sum of the A1 cells across multiple workbooks, the sum of the B2 cells, and the sum of the C3 cells as well.

I've seen a similar program on an excel help forum, but it seemed to only work for the original author and everyone else had troubles implementing it.

I'd appreciate any help, if one person knows how or a few people contribute to something I can copy, paste, and run I'd be happy to donate a few bucks to a charity or two in someone's name.

This kind of thing is always going to be monstrously unwieldy. Consultants earn huge salaries dealing with this kind of crap because it is like hiring a plumber to deal with data that has been stored like this. Excel is NOT a database, it is NOT a data storage tool, and it is NOT designed to make it easy to recover data. A database is a data storage tool. If you don't want to pay more than beer money to do this, then afaik you have a few options:

1) Kang together a script that converts all the spreadsheets to csv and does some kind of ridiculous hacky math on the plaintext values or something

2) Convert all the spreadsheets to csv and import them to a real database solution like mariadb, postgres, oracle, SQL Server, etc, and learn to use that because your life will be a lot easier in the long run that way.

3) Get ready for a lifetime of assache maintaining your script once you figure out how to do it, because nobody who knows enough VBA to handle this is going to store data like that in the first place and so nobody but consultants will have written an application to deal with it in any reliable way.

# ? Jun 7, 2013 03:39

Adbot: ADBOT LOVES YOU

# ? May 26, 2024 05:10

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

melon cat posted:

I want to create a Yes/No checkbox for a specific question (ie. D(o you live in Canada?"), whereas the text colour for that text turns red if 'No' is selected. How do I go about doing this?

Create a combobox with the available values, bind the combobox options to set the value of a cell underneath it to TRUE or FALSE, and set conditional formatting on the cell with the question to turn red when that cell is TRUE (or whatever).

melon cat posted:

Also- let's say I have two worksheets in the same workbook (let's call them Sheet 1 + Sheet 2). I want whatever information that's typed up in Sheet 1 Cell A1 to automatically appear in Cell B1 in Sheet 2. How do I get this to happen?

Sorry for this onslught of questions... still trying to getting better at Excel.

Just link the cells, eg set the formula of Sheet2!B1 to "=Sheet1!A1"

# ? Jun 7, 2013 03:42

Alastor_the_Stylish: Jul 25, 2006; WILL AMOUNT TO NOTHING IN LIFE.

Thanks for keeping me from making an rear end out of myself.

What we do at the end of every day is go through the deposits into each account that are recorded by hand as we receive them, and enter them into a sheet in a workbook which covers that day. After we close the 20 year old point of sale software for the day, that exact same data all goes into the POS system database.

Previously I hacked together a query to pull up details of transactions based on the date it happened or the item number purchased, so now I'll probably try hacking together something similar that adds everything together for each account for one month.

More time spent sitting in a chair at work

# ? Jun 7, 2013 04:20

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

It sounds doable with a couple of set controls. Is there any reason the workbooks have to be selected, and not just something that you run daily?

# ? Jun 7, 2013 17:08

Alastor_the_Stylish: Jul 25, 2006; WILL AMOUNT TO NOTHING IN LIFE.

We have a folder for each month, and in that folder are some excel workbooks not titled "Daily Report for (date)" so I'd like to leave selecting the books to use in the hands of the people who know which book has the daily reports and which are extraneous. Every month we look back at the month's folder of workbooks for the information, for hours. Since the workbooks are made based on reprinted paper receipts and should exactly match the database, I would try either something in vba based on the month's folder of workbooks or writing a query to create a database report containing that month's deposits and the summation of what was deposited into each account.

# ? Jun 7, 2013 22:03

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Obviously, if this data exists in a database, you should query it from there.

That being said, it is really not difficult to do like you want. You just can't expect it to be very robust.

Here's something I spent 10 minutes on that lets a user choose multiple files using the usual file open dialog. The reason why I show it multiple times is in case you want to multi-select files that are in different folders. Cancel once you've selected everything you need.

code:

Public Function getFileNames() As Variant
    'NOTE: REQUIRES REFERENCE TO MICROSOFT SCRIPTING RUNTIME

    Dim file_idx As Long
    Dim done_adding As Boolean
    
    Dim fileList As New Scripting.Dictionary
    Dim newFiles As Variant
    
    While Not done_adding
        newFiles = Application.GetOpenFilename(, , "Choose some files to open", , True)
        
        If Not VarType(newFiles) = vbBoolean Then
            For file_idx = 1 To UBound(newFiles)
                If Not fileList.Exists(newFiles(file_idx)) Then
                    fileList.Add newFiles(file_idx), ""
                End If
            Next file_idx
        Else
            done_adding = True
        End If
    Wend
    
    getFileNames = fileList.Keys
End Function

And here's a subroutine that does roughly what you want with those files:

code:

Sub openAndSum()
    Dim filesToOpen As Variant
    Dim fileIdx As Long
    Dim currWorkbook As Workbook
    Dim currSheet As Worksheet
    Dim sums(1 To 3) As Double, sumIdx As Integer
    
    'Get a list of files from the user
    filesToOpen = getFileNames()
    
    'Loop through the files
    For fileIdx = LBound(filesToOpen) To UBound(filesToOpen)
        Set currWorkbook = Workbooks.Open(filesToOpen(fileIdx))
        
        'Sum up A1's, B2's and C3's across books
        With currWorkbook.Sheets("Deposit Sheet")
            For sumIdx = 1 To 3
                sums(sumIdx) = sums(sumIdx) + .Cells(sumIdx, sumIdx).Value
            Next sumIdx
        End With
        
        currWorkbook.Close savechanges:=False
    Next fileIdx
    
    Set currWorkbook = Workbooks.Add
    
    With currWorkbook.Sheets(1)
        'Headers
        .Range("A1").Value = "Sum of A1"
        .Range("A2").Value = "Sum of B2"
        .Range("A3").Value = "Sum of C3"
        
        'Data
        For sumIdx = 1 To 3
            .Cells(sumIdx, 2).Value = sums(sumIdx)
        Next sumIdx
    End With
End Sub

Note that there is really no checking in there so obviously it's not very safe. If "Deposit Sheet" doesn't exist in a book, or if your data is misplaced in that sheet, or if you accidentally have string data where there should be numbers, it won't work (and you may not even notice).

If you know the field structure of the database, post it. You can query it directly with VBA through ADODB. It will be both easier and much safer.

ShimaTetsuo fucked around with this message at 00:59 on Jun 8, 2013

# ? Jun 8, 2013 00:51

Alastor_the_Stylish: Jul 25, 2006; WILL AMOUNT TO NOTHING IN LIFE.

ShimaTetsuo posted:

code:

Public Function getFileNames() As Variant
    'NOTE: REQUIRES REFERENCE TO MICROSOFT SCRIPTING RUNTIME

    Dim file_idx As Long
    Dim done_adding As Boolean
    
    Dim fileList As New Scripting.Dictionary
    Dim newFiles As Variant
    
    While Not done_adding
        newFiles = Application.GetOpenFilename(, , "Choose some files to open", , True)
        
        If Not VarType(newFiles) = vbBoolean Then
            For file_idx = 1 To UBound(newFiles)
                If Not fileList.Exists(newFiles(file_idx)) Then
                    fileList.Add newFiles(file_idx), ""
                End If
            Next file_idx
        Else
            done_adding = True
        End If
    Wend
    
    getFileNames = fileList.Keys
End Function

And here's a subroutine that does roughly what you want with those files:

code:

Sub openAndSum()
    Dim filesToOpen As Variant
    Dim fileIdx As Long
    Dim currWorkbook As Workbook
    Dim currSheet As Worksheet
    Dim sums(1 To 3) As Double, sumIdx As Integer
    
    'Get a list of files from the user
    filesToOpen = getFileNames()
    
    'Loop through the files
    For fileIdx = LBound(filesToOpen) To UBound(filesToOpen)
        Set currWorkbook = Workbooks.Open(filesToOpen(fileIdx))
        
        'Sum up A1's, B2's and C3's across books
        With currWorkbook.Sheets("Deposit Sheet")
            For sumIdx = 1 To 3
                sums(sumIdx) = sums(sumIdx) + .Cells(sumIdx, sumIdx).Value
            Next sumIdx
        End With
        
        currWorkbook.Close savechanges:=False
    Next fileIdx
    
    Set currWorkbook = Workbooks.Add
    
    With currWorkbook.Sheets(1)
        'Headers
        .Range("A1").Value = "Sum of A1"
        .Range("A2").Value = "Sum of B2"
        .Range("A3").Value = "Sum of C3"
        
        'Data
        For sumIdx = 1 To 3
            .Cells(sumIdx, 2).Value = sums(sumIdx)
        Next sumIdx
    End With
End Sub

Thank you very much for putting this together.

ShimaTetsuo posted:

Note that there is really no checking in there so obviously it's not very safe. If "Deposit Sheet" doesn't exist in a book, or if your data is misplaced in that sheet, or if you accidentally have string data where there should be numbers, it won't work (and you may not even notice).

This worries me, because the beautiful and kind ladies I work with (of whom I have become the work-child as opposed to their normal home-children) have very likely entered some of the A1 cells in Currency format, sometime as characters with the dollar sign entered by hand, sometimes as numbers with a comma, etc. I assumed something as intuitive to myself and my coworkers as selecting the sheets of the month to sum together in the file browser would be the most user-friendly way to do it, but that might have to be worked around.

ShimaTetsuo posted:

If you know the field structure of the database, post it. You can query it directly with VBA through ADODB. It will be both easier and much safer.

Honestly this is a question where I don't know what I don't know. Last summer I wrote/smashed together a SQL query which pulls up transaction data from the POS system database with just some background in C++. I was able to pull out transaction information within a date range and with a certain item number. For the end of month functionality it's likely that I will need to add filters for the customer account and maybe there is a way to write something that will sum all the transactions for X item times Y quantity bought by Z customer within a month.

Monday I can post the queries that are already written and working, hopefully that will have a clue as to this "field structure" and ADODB and VBA letters you used?

# ? Jun 8, 2013 03:44

melon cat: Jan 21, 2010; Nap Ghost

celestial teapot posted:

Create a combobox with the available values, bind the combobox options to set the value of a cell underneath it to TRUE or FALSE, and set conditional formatting on the cell with the question to turn red when that cell is TRUE (or whatever).

Just link the cells, eg set the formula of Sheet2!B1 to "=Sheet1!A1"

Thanks so much for your help on this. You're a lifesaver.

I've hit another snag, though. I have a bunch of drop down lists I've created. How do I create a "reset" button on the spreadsheet which resets all of the drop down menu selections?

And I'm trying to pick up on concatenation. How to concatenate division? I'm trying to divide a number in cell E6 and E7 and concatenate it with, "You final percentage is __%!" but I keep getting an error.

melon cat fucked around with this message at 03:57 on Jun 10, 2013

# ? Jun 9, 2013 17:38

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

melon cat posted:

Thanks so much for your help on this. You're a lifesaver.

I've hit another snag, though. I have a bunch of drop down lists I've created. How do I create a "reset" button on the spreadsheet which resets all of the drop down menu selections?

Pretty sure you'd need VBA for this, to reset their positions. You will need to define a macro that runs on click. It will hide the controls and then show them again. When they are shown after being hidden, they should have default values.

melon cat posted:

And I'm trying to pick up on concatenation. How to concatenate division? I'm trying to divide a number in cell E6 and E7 and concatenate it with, "You final percentage is __%!" but I keep getting an error.

="Your final percentage is " & E6/E7 & "%!"

# ? Jun 10, 2013 07:12

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

celestial teapot posted:

Pretty sure you'd need VBA for this, to reset their positions. You will need to define a macro that runs on click. It will hide the controls and then show them again. When they are shown after being hidden, they should have default values.

="Your final percentage is " & E6/E7 & "%!"

Try ="Your final percentage is " & text(E6/E7,"0.0%")
Otherwise you could end up with a cell that reads "Year final percentage is 7534.6516165347816%" When you want it to say "75.3%".

# ? Jun 10, 2013 13:50

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Yeah that is a good suggestion. ROUND() might do it too, but TEXT() is foolproof since you are combining the result with text anyway.

# ? Jun 10, 2013 20:22

melon cat: Jan 21, 2010; Nap Ghost

celestial teapot posted:

="Your final percentage is " & E6/E7 & "%!"

Thanks. I managed to get it to working, but instead of saying "Your final percentage is 81%" it's saying, "Your final percentage is 0.8181818181818%". I tried formatting the cell to output a % with no decimal places, but it won't work.

How the heck do I properly format a concatenated cell?

\/ :hfive:

melon cat fucked around with this message at 18:24 on Jun 16, 2013

# ? Jun 12, 2013 14:46

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

melon cat posted:

celestial teapot posted:
="Your final percentage is " & E6/E7 & "%!"

Thanks. I managed to get it to working, but instead of saying "Your final percentage is 81%" it's saying, "Your final percentage is 0.8181818181818%". I tried formatting the cell to output a % with no decimal places, but it won't work.

How the heck do I properly format a concatenated cell?

Read the next post after that....

# ? Jun 12, 2013 21:41

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

Old James posted:

Read the next post after that....

You're like some kind of wizard that could see into the future.

# ? Jun 13, 2013 18:21

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

I've got an interview tomorrow that wants Advanced Excel skills, including "including use of nested formulas and data mining." I no problem using nested forumals, and I'm assuming data mining has to do with pulling from an sql database. I use vlookups all the time, and have a pretty good grip on pivot tables. Any other suggestions to brush up on? I don't think I should have a problem, but I use macros more often than regular functions would rather not get caught off guard by something that people would think I should know.

# ? Jun 17, 2013 00:34

melon cat: Jan 21, 2010; Nap Ghost

celestial teapot posted:

Just link the cells, eg set the formula of Sheet2!B1 to "=Sheet1!A1"

I actually have another question about this. How do you reference another Worksheet whenthe name of said worksheet has a bracket? ie. (Q1) Performance. Excel seems to think that the brackets are a function, and keeps giving me an error message when I tried to display the value in cell A1 into a different Worksheet!

And before you recommend changing the worksheet name to something that doesn't have brackets- it's not an option at this point. I have severalhyperlinks that link to different parts of the Workbook, and changing any of the sheets breaks those links completely (unless there's a way of preventing that, then I'm all ears!).

Old James posted:

Try ="Your final percentage is " & text(E6/E7,"0.0%")
Otherwise you could end up with a cell that reads "Year final percentage is 7534.6516165347816%" When you want it to say "75.3%".

I have a follow-up question about this bit. I got it working thanks to you and celestial teapot's help. But I want to take things a step further- Sheet1 says, "Your percentage is 40%!" I want that 40% to show up in a different cell in a different worksheet. How do you reference ONLY the percentage part of the concatenated function?

melon cat fucked around with this message at 05:55 on Jun 17, 2013

# ? Jun 17, 2013 05:38

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

1. Use '. So it should be '(Q1) Performance'!A1.
2. Just use text(E6/E7,"0.0%")

# ? Jun 17, 2013 13:00

i like Ham: Dec 25, 2006; I'm a big fancy business man. Mind if I check you'r prostate?

Probably a stupid question, but I after numerous google searches I can't seem to find what I'm looking for. I need to use a listbox to select a named range for my macro to run in. So basically I'm looking for a way to have a listbox list some of the named ranges in my workbook, although preferably not all. There would always be 14 named ranges to select from, and there is no need for them to change.

Every google search I try always directs me to people looking to populate a listbox or combobox from a named range.

# ? Jun 17, 2013 17:47

Total Meatlove: Jan 28, 2007; Rangers died, shoujo Hitler cried ;_;

I'm getting to grips with Excel a bit but it's still killing me to nest stuff properly.

In my earlier problem in the thread (and thanks to everyone who helped) I got the two formulas I needed to set up a table that works 95% of the time, but I need to tweak it a tiny bit.

Essentially,

code:

SCAN INFO      NUMBER                   NAME
30404          =IF(ISNUMBER(A2),A2,"")     =IF(ISTEXT(A3),A3,"")
BOB            =IF(ISNUMBER(A3),A3,"")     =IF(ISTEXT(A4),A4,"")
UK040505       =IF(ISNUMBER(A4),A4,"")     =IF(ISTEXT(A5),A5,"")
TOM            =IF(ISNUMBER(A5),A5,"")     =IF(ISTEXT(A6),A6,"")
30506          =IF(ISNUMBER(A6),A6,"")     =IF(ISTEXT(A7),A7,"")
FRED           =IF(ISNUMBER(A7),A7,"")     =IF(ISTEXT(A8),A8,"")

returns

code:

SCAN INFO     NUMBER                NAME
30404         30404                 BOB
BOB
UK040505                            TOM   
TOM
30506         30506                 FRED
FRED

I need some way of having a string starting with UK treated the same way as a number and copied to column B, and also ignored by column C. What I've got is nesting two IF statements;

=IF(ISNUMBER(A2),A2, IF(FIND("UK",A2),A2,""))

e: I fixed it (I think) too

=IFERROR(IF(ISNUMBER(A6),A6, IF(FIND("UK",A6),A6,"")),"")

but now I've broken Column C so it won't ignore numbers starting UK.

Total Meatlove fucked around with this message at 20:10 on Jun 17, 2013

# ? Jun 17, 2013 19:51

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

i like Ham posted:

Probably a stupid question, but I after numerous google searches I can't seem to find what I'm looking for. I need to use a listbox to select a named range for my macro to run in. So basically I'm looking for a way to have a listbox list some of the named ranges in my workbook, although preferably not all. There would always be 14 named ranges to select from, and there is no need for them to change.

Every google search I try always directs me to people looking to populate a listbox or combobox from a named range.

Since it is just 14 items and they never change, why not just make a drop down box in a cell using data validation. No macro or coding needed.

http://www.online-tech-tips.com/ms-office-tips/how-to-use-data-validation-in-microsoft-excel-to-create-dropdown-lists/

# ? Jun 17, 2013 20:05

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Total Meatlove posted:

I need some way of having a string starting with UK treated the same way as a number and copied to column B, and also ignored by column C. What I've got is nesting two IF statements;

=IF(ISNUMBER(A2),A2, IF(FIND("UK",A2),A2,""))

But that's not working, where am I going wrong?

Try

=IF(ISNUMBER(A2),A2,IF(ISERROR(SEARCH("UK",A2)),"",A2))

I went to search since FIND() is case sensitive.

# ? Jun 17, 2013 20:14

Total Meatlove: Jan 28, 2007; Rangers died, shoujo Hitler cried ;_;

Old James posted:

Try

=IF(ISNUMBER(A2),A2,IF(ISERROR(SEARCH("UK",A2)),"",A2))

I went to search since FIND() is case sensitive.

Thanks for this, do you have anything on setting column C to accept text but ignore UK numbers?

I've got;

=IF(SEARCH("UK",A6),"",IF(ISTEXT(A6),A6,""))

which does well at ignoring UK numbers, but doesn't copy any other text.

# ? Jun 17, 2013 20:52

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Total Meatlove posted:

Thanks for this, do you have anything on setting column C to accept text but ignore UK numbers?

I've got;

=IF(SEARCH("UK",A6),"",IF(ISTEXT(A6),A6,""))

which does well at ignoring UK numbers, but doesn't copy any other text.

=IF(AND(ISTEXT(A6),ISERROR(SEARCH("UK",A6))),A6,"")

# ? Jun 17, 2013 20:56

Cockblocktopus: Apr 18, 2009; Since the beginning of time, man has yearned to destroy the sun.

I feel really, really dumb asking this because I think I've written a VBA script to do the same basic process on my work computer and this is totally the sort of thing that Access is used for, but I'm trying to remember how to do this on a machine that doesn't have Office installed for my girlfriend, who doesn't have Access (but has Excel):

Sheet1 has 200 values (Sheet1!A1:A200) that I need to use to filter 5000+ values on Sheet2 (Sheet2!N2:N5314 in this particular case). I figure writing a macro would be best since she'll have to repeat this for about 50 sets of data, but I'm completely unable to figure out how to filter Sheet2 by matching values in column N to column A of Sheet1. The catch is that I want the entire row in Sheet2 if the value in column N matches a value in the list in Sheet1.

I'm basically trying to do an inner join, but I've never programmed SQL in Excel and I'm not confident enough in my coding to do this without being able to take baby steps through it. Everything I've found in Google is basically "have you tried VLOOKUP?" but that doesn't do quite what I want it to do.

If Text Filters would just let me select Sheet1!A1:A200 (instead of apparently only letting me use two manually entered values at a time) then this wouldn't be a problem. :argh:

Thanks in advance! (I never thought I'd pine for Access but this is just ridiculous.)

# ? Jun 18, 2013 00:07

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

FadingChord posted:

I feel really, really dumb asking this because I think I've written a VBA script to do the same basic process on my work computer and this is totally the sort of thing that Access is used for, but I'm trying to remember how to do this on a machine that doesn't have Office installed for my girlfriend, who doesn't have Access (but has Excel):

Sheet1 has 200 values (Sheet1!A1:A200) that I need to use to filter 5000+ values on Sheet2 (Sheet2!N2:N5314 in this particular case). I figure writing a macro would be best since she'll have to repeat this for about 50 sets of data, but I'm completely unable to figure out how to filter Sheet2 by matching values in column N to column A of Sheet1. The catch is that I want the entire row in Sheet2 if the value in column N matches a value in the list in Sheet1.

I'm basically trying to do an inner join, but I've never programmed SQL in Excel and I'm not confident enough in my coding to do this without being able to take baby steps through it. Everything I've found in Google is basically "have you tried VLOOKUP?" but that doesn't do quite what I want it to do.

If Text Filters would just let me select Sheet1!A1:A200 (instead of apparently only letting me use two manually entered values at a time) then this wouldn't be a problem.

Thanks in advance! (I never thought I'd pine for Access but this is just ridiculous.)

Add a column O to sheet 2 with the following formula
=if(iserror(match($N2,Sheet1!$A:$A,0)),"N","Y")
And then filter the data to where column O has a "Y" in it.

# ? Jun 18, 2013 00:21

Cockblocktopus: Apr 18, 2009; Since the beginning of time, man has yearned to destroy the sun.

Old James posted:

Add a column O to sheet 2 with the following formula
=if(iserror(match($N2,Sheet1!$A:$A,0)),"N","Y")
And then filter the data to where column O has a "Y" in it.

This worked perfectly; thanks so much!

# ? Jun 18, 2013 01:23

Requiem: Jan 29, 2003; You can't bring that weak-ass shit up in this humpity-bumpity! This ain't Club Med, baby!

Hi all,

I'm an Excel beginner, knowing enough to use CONCATENATE but not enough to troubleshoot anything beyond that.

I'm trying to use CONCATENATE to develop an HTML field that forms a complete address block, eg:

quote:

John Smith
XYZ Fabrication
123 Main St
Toronto ON M2M 1A1

But, what I'm finding is that combining all cells with in between results in lots of empty lines where fields have no data, eg:

quote:

Jerry Johnson

456 Main St

Toronto ON M2M 1A1

Can anyone help me build IF statements so empty cells are ignored?
My formula thus far is:

quote:

=CONCATENATE(D169," ",E169," ",H169," ",I169," ",J169," ",K169," ",L169," ",M169)

My data looks something like this:

pre:

John	Smith		XYZ Fabrication	123 Main St			Toronto	ON	M2M1A1
Jerry	Johnson				456 Main St	Suite 400	Toronto	ON	M2M1A1
Mark	Anderson	ABC Innovation	789 Main St			Toronto	ON	M2M1A1
Andrew	Blacksmith			123 John St	Suite 900	Toronto	ON	M2M1A1
Peter	Parrot				4567 John St			Toronto	ON	M2M1A1

Thanks in advance.

# ? Jun 18, 2013 01:38

i like Ham: Dec 25, 2006; I'm a big fancy business man. Mind if I check you'r prostate?

Old James posted:

Since it is just 14 items and they never change, why not just make a drop down box in a cell using data validation. No macro or coding needed.

http://www.online-tech-tips.com/ms-office-tips/how-to-use-data-validation-in-microsoft-excel-to-create-dropdown-lists/

Actually the first thing I tried. Unfortunately they span multiple rows and data validation does not like that. The spreadsheet is definitely set up in a far from ideal way which has led to a lot of frustrating issues, unfortunately I'vr got zero say in a more appropriate way to set it up.

# ? Jun 18, 2013 04:29

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

i like Ham posted:

Actually the first thing I tried. Unfortunately they span multiple rows and data validation does not like that. The spreadsheet is definitely set up in a far from ideal way which has led to a lot of frustrating issues, unfortunately I'vr got zero say in a more appropriate way to set it up.

Just create the list of 14 items on a hidden tab.

# ? Jun 18, 2013 05:13

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Requiem posted:

Concatenate stuff

There are two ways to concatenate strings, using the function as in your example or using an ampersand between strings. I prefer the ampersand.

=if(isnull(D169),"",D169&" ")&if(isnull(E169),"",E169&" ")&if(isnull(H169),"",H169&" ")&if(isnull(I169),"",I169&" ")&if(isnull(J169),"",J169&" ")&if(isnull(K169),"",K169&" ")&if(isnull(L169),"",L169&" ")&M169

# ? Jun 18, 2013 05:17

Requiem: Jan 29, 2003; You can't bring that weak-ass shit up in this humpity-bumpity! This ain't Club Med, baby!

Old James posted:

There are two ways to concatenate strings, using the function as in your example or using an ampersand between strings. I prefer the ampersand.

=if(isnull(D169),"",D169&" ")&if(isnull(E169),"",E169&" ")&if(isnull(H169),"",H169&" ")&if(isnull(I169),"",I169&" ")&if(isnull(J169),"",J169&" ")&if(isnull(K169),"",K169&" ")&if(isnull(L169),"",L169&" ")&M169

Old James, thanks. I'm getting #NAME? as my result on this. Excel help, of course, unhelpfully lists all kinds of possible causes:
-The EUROCONVERT function is used in a formula, but the Euro Currency Tools add-in is not loaded.
-A formula refers to a name that does not exist.
-A formula refers to a name that is not spelled correctly.
-The name of a function that is used in a formula is not spelled correctly.
-Text may have been entered in a formula without enclosing it in double quotation marks.
-A colon (

was omitted in a range reference.
-A reference to another sheet is not enclosed in single quotation marks (').
-A workbook calls a user-defined function (UDF) that is not available on your computer.

If you can get me to a complete and functioning formula, I'm happy (unless this is only kosher in SA Mart?) to send you a $15 iTunes or Amazon card or just PayPal...

Thanks

# ? Jun 18, 2013 06:11

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

#NAME? means that you have the wrong name for something (like a function).

It should be ISBLANK not ISNULL (IsNull is a VBA function that does something slightly different and is not available in the spreadsheet).

# ? Jun 18, 2013 11:25

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

ShimaTetsuo posted:

#NAME? means that you have the wrong name for something (like a function).

It should be ISBLANK not ISNULL (IsNull is a VBA function that does something slightly different and is not available in the spreadsheet).

Oops, I work primarily in Qlikview these days where my example would work.

# ? Jun 18, 2013 12:31

Requiem: Jan 29, 2003; You can't bring that weak-ass shit up in this humpity-bumpity! This ain't Club Med, baby!

Old James posted:

Oops, I work primarily in Qlikview these days where my example would work.

Perfect, friends--it works.

Old James, please PM me contact info for PayPal?

# ? Jun 18, 2013 16:07

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Requiem posted:

Perfect, friends--it works.

Old James, please PM me contact info for PayPal?

No need.

But if you feel compelled to spend some money why don't you buy some strangers custom titles and PM me the names so I can laugh at them when I see them in a thread.

# ? Jun 18, 2013 18:11

Target Practice: Aug 20, 2004; Shit.

Okay I swear this isn't a "do my homework" post; I think I just need to be pointed in the right direction.

I have never coded before, but I'm in a summer class that I need for my degree that uses VBA for Excel. I probably shouldn't have taken this class in a six-week summer course but there you go. I've been getting it no problem and have been doing well but my current project has got me hung up.

The problem is pretty straightforward: Locate the optimum location for a distribution center that services 6 customers in a 30x30 mile area, with 0.5 mile precision, minimizing cost. The locations of the customers are given in in terms of miles North and miles East of an origin (basically a Cartesian plane), and each customer gets a different "volume" which goes into determining the price. There are a shitload of other parts to this but this is the only thing really giving me trouble.

My instructor recommends a "brute force" technique, which I take to mean testing every element in a (30 / 0.5) + 1 = 61x61 array. The problem is he spent about 5 minutes on 2d arrays, he gave little instruction on how to populate them, and zero instruction on how to iterate though them.

So basically what I want to do is to create a 61x61 array (I can do this) whose elements are 2 numbers, and X and a Y, that I can put into the the forumula:

code:

d_i=abs(X-xi)+(Y-yi)

Where (xi,yi) are the locations of the i'th customer. (I cannot do this). From there, I have a pretty good plan for the rest of it, I think: Finish the other calculations to find the total cost for all 6 at each location, dump those costs into an 1d array with 61 x 61 = 3721 members and Worksheetfunction.min() the gently caress out of that bastard.

Any help is appreciated, and I read the Rules and I don't think I broke any :ohdear:

.

Edit: son of a bitch gonna be on the bottom of this here page

Target Practice fucked around with this message at 17:56 on Jun 23, 2013

# ? Jun 23, 2013 17:39

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Target Practice posted:

geometric mean

Here's some info on how to create arrays. http://msdn.microsoft.com/en-us/library/vstudio/wak0wfyt.aspx

To cycle through elements of an array you can use code like the following

code:

Dim arrLocation(1 to 61, 1 to 61) as integer
Dim x as integer
Dim y as integer

For x=lbound(arrLocation,1) to ubound(arrLocation,1)
     For y=lbound(arrLocation,2) to ubound(arrLocation,2)
          'Code goes here
     Next y
Next x

Old James fucked around with this message at 19:55 on Jun 23, 2013

# ? Jun 23, 2013 19:31

Target Practice: Aug 20, 2004; Shit.

Geographic mean, poo poo I knew that was called something. Thanks so much, gonna give this a shot.

Edit: Welp I'm dumb VVVVV

Target Practice fucked around with this message at 20:25 on Jun 23, 2013

# ? Jun 23, 2013 19:35

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Target Practice posted:

Geographic mean, poo poo I knew that was called something. Thanks so much, gonna give this a shot.

Sorry typo, geometric mean.

# ? Jun 23, 2013 19:56

Adbot: ADBOT LOVES YOU

# ? May 26, 2024 05:10

melon cat: Jan 21, 2010; Nap Ghost

Alright. New project, new challenge.

I have a spreadsheet that tracks our Quarterly profits.

E8 has $20,000
F8 has $30,000

Right beneath, I have a cell (F9) that indicates the % increase, or decrease in profits. I've figured out how to get that number (=(F8-E8)/E8). But, how do I set it up so if there's a decrease it inserts a "-" symbol, and if there's an increase, it inserts a "+" symbol next to the result?

I'm guessing that I need to use an 'IF' and 'OR' statement... but I'm really inexperienced with at those. :ohdear:

melon cat fucked around with this message at 03:08 on Jun 24, 2013

# ? Jun 24, 2013 00:25

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

«‹›82 »