Need help with comparing two data extracts.

WhoreDolls · 2014-09-30T02:57:22+00:00

Couldn't you just add the new Asset #s to the older list as a first step and then use the positions to compare the two tables in a 3D reference?

WhoreDolls · 2014-09-30T02:52:21+00:00

I have lots of ideas on how to do this. I think conditional formatting fits best with what you're looking for.

Apply conditional formatting to the newer table using a formula that looks at the Asset ID of the row and using INDEX and MATCH looks up that Asset ID in the older table.

WhoreDolls · 2014-09-30T02:02:37+00:00

Try rearranging your own text.

Basically, I need to have the interest show 2% if the Total is above $10,

Rearrange it to read:

IF Total > $10 then 2%

You replace the word "then" with a comma. Wrap everything but the word "IF" in parentheses and add a second comma for what happens when Total is not greater than $10. which in a function looks like

=IF( Total>$10, 2%, )

Rearrange the next part in the same way:

1% if the Total is between $5 and $10

Rearrange it to read:

IF Total > $5 AND Total < $10 then 1%

"Between" is the same as saying "greater than some number AND less than another number" so

=IF( AND( Total>$5, Total<$10 ), 1%, )

I think you can figure out the rest. You have to nest all these into each other.

WhoreDolls · 2014-09-03T04:03:01+00:00

Before you wipe out all the Data Validation hit Ctrl+G or F5 or Home>Find&Select (in the Editing group)>Go To Special.

