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 »

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

diremonk posted:

Thanks this works great, although I haven't tested it yet on the network share portion. Sorry if took a couple days to get back to you about it, had to take a couple days off.

If I could ask a bit more though, is is possible to have the individual sheets save to different folders? Or maybe point me in the direction on how to do it myself? It's kind of fun to get a nice macro or worksheet in Excel and look like a smart person to the boss.

Again, thank you for working on this.

Just change the following line in ScarletBrother's code from this

code:

wbDest.SaveAs strSavePath & sht.Name & Format(Date, "MMDDYY") & ".xls"

to this

code:

wbDest.SaveAs strSavePath & sht.name & "\" & sht.Name & Format(Date, "MMDDYY") & ".xls"

This will save the CBS sheet to a folder labelled CBS and the FOX sheet to the FOX folder.

# ? Jul 24, 2012 15:55

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 02:46

miserable lil onion: Oct 15, 2008

Never mind, figured it out!

miserable lil onion fucked around with this message at 17:35 on Jul 24, 2012

# ? Jul 24, 2012 16:46

Lawnie: Sep 6, 2006; That is my helmet
Give it back
you are a lion
^{It doesn't even fit}; Grimey Drawer

I'm trying to set up a sheet in a workbook that will take a daily average of values in a table. So say the columns in the table look something like this:

code:

Id #      date      measurement 1      measurement 2

With multiple values for both measurement 1 and 2 for each date in a month. However, the number of measurements taken per day varies. What I want to do is plug in a table that looks something like the above populated with data from an entire month, and then another sheet will return the average of each measurement on the first of the month, second, third, and so on until the end of the month. Is there any way to do this automatically or will I have to manually separate out the dates?

I'm posting from my phone at work so sorry if it's unclear. Hopefully you can see what I'm looking for; if not I can rephrase the question once I'm back at my PC at home.

EDIT: I'm trying to use the averageif function with the range being the date column and the criteria as "*/11/*" and the average range being the column measurement I want to average, but I'm getting a #div/0 error, and I can't figure out why. I suspect it has to do with how I'm using wild cards but honestly I'm a pretty poor programmer, so I don't know what exactly I'm doing wrong.

Lawnie fucked around with this message at 21:19 on Jul 24, 2012

# ? Jul 24, 2012 21:02

G-Dub: Dec 28, 2004; The Gonz

A pivot table will do exactly this.

# ? Jul 24, 2012 22:18

Lawnie: Sep 6, 2006; That is my helmet
Give it back
you are a lion
^{It doesn't even fit}; Grimey Drawer

G-Dub posted:

A pivot table will do exactly this.

That looks like it will work perfectly, and I might even be able to apply it to a couple other things at work. Thanks a bunch!

# ? Jul 24, 2012 23:06

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

G-Dub posted:

A pivot table will do exactly this.

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.

# ? Jul 25, 2012 00:00

diremonk: Jun 17, 2008

Old James posted:

Just change the following line in ScarletBrother's code from this
code:
wbDest.SaveAs strSavePath & sht.Name & Format(Date, "MMDDYY") & ".xls"
to this
code:
wbDest.SaveAs strSavePath & sht.name & "\" & sht.Name & Format(Date, "MMDDYY") & ".xls"
This will save the CBS sheet to a folder labelled CBS and the FOX sheet to the FOX folder.

That did it, thank you both so much for the help. You'll have made the day of my entire department once I let everyone know.

# ? Jul 25, 2012 04:36

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

DoleMIGHTY: Oct 23, 2007; uncle bens low fat puaddings

I'm trying to compare attributes of parts that are used across a maximum of 4 projects. Is there a way to compare text strings across multiple non-consecutive cells, and only count the non-blank cells? Something like

code:

Desc.  Rev   Desc.  Rev    Desc.  Rev    Desc.   Rev  Desc. Match?  Rev Match?
Apple | A  |       |    | Orange | A  | Banana | A  |    NO        |   YES
      |    | Apple |  B | Apple  | C  |        |    |    YES       |   NO
Orange| B  |       |    |        |    | Orange | B  |    YES       |   YES

is what I'm trying to do.

# ? Jul 30, 2012 13:14

Aredna: Mar 17, 2007; Nap Ghost

Since it's limited to 4 sets of data I just do it the brute force way and do all of the comparisons, defaulting to TRUE if either cell in a specific comparison is blank.

Assuming your table below is from Cells A1:J4, then paste this into I2 (Desc Match for the first row), but on one line instead of 3 as below that I had to do to prevent table breaks

code:

=IF(AND(OR(A2=C2,A2="",C2="",ISBLANK(A2),ISBLANK(C2)),OR(C2=E2,C2="",E2="",ISBLANK(C2),ISBLANK(E2)),
OR(E2=G2,E2="",G2="",ISBLANK(E2),ISBLANK(G2)),OR(A2=E2,A2="",E2="",ISBLANK(A2),ISBLANK(E2)),
OR(C2=G2,C2="",G2="",ISBLANK(C2),ISBLANK(G2)),OR(A2=G2,A2="",G2="",ISBLANK(A2),ISBLANK(G2))),"Yes","No")

Then you can copy and paste it in Excel to the right and down for the formula to adjust as necessary for all other comparisons.

# ? Jul 30, 2012 15:50

DoleMIGHTY: Oct 23, 2007; uncle bens low fat puaddings

Aredna posted:

Since it's limited to 4 sets of data I just do it the brute force way and do all of the comparisons, defaulting to TRUE if either cell in a specific comparison is blank.

Assuming your table below is from Cells A1:J4, then paste this into I2 (Desc Match for the first row), but on one line instead of 3 as below that I had to do to prevent table breaks
code:
=IF(AND(OR(A2=C2,A2="",C2="",ISBLANK(A2),ISBLANK(C2)),OR(C2=E2,C2="",E2="",ISBLANK(C2),ISBLANK(E2)),
OR(E2=G2,E2="",G2="",ISBLANK(E2),ISBLANK(G2)),OR(A2=E2,A2="",E2="",ISBLANK(A2),ISBLANK(E2)),
OR(C2=G2,C2="",G2="",ISBLANK(C2),ISBLANK(G2)),OR(A2=G2,A2="",G2="",ISBLANK(A2),ISBLANK(G2))),"Yes","No")
Then you can copy and paste it in Excel to the right and down for the formula to adjust as necessary for all other comparisons.

I had to use "==" because spaces count as unique characters in my descriptions, but this works perfectly. Thanks!

# ? Jul 30, 2012 18:02

Lawnie: Sep 6, 2006; That is my helmet
Give it back
you are a lion
^{It doesn't even fit}; Grimey Drawer

I have a calculator set up that takes 3 inputs and outputs a number in another cell. Is there any way to create a button or a macro that the user could press that would automatically export the output to another sheet, along with the date and time it is exported? I'm trying to track some data for a project and I need to make it as quick and easy as possible to do.

# ? Jul 31, 2012 16:45

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

Lawnie posted:

I have a calculator set up that takes 3 inputs and outputs a number in another cell. Is there any way to create a button or a macro that the user could press that would automatically export the output to another sheet, along with the date and time it is exported? I'm trying to track some data for a project and I need to make it as quick and easy as possible to do.

Yes, and there are examples of similar scenarios on the last page to get you started.

# ? Jul 31, 2012 18:52

DoleMIGHTY: Oct 23, 2007; uncle bens low fat puaddings

e: fixed

DoleMIGHTY fucked around with this message at 17:56 on Aug 3, 2012

# ? Aug 3, 2012 16:19

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

DoleMIGHTY posted:

*sigh*

I'm trying to expand on my little project and I'm needing to use VBA. I'm flying by the seat of my pants and learning VBA on the fly. There's this little block that generates a "Subscript out of range" error after stepping into "MyRange="
code:
VBA
No idea what could be wrong here, any help?

This is actually a quick fix. When setting the value of a variable to an object (such as a range or worksheet) you need to use the term "Set " before the variable name.

So change

code:

MyRange = Workbooks(MasterList).Sheets("Sheet1").Range("$A3:$E")

code:

Set MyRange = Workbooks(MasterList).Sheets("Sheet1").Range("$A3:$E")

However, it looks like there are problems with the rest of the code. I'll take a look at that and get back to you.

# ? Aug 3, 2012 17:52

puchu: Sep 20, 2004; hiya~

Hello thread. I have a little question that I hope someone with greater Excel nous than me can solve.

I've got two excel workbooks, one called datasource.xlsx and the other called datapresentation.xlsx. In datasource.xlsx I have a range of cells (B11:B42) which contain data. I want to be able to link these two books so that the data from datasource.xlsx will also appear in datapresentation.xlsx. This sounds simple; something like =[datasource.xlsx]sheet1!whateverthecellrefis would do.

Unfortunately, there are a couple of things I need to be able to do.

1. I need to be able to pull the data from datasource.xlsx even when datasource.xlsx is closed.
2. The links can't be absolute, because I will end up moving the two files around. The only guarantee I have is that they will both be in the same directory.

So after a bit of a google I got indirect.ext working, but the problem I have with that is that if I want to reference a cell in a closed workbook I have to have a full directory reference i.e. C:\mydumbwork\data\datasource.whatever.

Therefore I have two questions

1. Is what I'm asking for possible
2. If so, is it worth going to the trouble of trying to make it work instead of just dumping all the data from datasource.xlsx in to another worksheet in the datapresentation.xlsx workbook and linking internally? It won't look as well structured but I'm damned if I'm going to go for a super-complicated solution when an easy one can do the same.

Thanks!

# ? Aug 5, 2012 13:18

FrankeeFrankFrank: Apr 21, 2005; Say word son.

I have thousands of pictures, not porn believe it or not..., I need to hotlink all these pictures separately into individual cells. So what I'm doing now is right clicking on the cell, choosing hotlink, and browsing to the photo. I've probably done 100... and figured there has to be a better way.

I found this and am going through it now... http://forums.esri.com/Thread.asp?c=3&f=39&t=150522

But I know nothing of 'scripts' etc.

Thanks.

Anyone use hotpotato.avx ??

# ? Aug 13, 2012 16:02

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

FrankeeFrankFrank posted:

I have thousands of pictures, not porn believe it or not..., I need to hotlink all these pictures separately into individual cells. So what I'm doing now is right clicking on the cell, choosing hotlink, and browsing to the photo. I've probably done 100... and figured there has to be a better way.

I found this and am going through it now... http://forums.esri.com/Thread.asp?c=3&f=39&t=150522

But I know nothing of 'scripts' etc.

Thanks.

Anyone use hotpotato.avx ??

Do you already have a list of all the file names? I've done something like this before, I used some VBA to import all of the full file directory names ("C:\Folder\File1.jpg") into distinct rows, so I had a big list of non-linked strings in the cells. Then I looped through them again and used this little snippet to make each cell link to the destination contained within the cell:

code:

link = Cells(i, col)
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, col), Address:=link, TextToDisplay:=link

# ? Aug 13, 2012 18:08

FrankeeFrankFrank: Apr 21, 2005; Say word son.

DukAmok posted:

Do you already have a list of all the file names? I've done something like this before, I used some VBA to import all of the full file directory names ("C:\Folder\File1.jpg") into distinct rows, so I had a big list of non-linked strings in the cells. Then I looped through them again and used this little snippet to make each cell link to the destination contained within the cell:
code:
link = Cells(i, col)
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, col), Address:=link, TextToDisplay:=link

Might be easier to show you.... I have photos of doors, with varying numbers of photos per door. Anywhere from 1-8 photos. I know which photos go with which door and am working on renaming them something that makes sense. Photo# 8-101-1 = Bldg. 8, Door 101, 1st photo,... etc.

I then have a speadsheet with other door information and then within the spread sheet I want to hotlink to the various photos.

I'm not sure what you are describing would work the way I have the spread sheet set up. Or maybe it will work on a different tab and then I could just cut and paste the link in my spreadsheet.

I'm kind of at a loss when it comes to 'code' etc. like you posted, so if anyone has an idea you are probably going to have to walk me through it.

Thanks.

# ? Aug 13, 2012 19:14

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

FrankeeFrankFrank posted:

words

What he posted is a snippet of VB code that would turn a cell value into a link. So you would have to enter "C:\files\img_2069.jpg" into cell A1 and that macro would create the hyperlink for you to that location.

full code would look something like this...

code:

sub TextToLink()
   Dim i as integer
   Dim lnk as string

   do while activecell.value <> ""
      lnk = activecell.value
      ActiveSheet.Hyperlinks.Add Anchor:=activecell, Address:=link, TextToDisplay:=link
      activecell.offset(1,0).select
   loop
end sub

# ? Aug 13, 2012 21:36

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

FrankeeFrankFrank posted:

I'm not sure what you are describing would work the way I have the spread sheet set up. Or maybe it will work on a different tab and then I could just cut and paste the link in my spreadsheet.

Yeah, I can't quite make out the data on the spreadsheet but as best as I can tell, there isn't a quick and easy solution with the way that's set up. If your images were all named in a sort of hierarchical fashion, or there was a reference to translate those image names into that hierarchy, we could construct a pretty complex script to parse through those and assign links to your cells, but honestly that's not entirely useful, it might end up taking as much time as clicking a bunch, depending on how many links you have.

Are you tied to this overall spreadsheet format? There are a lot of easier ways to categorize this data that will make later translations and modifications much easier.

pizzaman5000 posted:

Hello thread. I have a little question that I hope someone with greater Excel nous than me can solve.

I've got two excel workbooks, one called datasource.xlsx and the other called datapresentation.xlsx. In datasource.xlsx I have a range of cells (B11:B42) which contain data. I want to be able to link these two books so that the data from datasource.xlsx will also appear in datapresentation.xlsx. This sounds simple; something like =[datasource.xlsx]sheet1!whateverthecellrefis would do.

Unfortunately, there are a couple of things I need to be able to do.

1. I need to be able to pull the data from datasource.xlsx even when datasource.xlsx is closed.
2. The links can't be absolute, because I will end up moving the two files around. The only guarantee I have is that they will both be in the same directory.

So after a bit of a google I got indirect.ext working, but the problem I have with that is that if I want to reference a cell in a closed workbook I have to have a full directory reference i.e. C:\mydumbwork\data\datasource.whatever.

Therefore I have two questions

1. Is what I'm asking for possible
2. If so, is it worth going to the trouble of trying to make it work instead of just dumping all the data from datasource.xlsx in to another worksheet in the datapresentation.xlsx workbook and linking internally? It won't look as well structured but I'm damned if I'm going to go for a super-complicated solution when an easy one can do the same.

Thanks!

I would save the hassle and just get the data you need into the presentation workbook, unless there's some serious size/storage concerns. I've messed around with closed workbook references and such, and it's never really as efficient as I thought it would be, and a lot more maintenance intensive. Usually much easier just to have a little data duplication between workbooks and not worry about it too much.

DukAmok fucked around with this message at 22:28 on Aug 13, 2012

# ? Aug 13, 2012 22:25

FrankeeFrankFrank: Apr 21, 2005; Say word son.

DukAmok posted:

Yeah, I can't quite make out the data on the spreadsheet but as best as I can tell, there isn't a quick and easy solution with the way that's set up. If your images were all named in a sort of hierarchical fashion, or there was a reference to translate those image names into that hierarchy, we could construct a pretty complex script to parse through those and assign links to your cells, but honestly that's not entirely useful, it might end up taking as much time as clicking a bunch, depending on how many links you have.

Are you tied to this overall spreadsheet format? There are a lot of easier ways to categorize this data that will make later translations and modifications much easier.

I'm pretty tied to it for this project, but we may have similar things in the future. Thanks a ton.

I thought I thumbnailed that spreadsheet image...

FrankeeFrankFrank fucked around with this message at 13:52 on Aug 14, 2012

# ? Aug 14, 2012 13:48

kapinga: Oct 12, 2005; I am not a number

FrankeeFrankFrank posted:

I'm pretty tied to it for this project, but we may have similar things in the future. Thanks a ton.

I thought I thumbnailed that spreadsheet image...

Yeah, without having a consistent pattern for your image files it's going to be tricky, and probably more work than it's worth.

Couple thoughts:
Edit2: Take a look at my edit below, as I think the Hyperlink function will be easier than using VBA for this.
I think it's going to be far easier if you figure out a way to get the file name and path into the spreadsheet, then run the VBA code DukAmok posted to convert it all into links.

If all the pictures are in the same folder, you can set that code up to automatically attach the path, so all you'd need to type in is the filename. If they all have the same extension (".jpg"), you can skip that as well.

Visual Basic .NET code:

Dim link As String = Cells(i, col)
link = "C:\Path\to\Pictures\" & link & ".jpg"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, col), Address:=link, TextToDisplay:=link

Since most of the pictures appear to be named consecutively, you can write an easy function to increment it by one:

code:

        [Column A]    [Column B]
[Row 1] pic00123      =LEFT(A1,LEN(A1)-1) & VALUE(RIGHT(A1,1))+1

I'm sure there's a cleaner way to do it, but this works. The value for column B would be pic00124

This won't completely automate the process, but it should reduce the amount of mindless clicking you have to do.

Also, take a look at this MSDN article to figure out how to turn that VBA code into a working macro.

Edit: Just realized there's a Hyperlink function. I would do the following:

code:

        [Column A]   [Column B]
[Row 1] pic00123     =HYPERLINK("C:\Path\To\Pictures\" & LEFT(A1,LEN(A1)-1) & VALUE(RIGHT(A1,1))+1 & ".jpg", LEFT(A3,LEN(A1)-1) & VALUE(RIGHT(A1,1))+1)

A1 should be a manually entered hyperlink to the relevant picture, with the text of just the picture name, no path and no extension. B1 takes that link text and turns it into a link to the next picture (assuming you know the path already). From there, you can just copy B1 into C1 to get the next picture.

kapinga fucked around with this message at 15:50 on Aug 14, 2012

# ? Aug 14, 2012 15:37

Dr. Fraiser Chain: May 18, 2004; Redlining my shit posting machine

I have an excel workbook with 100+ tabs that need to be split into separate workbooks. Is there a shortcut to do this or am I doomed to right click on each individual tab?

# ? Aug 14, 2012 21:12

ScarletBrother: Nov 2, 2004

Goodpancakes posted:

I have an excel workbook with 100+ tabs that need to be split into separate workbooks. Is there a shortcut to do this or am I doomed to right click on each individual tab?

From the previous page:

code:

Sub CreateWorkbooks()
    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim sht As Object
    Dim strSavePath As String
     
    On Error GoTo ErrorHandler
     
    Application.ScreenUpdating = False
     
    strSavePath = "C:\My Docs\" 'Your Pathname here
    Set wbSource = ActiveWorkbook
     
    For Each sht In wbSource.Sheets
        sht.Copy
        Set wbDest = ActiveWorkbook
        wbDest.SaveAs strSavePath & sht.Name & Format(Date, "MMDDYY") & ".xls"
        wbDest.Close
    Next
     
    Application.ScreenUpdating = True
     
    Exit Sub
     
ErrorHandler:
    MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub

Will save all your tabs as files named by their title to a path of your choice.

If you want to save them to individual folders,

Just change the following line from this

code:

wbDest.SaveAs strSavePath & sht.Name & Format(Date, "MMDDYY") & ".xls"

to this

code:

wbDest.SaveAs strSavePath & sht.name & "\" & sht.Name & Format(Date, "MMDDYY") & ".xls"

This will save the CBS sheet to a folder labelled CBS and the FOX sheet to the FOX folder.

Second part courtesy of Old James.

# ? Aug 14, 2012 21:17

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

kapinga posted:

Edit: Just realized there's a Hyperlink function. I would do the following:

code:

        [Column A]   [Column B]
[Row 1] pic00123     =HYPERLINK("C:\Path\To\Pictures\" & LEFT(A1,LEN(A1)-1) & VALUE(RIGHT(A1,1))+1 & ".jpg", LEFT(A3,LEN(A1)-1) & VALUE(RIGHT(A1,1))+1)

Ah yeah I had forgotten about that, definitely easier for how he's got the sheet set up. For my particular use, I ended up skipping to the VBA because I think HYPERLINK has a character limit on the URLs, 255 if I remember correctly. Adding the link in via VBA bypasses that limit.

# ? Aug 14, 2012 21:36

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Is there a good tutorial on how to use Excel 2010 Pivot Tables? The older version ones seemed so much simpler and better

# ? Aug 15, 2012 22:22

ZerodotJander: Dec 29, 2004; Chinaman, explain!

I don't think there has been significant changes in pivot table functionality at the level where anybody who would need a tutorial would notice. What are you having trouble with?

# ? Aug 15, 2012 22:35

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

ZerodotJander posted:

I don't think there has been significant changes in pivot table functionality at the level where anybody who would need a tutorial would notice. What are you having trouble with?

Really? There was no drag and drop functionality, and the thing just feels like a total mess. Am I just bad at Pivot tables now?

# ? Aug 15, 2012 23:36

Ragingsheep: Nov 7, 2009

Is it possible to use a full filepath and the indirect function?

# ? Aug 16, 2012 09:10

Mouse Cadet: Mar 19, 2009; All aboard the McEltrain
Next Stop: Atlanta

I have a series of procedures that uses the below code to search a column for a string and adds a comment in the next column. Let's say I have five procedures like the one below and each one searches for a unique name and enters a unique comment. Example: it searches for "Ron" and adds the comment "This person is Ron", another one searches for "Bob" and adds the comment "We have found Bob". Another searches for "Billy", another for "Timmy" and so on...

Is there a way to combine this into one procedure that searches for all five strings and provides five unique comments?

Sub Mark_cells_in_column1()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim I As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

MyArr = Array("ron")

With Sheets("Sheet1").Range("A:A")

.Offset(0, 1).ClearContents

For I = LBound(MyArr) To UBound(MyArr)

Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Offset(0, 1).Value = "X"
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
End If
Next I
End With

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

# ? Aug 21, 2012 12:48

Sonic H: Dec 8, 2004; Me love you long time

Here's an interesting one. Whilst it's possible to create a dynamic graph/chart in Excel using dynamic named ranges so the X/Y values automatically update if you add/remove data, is it possible to actually create new series on the fly?

For example, I have a table of data: with each row forming a category (project). Each row has 6 columns (categories). It is simple enough to plot each project's scores on a graph and have the graph update if a new row is added. Graphs are plotted as scores vs project and each category is highlighted in a stacked column - each column made up of the 6 categories.

The drawback is that the categories are predefined and can't be changed dynamically like the axes can be. For example, I'd like to be able to make each row a new series and if I add a new row, the number of chart columns would be updated. In short - can I swap the series and the x-axes data around - it would effectively mean that the x-axis would be static but the series would be dynamic. I can't find a way of doing this, so I guess it's not possible so I turn to the ever-knowing power of the forums.

Here is a diagram to explain what I have:

Basically: Each row of data is a column in the graph. Each (spreadsheet) column is a colour in a (chart) column. Can I make the series in the legend the x-axis and the x-axis a set of series that change dynamically? I appreciate that the style of graph may have to change to accommodate.

Sonic H fucked around with this message at 14:35 on Aug 21, 2012

# ? Aug 21, 2012 14:27

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

In Response to Mouse Cadet

code:

Sub Mark_cells_in_column1()
    Dim FirstAddress As String
    Dim MyArr(1 to 4) As String
    Dim Rng As Range
    Dim I As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

   MyArr(1) = "ron"
   MyArr(2) = "bob"
   MyArr(3) = "billy"
   MyArr(4) = "timmy"

   With Sheets("Sheet1").Range("A:A")

        .Offset(0, 1).ClearContents

        For I = LBound(MyArr) To UBound(MyArr)

    
            Set Rng = .Find(What:=MyArr(I), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                   Rng.Offset(0, 1).Value = "We have found " & proper(MyArr(I))
                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next I
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Old James fucked around with this message at 15:43 on Aug 21, 2012

# ? Aug 21, 2012 15:12

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

Sonic H posted:

Basically: Each row of data is a column in the graph. Each (spreadsheet) column is a colour in a (chart) column. Can I make the series in the legend the x-axis and the x-axis a set of series that change dynamically? I appreciate that the style of graph may have to change to accommodate.

You can do this with Visual Basic to have a macro trigger when the workbook is opened. With normal Excel you could define the blank ranges before hand the only drawback is you would have extra listings in your legend when those rows are null.

# ? Aug 21, 2012 15:15

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

I'm using Concatinate to pull together words to make a sentence, but one of the columns is a time. What is the function you use to pull that into the sentence? Is there a general string function?

# ? Aug 21, 2012 15:21

kapinga: Oct 12, 2005; I am not a number

Veskit posted:

I'm using Concatinate to pull together words to make a sentence, but one of the columns is a time. What is the function you use to pull that into the sentence? Is there a general string function?

Use the TEXT() function.

# ? Aug 21, 2012 15:24

Sonic H: Dec 8, 2004; Me love you long time

Old James posted:

You can do this with Visual Basic to have a macro trigger when the workbook is opened. With normal Excel you could define the blank ranges before hand the only drawback is you would have extra listings in your legend when those rows are null.

I had a feeling I needed to delve into the magic that is VBA. Cheers

# ? Aug 21, 2012 15:57

Dr. Fraiser Chain: May 18, 2004; Redlining my shit posting machine

ScarletBrother posted:

From the previous page:
code:
sweet macro
Second part courtesy of Old James.

Thanks for this macro ScarletBrother and Old James. I had to trouble shoot an error message, and I was gone last week so that delayed my thank you post (I had to install some Visual Basic tools for the macro to complete properly for the curious).

# ? Aug 21, 2012 21:51

Gooses and Geeses: Jan 1, 2005; OH GOD WHY DIDN'T I LISTEN?

Utterly stumped here. I'm no good with Excel but even the guy who I asked, who used to work with this stuff, can't get it to work.

Any help would be great.

Table looks odd as I've resized columns to hide data that I don't need at this point.
I'm trying to get it to look up the value in BH. I then want it to return the value in row 3 (the table headers) which it matches - for instance, in the first row (4) it should return 1B. In the second row, (5), it should return 1B.

I hope this is clear. I can't get hlookup, lookup or vlookup to work. Help!

Gooses and Geeses fucked around with this message at 23:24 on Aug 21, 2012

# ? Aug 21, 2012 23:22

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 02:46

Aredna: Mar 17, 2007; Nap Ghost

I think this will do what you need if you put it in BI4 and copy it down: =INDEX($AC$3:$BG$3,0,MATCH(BH4,AC4:BG4,0))

Note: This will have problems if you have hidden values that also produce a match. If that is possible to happen then you'll just need to hard code an ugly nested IF statement or change the way your data is laid out.

Aredna fucked around with this message at 00:13 on Aug 22, 2012

# ? Aug 22, 2012 00:10

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

«‹›82 »