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 »

ZerodotJander: Dec 29, 2004; Chinaman, explain!

I would do a VLOOKUP from Column A into Column B and keep all the ones that don't error out.

So in cell C1, =VLOOKUP(A1, B:B, 1, False) and fill down. This will look in Column B for the value in A1. If it is found, it will just reprint the value again. If it is not found, it will produce an error message.

# ? Mar 29, 2011 17:01

Adbot: ADBOT LOVES YOU

# ? Apr 24, 2024 21:46

The Mechanical Hand: May 21, 2007; as this blessed evening falls don't forget the alcohol

ZerodotJander posted:

I would do a VLOOKUP from Column A into Column B and keep all the ones that don't error out.

So in cell C1, =VLOOKUP(A1, B:B, 1, False) and fill down. This will look in Column B for the value in A1. If it is found, it will just reprint the value again. If it is not found, it will produce an error message.

drat! Thanks a million. Out of simple curiosity how does this work out on the technical side? Like I said, I'm completely retarded with this sort of stuff so I'm not sure what's going on behind the scenes here.

# ? Mar 29, 2011 17:06

ZerodotJander: Dec 29, 2004; Chinaman, explain!

VLOOKUP looks for a value in the first column of a table, then returns the corresponding value X columns over. That formula looks for the value (A1) in the first column of a table (column B) and returns the value in the first (1) column of that table. If the value isn't found at all, it just errors out. The False just specifies that it looks for an exact match, rather than just returning the closest value in alphabetical order.

# ? Mar 29, 2011 17:52

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I also use COUNTIF for similar operations since it can serve the same purpose, but also lets you know if there's duplicates.

The way COUNTIF works is it'll count each occurrence in the range. If all values are unique you'll get a '1' in your derived column instead of the original value (like VLOOKUP provides). If the email occurs twice you'd get a 2, etc. If there's no match it'll be 0 instead #ERROR. The similar syntax to what was provided above is:

COUNTIF(A:A,B1)

It works in reverse, first the range then the value. It's not exactly what you wanted (takes a bit more work to get the values) but it can be a handy function.

# ? Mar 30, 2011 00:23

DankTamagachi: Jan 20, 2005; Tamagachi to your throat!

I have two worksheets I use daily for work. One is a shared sheet that tracks progress on projects and the second is a sheet that runs macros to generate lists of words. When the shared sheet is closed, the generator runs very quickly. However, when the shared sheet is open, the generator runs extremely slowly, generating terms line-by-line. Why does the completely separate generator sheet slow down when the other is opened? Has anyone encountered this before?

# ? Apr 4, 2011 17:56

Aredna: Mar 17, 2007; Nap Ghost

Do you have a lot of INDIRECT formulas in the 2nd file? I've seen cases where they can slow things down quite a bit. This happens because every time a change happens in file 1 then all of the INDIRECT formulas in file 2 that didn't exist before are now being recalculated constantly.

# ? Apr 4, 2011 22:15

angry armadillo: Jul 26, 2010

I couldn't see a vba thread so I am going to assume I am in the right place. If not tell me to go away

Is it possible for a macro to get the free space available on a network share - one of my duties is a daily check of all servers including hard drive space - I was wondering if a macro could fill this in by getting free space from the C$ and D$ (etc) shares

Any help or advise would be appreciated. Thanks.

# ? Apr 5, 2011 00:00

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

angry armadillo posted:

I couldn't see a vba thread so I am going to assume I am in the right place. If not tell me to go away

Is it possible for a macro to get the free space available on a network share - one of my duties is a daily check of all servers including hard drive space - I was wondering if a macro could fill this in by getting free space from the C$ and D$ (etc) shares

Any help or advise would be appreciated. Thanks.

First google result for 'excel vba network drive size'
http://www.codeforexcelandoutlook.com/vba/using-wmi-services-in-vba/drive-information-local-network-mapped-drives/

There's probably a hell of a bunch of auth stuff involved but since you're using VBA for file access I assume you don't care about running it in admin context.