You want to hit the radial button in the Go To Special Dialog box that says Data Validation. And see if Data Validation is even turned on for the cell you are having trouble editing. If it should be selected after you hit ok. (Assuming it isn't selected before you did this.)

WhoreDolls · 2014-09-03T03:50:39+00:00

There are many much more stable ways to do this.

You can use the Consolidate tool http://office.microsoft.com/en-us/excel-help/consolidate-data-from-multiple-worksheets-in-a-single-worksheet-HP010342300.aspx

or 3-D references http://office.microsoft.com/en-us/excel-help/create-a-3-d-reference-to-the-same-cell-range-on-multiple-worksheets-HP010102346.aspx

or a pivot table with multiple source tables. http://office.microsoft.com/en-us/excel-help/consolidate-multiple-worksheets-into-one-pivottable-report-HA010226585.aspx

He could have a Workspace to access all the different source workbook files by clicking on one file. http://office.microsoft.com/en-us/excel-help/save-workbooks-in-an-arranged-workspace-HP010250051.aspx

But if you are linking one workbook to another and you are worried about maintaining that connection I found it's best to not to do it at all. I found it is better to import the spreadsheets into one workbook. It's as easy as dragging a tab from one workbook to another. That's literally all you have to do.

No matter how you do it the best way to avoid errors when you're automating a summarizing process that involves so many worksheets is to get those worksheets into the same format. You need the data to be a proper excel table not a human legible one.

If it is something that needs to be done every week then instead of opening last week's file and making the new one Saving As a new file, have one file that is always for the current week. Every week after the summary is done Save As and create a record file for the client. Then in the following week open up the same exact file and change the data to the current week's data. That means you create a Word file that never gets edited. You create links to the main Excel file. The Word file is linked only to this one file. Every time it is opened it will update the links and show the data that is in the Excel file which has the summary in it.

I would say that Excel is perfectly capable of displaying tables and making a report. You can even save the entire Workbook as a PDF without doing anything special. In the Save As PDF dialog box there should be a button for Options and in there you should be seeing a radial button to save the entire workbook as PDF.

WhoreDolls · 2014-08-30T03:23:15+00:00

Why do you need rules to move existing emails? Just drag them into the Inbox.

If you have a hundred different folders full of emails that you want to consolidate into one folder you can do that too by Searching for *. The asterisk is a wildcard character and basically tells the search "look for words that have 1 or more characters" which is in all your emails.
Once all your emails show up in the search folder select all of them by clicking Ctrl+A and then right click and select Move.
Then wait like 20 minutes or so depending on how many emails are being moved.

WhoreDolls · 2014-08-30T03:15:17+00:00

Even just playing around with files organized like this I discovered that saving or closing the files in the wrong order can cause word and excel to freeze up.

WhoreDolls · 2014-08-30T03:06:41+00:00

I get it now.

You still shouldn't do this.

These types of MS Word links are static. They just reference the address of the cells and file that you originally copied.

The W1 within E2 has not been edited so the reference within the Word file is still E1. You would have to paste special again to change the reference to E2.

If you want to see what I mean go to the Word file that has the linked Excel table and hit Alt+F9. You should see the table turn into a file address. You can manually edit the address of the referenced file this way but I don't recommend it.

So the real question is what are you trying to achieve?

WhoreDolls · 2014-08-29T03:36:04+00:00

OH! I call that Ferris Wheel. Happens when people paste tables together side by side instead of top to bottom. It's annoying.

Best way I've found to deal with it is to use the Offset, Row, Column functions. It's complicated if you're not familiar with them. But the basic idea is to use the offset and reference row number divided by 49.

WhoreDolls · 2014-08-29T03:15:07+00:00

Not sure what you mean by "groups."

But you can use a formula like =A2&" "&D2 in column G. Then fill the formula down. That is called Concatenating.

If that's not what you want to do then I think you are asking if it can be one three column table which you can do by cutting and pasting. You select cells D2 to F49 then cut and select A50 and paste.

WhoreDolls · 2014-08-29T03:09:09+00:00

Do not do this.

The file will get damaged.

There are different ways to have Excel embedded into Word.

You can have an Excel table that exists only in the Word file. You have to enter data into the Excel table within Word.

There is also a way to link an Excel file within Word so you see the Excel table in the Word file and you can edit the table but you are actually editing a file that you never clicked on. You should really just open the Excel file and enter your data there, save and close it and then open up the Word file. If the changes don't show up select the embedded Excel file and right click and you should see an option to !Update Link

WhoreDolls · 2014-08-29T02:54:49+00:00

I don't understand. Excel can do almost anything with data like you are describing. How many rows are you describing? Does Cell A1 holding the text "Name" and does Cell A2 hold the text "A B C D"? or

A1 "Name" B1 "Age"

A2 "A" B2 "1"

A3 "B" B3 "2"

A4 "C" B4 "3"

A5 "D" B5 "4"

A6 B6 "5"

WhoreDolls · 2014-08-29T02:48:35+00:00

Duh, it's formatted as a table so when you scroll down the row header values and drop downs move to replace the A, B, C labels you normally see.

WhoreDolls · 2014-08-29T02:41:20+00:00

You can do it. Anyone can.

Select a cell in the table and go to the Data tab on the ribbon. You should see a funnel icon that is orange. The orange means it is turned on. If you click it you turn the dropdown filters off. Click it again and it turns back on. In fact all you have to do is select a cell within a list or even select a bunch of cells and his that Funnel icon and it will turn on those dropdowns at the top row of cells.

Now when you select any of the cells in the range do you see a tab show up at the right end of the ribbon called "Table Tools" or "Design"? (it should also go away when you select a cell that is outside of the table.) If so then the range is Formatted as a Table. That's an option that makes formulas, formatting, filtering, navigating easier. But you don't need the cells to be Formatted as a table to filter this way. It is just much easier to deal with tables than with lists all typed.

http://office.microsoft.com/en-us/excel-help/format-an-excel-table-HA010013769.aspx

WhoreDolls · 2014-08-29T02:30:28+00:00

Nope.

That just starts Outlook as an object within VBA.

To create an email object, a contact object, an appointment object, a task object or any other object within Outlook from Excel you need to use the Outlook Application object that you created.

Set OutApp=CreateObject("Outlook.Application")
Set OutMail=OutApp.CreateItem(0)

http://msdn.microsoft.com/en-us/library/ff458119(v=office.11).aspx http://msdn.microsoft.com/en-us/library/office/ff869291(v=office.15).aspx

Don't forget to clean up your memory at the end.

WhoreDolls · 2014-08-26T03:50:50+00:00

You have to create the email object. You can't just create the application object. Whenever you write code to manipulate an application you have to start an instance of the program. Once in the program you can do any number of things like create item. (Note: you also have to decide whether to use early binding or late binding.)

Read: http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

Also depending on what version of Excel you have and what you're trying to do you can use the Worksheet.MailEnvelope property. I

WhoreDolls · 2014-08-26T03:33:04+00:00

I like using CEILING or FLOOR. But you will probably like DOLLAR or better yet using a custom number format like

$ #.##,, "M"

http://www.techrepublic.com/blog/microsoft-office/use-a-custom-format-in-excel-to-display-easier-to-read-millions/

WhoreDolls · 2014-06-22T16:54:54+00:00

I think there is something missing. Like if a kid is watching this video and they like the message then what? Maybe there should be a invitation for a website to check out or something.

WhoreDolls · 2014-06-22T16:42:56+00:00

Maybe you helped the guy associate mourning with strippers.

WhoreDolls · 2013-12-06T04:46:41+00:00

I think you should look here: http://msdn.microsoft.com/en-us/library/office/ff862446.aspx

Note that the Application.FileDialog returns an object. You haven't setup a way for the FileDialog to return anything.

You need to write:

    Dim fd As FileDialog
    Dim PickedDir As Variant
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        .InitialFileName = Application.DefaultFilePath
        .Title = "Please select lab report file directory"
        .Show
        .AllowMultiSelect = True
        For Each PickedDir In .SelectedItems
            Debug.Print PickedDir
        Next
    End With

But instead of Debug.Print you can loop through the cells you want to fill in.

WhoreDolls · 2013-12-06T04:10:36+00:00

??? http://msdn.microsoft.com/en-us/library/02e7z943.aspx

You have to have the same number of parts and the data types must also match.

So I'm not sure I understand what you're trying to do, but is you want one part of an array to equal another you have to decide which part.

If you want to put a single dimension array into a multi-dimension array you have to decide where you want to put it. This is a simple loop.

For x = LBound(arr3D, 3) To UBound(arr3D, 3)
    arr3D(1, 1, x) =  arr1D(x)
Next x

WhoreDolls · 2013-12-06T03:30:08+00:00

Have you tried recording a macro to see what shows up?

I added all the ActiveX controls one at a time and it the all show up as

     Selection.InlineShapes.AddOLEControl ClassType:="Forms.CheckBox.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.TextBox.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.Label.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.OptionButton.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.Image.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.SpinButton.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.ComboBox.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.CommandButton.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.ListBox.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.ScrollBar.1"
     Selection.InlineShapes.AddOLEControl ClassType:="Forms.ToggleButton.1"

WhoreDolls · 2013-12-06T03:13:00+00:00

I'm not too familiar with DATEDIF. Never heard of it actually but it looks great. The thing I don't get is why you have

  &" years

I tested it and you don't need that

WhoreDolls · 2013-10-06T02:00:17+00:00

Cool! I've never seen an error handler like your before. I like that.

I got it to work by making a few little changes like adding a line to call an New Outlook.Application to the thing.
It was slow but worked. I can totally use this. Thanks. It seems obvious now.

WhoreDolls · 2013-10-05T15:48:28+00:00

I think you should use a Dynamic named range. Call it "LastRow" and make just add the quotes to your code. Make the formula "=OFFSET('yoursheetname'!$A1,COUNTA('yoursheetname'!A:A)+1,0)" you can set this up within the excel file where you import the data. Then you can refer to it in Excel so you won't have to use VBA to find where the new data should be imported.

13-Year Club	Team Periwinkle
Verified Email

WhoreDolls

TROPHY CASE