# ? Apr 5, 2011 00:24

Econosaurus: Sep 22, 2008; Successfully predicted nine of the last five recessions

I don't know much about excel at all, but is it possible to graph equations? I need to graph things like B=aq, B=b+c(q-Q')^k, etc. Would excel be the right program to use for this?

# ? Apr 5, 2011 01:46

jet_dee: May 20, 2007; Blah blah blah Nationstates is cool blah blah blah

Do people have any recommended books or online courses which I could look at to brush up on my Excel skills whilst I'm unemployed and looking for work (a lot of jobs for "numerate econ grads" specify being able to use Excel to analyse data and so on)?

I found some helpful material on the Microsoft Support for MS Office, including example spreadsheets, and googling for Excel is a good way to find what some universities provide in the way of online Excel instruction for their students.

# ? Apr 8, 2011 15:01

TasteMyHouse: Dec 21, 2006

Econosaurus posted:

I don't know much about excel at all, but is it possible to graph equations? I need to graph things like B=aq, B=b+c(q-Q')^k, etc. Would excel be the right program to use for this?

All you need to do is make a table containing the values you wanna plot by making a column representing your independent variable and then a column applying the rule of the equation to that column.

# ? Apr 8, 2011 16:23

ZerodotJander: Dec 29, 2004; Chinaman, explain!

jet_dee posted:

Do people have any recommended books or online courses which I could look at to brush up on my Excel skills whilst I'm unemployed and looking for work (a lot of jobs for "numerate econ grads" specify being able to use Excel to analyse data and so on)?

I found some helpful material on the Microsoft Support for MS Office, including example spreadsheets, and googling for Excel is a good way to find what some universities provide in the way of online Excel instruction for their students.

You can get really far by just going through the Ribbon and learning what all the buttons in it do.

# ? Apr 8, 2011 17:02

jet_dee: May 20, 2007; Blah blah blah Nationstates is cool blah blah blah

ZerodotJander posted:

You can get really far by just going through the Ribbon and learning what all the buttons in it do.

Heh, maybe I should spring for Office 2011 (for Mac) so I can have the Ribbon (still on version 2004 here).

# ? Apr 8, 2011 17:12

Dr.Magnificent: Dec 24, 2007; Comes with hands on care.; Fun Shoe

When I was first learning, I found these videos pretty helpful. http://www.youtube.com/user/ExcelIsFun#p/c/D8EEA7196D943ED5/0/Wq7p4uoj4Vo

He has other playlists full of even more videos. There is even a playlist devoted to him and another excelblogger solving various problems with macros or formulas, so it exposes you to other ways to do things.

# ? Apr 8, 2011 21:22

r2tincan: Jul 21, 2004

Can I count the numbers of "not zero values" in a row of cells?

So if it says 8, 3, 1, 3, 5, 0, 0, "", 1 in a row, I want it to count 6.

How do I do this?

Thanks!

# ? Apr 11, 2011 01:17

Aredna: Mar 17, 2007; Nap Ghost

Assuming your example data is in cells A1 to A9, I think this will do it:

code:

=countif(A1:A9,">0")

# ? Apr 11, 2011 02:19

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

That'll work, but if you wanted to include the empty string <>0 might work too. If you don't want to deal with the string the best way would be to clean the data and ensure it's all numeric, or even do CONVERT on the seek range.

# ? Apr 11, 2011 19:40

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

I'm trying to remove duplicates, but I need to remove both duplicates, not just one of the duplicates. That's what I'm getting when I filter 'unique records'

For example, I have a list of all goons:

Andy
Bob
Carl
Dave
Eddy
Frank
George

Then I have a list of goons that have girlfriends:

Andy
Carl
Eddy
Frank

Combining the lists gives me:

Andy
Andy
Bob
Carl
Carl
Dave
Eddy
Eddy
Frank
Frank
George

If I combine then and filter for unique, I end up getting the original list of all goons. Basically it filters out all the duplicates (from the girlfriend list).

But what I'd like to end up with is:

Bob
Dave
George

Goons without girlfriends. Basically combine the lists and remove BOTH references to anyone who is on the list twice. Or, extract only the people who appear once.

Is there a simple way to do this?

# ? Apr 20, 2011 17:58

Bob Morales: Aug 18, 2006; ~~Just wear the fucking mask, Bob~~

I don't care how many people I probably infected with COVID-19 while refusing to wear a mask, my comfort is far more important than the health and safety of everyone around me!

I ended up doing a conditional format based on duplicates, then sorting by color and just deleting the colored ones.

# ? Apr 20, 2011 18:39

Aredna: Mar 17, 2007; Nap Ghost

Assuming your data starts in cell A1, put this in B1:

code:

=countif(A:A,A1)

then copy it down beside all of your data. Sort ascending and delete anything >1.

# ? Apr 20, 2011 20:13

Butt Soup Barnes: Nov 25, 2008

Is there a way to hide fields in a pivot chart without removing the actual data in the pivot table? I have 6 or 7 fields in my pivot table but only want to display 2 on a chart.

# ? May 9, 2011 18:58

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Is there any particular reason you're using a pivot chart? In general for almost all applications I find it much more convenient to copy data out of the pivot table into a normal table, then chart that with a regular chart. If you want the chart to be dynamic based on the pivot table, just create the normal table with references instead of copying the data out directly.

# ? May 9, 2011 19:01

Butt Soup Barnes: Nov 25, 2008

ZerodotJander posted:

Is there any particular reason you're using a pivot chart? In general for almost all applications I find it much more convenient to copy data out of the pivot table into a normal table, then chart that with a regular chart. If you want the chart to be dynamic based on the pivot table, just create the normal table with references instead of copying the data out directly.

I didn't even think about that, thanks.

One other question now:

I want my charts to be on a separate worksheet from the pivot table since they take up too much space if they are together. Is there any way for the pivot table report filter fields to appear on another tab? That way my manager can manipulate the chart without having to go back to the table tab.

# ? May 9, 2011 19:56

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Yes, but it requires a little bit of VBA. It also works much better for the Report Filter level than lower level fields. I'm going to assume you are working with Excel 2007 or 2010 here.

First, you need to create a Support tab that contains your Pivot Table. Then, create a secondary table off to the side that is just references to the Pivot Table, duplicating it as a regular table but that will dynamically change if the Pivot Table changes.

Off to the side, also list all of the fields in your Report Filter field in a column- e.g., if you are filtering by month, make a list of all applicable months.

On your actual dashboard tab, create your desired chart, pulling the data from the secondary table.

Then, on your dashboard tab, add an ActiveX ComboBox - you have to go into your Excel Options and check off "Show Developer Tab in the Ribbon" under Popular, then you will have access to the Developer Tab and can Insert ActiveX ComboBox.

Go into Design Mode (next to the Insert button in the Developer Tab) and click on your new ComboBox, and click Properties. Note the name of your ComboBox - most likely it is ComboBox1, but it might be different if you've been experimenting. I find it helpful to switch to the Categorized view, instead of the Alphabetic view.

Under Data->ListRows, enter how big you want your dropdown to be - if it is less than the number of values to be in the dropdown, you'll get a scrollbar. For example, 12, if you want all months to be visible; 6 if you just want half.

You can change the Name of the ComboBox under Misc->(Name) if you want it to be something easier to remember.

Under Misc->ListFillRange, enter the reference for your list of values on your Support Tab (e.g., Support!$D$5:$D$27). This populates your ComboBox selector with the possible values for your Report Filter.

Now that you have your dropdown selector, you just have to tie it to your pivot table (and therefore chart). Close the properties box (everything saves automatically) and click on View Code (make sure your ComboBox is selected, you want the code to be associated with the ComboBox).

Enter in the following VBA (with Sheet and object names adjusted to your specific needs):

code:

Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

    Sheets("Support").Visible = True
    Sheets("Support").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").CurrentPage = ComboBox1.Value
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Dashboard").Select
    
Application.ScreenUpdating = True

End Sub

What this does, is that it turns off screen updating so your boss doesn't see a ton of weird spreadsheets flash by. It then unhides your Support tab (which you would normally leave hidden to keep your Dashboard neat and discourage tampering), selects it, clears any filters on your PivotTable1's Month field (adjust Pivot Table and Field name accordingly) and then sets it equal to whatever the value of your ComboBox1 is. This updates your pivot table, which updates your secondary table, which updates your chart. The Support tab is hidden again, and focus is returned to the Dashboard, and ScreenUpdating is turned back on. The effect is that your boss clicks the right Month in the dropdown, and his chart changes.

It might seem complicated if you haven't worked with ActiveX Objects or VBA in Excel before, but it's actually pretty straightforward.

Common sources of error:

Make sure that your Support tab's field list for the Dropdown is in the correct order, and everything is capitalized and spelled correctly - it has to match the values the PivotTable will find valid exactly.

Make sure you are referencing the correct ComboBox and PivotTable.

Make sure you set your Support tab's zoom to 100%. For some bizarre reason, the ComboBox will produce an error if the Dashboard and Support tab are not set to either 100% or exactly the same zoom. So just make sure that the Support tab is at 100% and then it doesn't matter what zoom the Dashboard is at.

Hope that provides what you need!

# ? May 9, 2011 20:34

Butt Soup Barnes: Nov 25, 2008

ZerodotJander posted:

Yes, but it requires a little bit of VBA. It also works much better for the Report Filter level than lower level fields. I'm going to assume you are working with Excel 2007 or 2010 here.

First, you need to create a Support tab that contains your Pivot Table. Then, create a secondary table off to the side that is just references to the Pivot Table, duplicating it as a regular table but that will dynamically change if the Pivot Table changes.

Off to the side, also list all of the fields in your Report Filter field in a column- e.g., if you are filtering by month, make a list of all applicable months.

On your actual dashboard tab, create your desired chart, pulling the data from the secondary table.

Then, on your dashboard tab, add an ActiveX ComboBox - you have to go into your Excel Options and check off "Show Developer Tab in the Ribbon" under Popular, then you will have access to the Developer Tab and can Insert ActiveX ComboBox.

Go into Design Mode (next to the Insert button in the Developer Tab) and click on your new ComboBox, and click Properties. Note the name of your ComboBox - most likely it is ComboBox1, but it might be different if you've been experimenting. I find it helpful to switch to the Categorized view, instead of the Alphabetic view.

Under Data->ListRows, enter how big you want your dropdown to be - if it is less than the number of values to be in the dropdown, you'll get a scrollbar. For example, 12, if you want all months to be visible; 6 if you just want half.

You can change the Name of the ComboBox under Misc->(Name) if you want it to be something easier to remember.

Under Misc->ListFillRange, enter the reference for your list of values on your Support Tab (e.g., Support!$D$5:$D$27). This populates your ComboBox selector with the possible values for your Report Filter.

Now that you have your dropdown selector, you just have to tie it to your pivot table (and therefore chart). Close the properties box (everything saves automatically) and click on View Code (make sure your ComboBox is selected, you want the code to be associated with the ComboBox).

Enter in the following VBA (with Sheet and object names adjusted to your specific needs):
code:
Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

    Sheets("Support").Visible = True
    Sheets("Support").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").CurrentPage = ComboBox1.Value
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Dashboard").Select
    
Application.ScreenUpdating = True

End Sub
What this does, is that it turns off screen updating so your boss doesn't see a ton of weird spreadsheets flash by. It then unhides your Support tab (which you would normally leave hidden to keep your Dashboard neat and discourage tampering), selects it, clears any filters on your PivotTable1's Month field (adjust Pivot Table and Field name accordingly) and then sets it equal to whatever the value of your ComboBox1 is. This updates your pivot table, which updates your secondary table, which updates your chart. The Support tab is hidden again, and focus is returned to the Dashboard, and ScreenUpdating is turned back on. The effect is that your boss clicks the right Month in the dropdown, and his chart changes.

It might seem complicated if you haven't worked with ActiveX Objects or VBA in Excel before, but it's actually pretty straightforward.

Common sources of error:

Make sure that your Support tab's field list for the Dropdown is in the correct order, and everything is capitalized and spelled correctly - it has to match the values the PivotTable will find valid exactly.

Make sure you are referencing the correct ComboBox and PivotTable.

Make sure you set your Support tab's zoom to 100%. For some bizarre reason, the ComboBox will produce an error if the Dashboard and Support tab are not set to either 100% or exactly the same zoom. So just make sure that the Support tab is at 100% and then it doesn't matter what zoom the Dashboard is at.

Hope that provides what you need!

I won't have time to attempt this until tomorrow but I really, really appreciate your quick and thorough reply. I'll let you know how it works out.

# ? May 9, 2011 20:37

ZerodotJander: Dec 29, 2004; Chinaman, explain!

No problem, I have been doing a lot of this stuff this year, so it was just writing up something I've been doing lately anyway.

# ? May 9, 2011 20:42

Thel: Apr 28, 2010

I've been asked to put together a worksheet to go on sharepoint so the building/grounds/property guys can track job requests.

So, they want:
- Password-protected sheet (done, but off while I struggle with the rest of it)
- Three user-editable columns (done):
-> location, dropdown select from a list
-> staff name
-> description of job/issue

The bits I'm struggling with:
- Timestamp in a cell when a new job is entered (ie. the location select is column B, they want to automatically fill in the appropriate row in column A, when the location is selected, with the current time - no overwriting)
- Lock the user-editable cells once they have data in them.

The code I have so far:

code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  With Target
    If .Count > 1 Then
      Debug.Print "Exiting Sub - Count > 1"
      Exit Sub
    End If
    If Not Intersect(Range("B2:B5000"), .Cells) Is Nothing Then
      Debug.Print "Intersection with column B, proceeding"
      Application.EnableEvents = False
      If Not IsEmpty(.Value) Then
        Debug.Print "Column B not empty, proceeding"
        With .Offset(0, -1)
          If IsEmpty(.Value) Then
            Debug.Print "Column A empty, proceeding"
            'ActiveSheet.Unprotect
            .NumberFormat = "dd/mm/yyyy"
            .Value = Now
            'ActiveSheet.Protect
          Else
            Debug.Print "Column A not empty, aborting"
          End If
        End With
      Else
        Debug.Print "Column B empty, aborting"
      End If
      Application.EnableEvents = True
    Else: Debug.Print "Herp"
    End If
  End With
  If Target.Cells.Count > 1 Then Exit Sub
  If Not Intersect(Target, Range("B3:D5000")) Is Nothing Then 'set your range here
    'ActiveSheet.Unprotect
    Target.Locked = True
    'ActiveSheet.Protect
  End If
End Sub

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

e: Discovered Debug.Print. Scattered a few of those through this chunk of code, now it's doing exactly what I want it to do. :what:

Excel.

Thel fucked around with this message at 00:42 on May 11, 2011

# ? May 10, 2011 22:27

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

Thel: Apr 28, 2010

G-Dub posted:

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

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. :hurr:

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

Thel fucked around with this message at 00:11 on May 12, 2011

# ? May 11, 2011 22:41

Thel: Apr 28, 2010

Finally got everything working the way I want.

Except the loving macro warning pops up every time I open it. This is a sheet that will eventually be going on the company sharepoint server (so like http://companyname-sharepoint/foldername/Maintenance Spreadsheet.xls). Is there any way I can set a URI as a trusted location in Excel? Signing isn't going to work since the spreadsheet will be constantly changing.

Something that can be pushed to all users by GPO would be awesome, but if they have to set it up themselves that's fine (I can write a guide for that :j:

).

e: I don't know what a "loving macro wartning" is, but I want no part of it. :stare:

# ? May 12, 2011 05:01

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

Well I ended up with quite the excel problem.

I have a rather large database (~4000 data points). These points include location data in the form of Latitude and Longitude. In a good clean copy this data is split between two columns. One would read Latitude: 40.5633 and the other Longitude: -124.3533.

Now the ugly database I have to clean up has this data dumped as one string into one column, such as 70227N148316W. Is there an easy way to split this data up? Or is this an old fashion grind-fest of Ecel: The Korean MMO?

# ? May 12, 2011 23:24

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Goodpancakes posted:

Well I ended up with quite the excel problem.

I have a rather large database (~4000 data points). These points include location data in the form of Latitude and Longitude. In a good clean copy this data is split between two columns. One would read Latitude: 40.5633 and the other Longitude: -124.3533.

Now the ugly database I have to clean up has this data dumped as one string into one column, such as 70227N148316W. Is there an easy way to split this data up? Or is this an old fashion grind-fest of Ecel: The Korean MMO?

Sorry I don't understand lat/long very well; would the first value always end in N and the second always end in W? If so that's pretty straight forward. Couldn't you just do a search and replace on 'N' and replace it with 'N,' and then save the resulting file as a CSV?

# ? May 13, 2011 00:15

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

Scaramouche posted:

Sorry I don't understand lat/long very well; would the first value always end in N and the second always end in W? If so that's pretty straight forward. Couldn't you just do a search and replace on 'N' and replace it with 'N,' and then save the resulting file as a CSV?

This is certainly a great idea, and I feel a bit daft for not thinking of it (yes each one will end with N or W). My question is, then, since the file contains a lot of other information this would replace all Ns with "N " yes? Can this be done by column?

# ? May 13, 2011 00:22

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Goodpancakes posted:

This is certainly a great idea, and I feel a bit daft for not thinking of it (yes each one will end with N or W). My question is, then, since the file contains a lot of other information this would replace all Ns with "N " yes? Can this be done by column?

I just tested it and if you highlight just one column (e.g. by clicking the column head) and then do the replace it only worked within that column.

# ? May 13, 2011 00:39

ZerodotJander: Dec 29, 2004; Chinaman, explain!

You can also just select that column, click Data->Text to Columns and specify it as delimited by the character 'N', then find and replace the W with nothing and you're good.

# ? May 13, 2011 01:16

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

Thanks a lot guys. This will certainly help in cleaning up this hellscape of an excel file.

# ? May 13, 2011 01:45

Butt Soup Barnes: Nov 25, 2008

ZerodotJander posted:

Awesome Excel advice

Hey man, thanks again for that write-up. It worked great and it inspired me to learn more about VBA. I just created a dashboard from scratch in Excel using VBA and my boss is incredibly happy.

I have another question though. Now that I have this Dashboard in Excel working the way I want it to, I want to integrate it with Access. Right now everything works fine because I am using sample data to generate the reports. In the future there will be upwards of 50,000 records I would have maintain, and obviously Access is far better suited for that.

Is it possible to store everything in Access - databases, pivot tables, etc. and generate the charts/dashboard in Excel without having to import data into another worksheet?

# ? May 13, 2011 15:59

ZerodotJander: Dec 29, 2004; Chinaman, explain!

I believe there is a way to import data from Access into Excel automatically and update stuff, but unfortunately I don't have any experience with it. Good luck!

# ? May 13, 2011 16:23

Butt Soup Barnes: Nov 25, 2008

ZerodotJander posted:

I believe there is a way to import data from Access into Excel automatically and update stuff, but unfortunately I don't have any experience with it. Good luck!

Welp, I guess it's time to get my reading on and find some books on Access/VBA.

Thanks again for all your advice though, you may have literally saved my job due to impending cutbacks/layoffs.

# ? May 13, 2011 16:57

Adbot: ADBOT LOVES YOU

# ? Apr 24, 2024 21:46

Aredna: Mar 17, 2007; Nap Ghost

You can have Excel use External Data Sources and point it to the Access file. If it's a pivot table that's an option for the data source, but you can also just have raw data update into the cells.

# ? May 13, 2011 17:59

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

«‹›82 »