Replies

Dear ab2537:

Attached is a solution based on Hlookup formula. Right now it picks up rows 3 – 75 on both worksheets. But can be modified to include more rows if needed. I also put in a special ID formula to better track where the data is coming from.

This approach is flexible and would allow you to added more columns to the Master based on the Columns in the Data sheets.

Hope this helps

DuthieT

Dear AceWak:

If I completely understand what you are trying to do see the attached workbook.

In the Worksheet “U093K007” using the Endroit Column and String Formulas I extracted the key words found in the Columns of the Plan Worksheet

Lastly by using Advance Filters the “NewData” worksheet should display the required items to be billed.

Please read and follow the simple steps in the Instruction Worksheet

TIP – If desired you can record Macros for each Plan then run the Macro and the sheet will automatically update. I skipped this step since you appeared to want to avoid VBA.

Good Luck
DuthieT

Keenplanner:

I found this on the WEB. Perhaps it will help. I have not tried to use the below solution.
Regards,
Duthiet

A script that performs this function is available through the Actions Exchange at adobeusers.com:
http://acrobatusers.com/actions-exchange/create-comment-summary

Download the pdf from the link above, which includes instructions on how to use the Create Comment Summary Action. Note that you must dowload and import the Create Comment Summary.sequ file attached to the pdf. This link contains a video tutorial.

This is a great tool, but could be improved by grouping actions related to the same comment on single rows, rather than create a new row for each action. I will create a new post to inquire about how this functionality might be achieved.

rcough:

Welcome to the lounge!!! Retired Geek has given you a great way.

However, you can do this without a macro by using filters.

First Select the Data Ribbon or in Excel 2000 the Filter Option

Then on your sheet highlight the rows in col AG you want to work on. Make sure the first items in your highlighted rows has some data (ie not blank)

Now Click the Filter on the Data Tab – Excel will create a Filter List with a drop down. Click on the drop down and deselect the Archive Element. Close this tab and those items are hidden. (filtered out). Any time you want to see all the rows just click the drop down filter and select the archive element.

Gotta love Excel Filters

DuthieT

Did this always occur? Or did it begin after updates were installed?

You might try uninstalling and reinstalling Excel and see if this solves the issue.

Dear All:
Science says time is infinite, but in Excel there are only 1,440 minutes in a day. Based on that principal see the attached

I am sure others can further simplify this even more, but I do know this one works.

If you need to copy the results remember to the Copy Values rather than the formulas.

Hope this helps

DuthieT

Robert

OK the below macro will do 4 Cells so in Cell B50 put the background Colour , B51 the next background colour, B52 the next background colour, and lastly in B53 the final background color. Results will shown in cells C50 to C53

Regards,

Sub Color()
‘
‘ Color Macro
‘
Dim R As Long
Dim Z As Long
‘
R = 0
ActiveSheet.Range(“B50”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C50”).Activate
ActiveCell.Value = R
R = 0
ActiveSheet.Range(“B51”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C51”).Activate
ActiveCell.Value = R
R = 0
ActiveSheet.Range(“B52”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C52”).Activate
ActiveCell.Value = R
R = 0
ActiveSheet.Range(“B53”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C53”).Activate
ActiveCell.Value = R
R = 0
‘
End Sub

Robert:

This worked for me.

1. Set a Range Name that covers the Pivot Table Data. In my Code that range is “MyGroup”
2. In a blank cell of the Excel Sheet set the background colour to equal the colour used in the PivotTable to highlight. In my Example it was Cell “B50”
3. Run the below VBA Code. In the below example it puts the total in Cell “C50”

VBA Code

Sub Macro3()
‘
‘ Macro3 Macro

Dim R As Long
Dim Z As Long
‘
R = 0
ActiveSheet.Range(“B50”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each mCell In Selection
If mCell.Interior.Color = Z Then
R = R + mCell.Value
End If
Next
ActiveSheet.Range(“C50”).Activate
ActiveCell.Value = R

End Sub

Hope this helps.

DuthieT

If Column B has no text or alpha numeric entries then you could try the following.

In the worksheet in any empty column (Sale Col “D”) put in D1 the formula =10/B1. Copy down to D10000.

Open VBA and put in the following Code.

Code:

Sub RemoveIt ()
Range(D1:D10000).Select
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.EntireRow.Detete
End Sub

Make excel activce run the Macro and it should delete the desired rows rather quickly.

Hope this helps.

DuthieT

Abhi:

Maudibe has provided an excellent solution. However if you are still interested in “Connections” A bit more detail on getting fully connected.

Step One:

Assuming that the data can be converted to a flat Table please make sure that the first row of the needed worksheets in the closed file have each Column Name in the first row.

Step Two:
In an Open workbook on the Data Ribbon select “Connections” Click Add and create a connection to the closed workbook to the first worksheet.
Next repeat the above but this time create a connection to the second worksheet.

Step Three
On the Data Ribbon Select the “Existing Connections” Icon. A dialog box will open showing all of the current connections available. Find the connection for the first worksheet in the closed workbook. “Double Click” on the item. A new dialog box will open, Check you want a table and check you want it in a new worksheet. This should created a new worksheet with the data. Repeat for the Second Worksheet. You will now have the same data as in the close workbook. But if anyone happens to open the Closed Workbook and changes some data you can refresh you connection and the new information will be connected to your open workbook.

Lastly If all the data is imported you will have to filter the data in the Open Workbook.

Hope this helps.

TD

Abi:

As an alternative you might try using the Data Connection function. On the Data Ribbon find and select the “Connections” Icon. In the Dialog box Select “Add” and folllow the prompts. Excel should create a new worksheet in the Open Book with the data you need and you would change your formulas to reference this sheet rather than the closed workbook.

Regards.

TD

Kweaver

The solution I propose does not use a Macro but its fast and should meet your needs.

Attached in the Workbook “Input2.xlsx” It has been set up to be the master Workbook.

What you would do is first import the text file into an Excel workbook and change the Worksheet name to what you want it to be.

Next Open the Input2.xlsx workbook and copy the renamed worksheet into the master Workbook
Then on the “Revised Data” Worksheet in Cell H3 type in the exact name of the imported worksheet.
You will now have the data revised as you requested. If you need it be “Flat” just copy and paste special “Values”

Hope this helps.

TD

Dear Bhushanvshah:

Does the attached help? It uses 3 pivot tables to show results. By Clicking the Text Box “Update Tables” it will refresh all three pivot tables and give the results in your request.

Currently the Pivot Table is set to pick up 5,000 lines of data. Of course if more lines are needed you will need to change the data source via the Pivot Table Ribbon. I also included a reference to the row number where the data is being pulled from.

Regards,

Tom D

Dear Bhushanvshah:

Attached is a solution. It uses a new formula in Col L and then advanced Data Filter.

Each time you populate the sheet you will need to go to the “Data Ribbon” and select Filter “Advanced” make sure to select copy to a different location and press OK. Each time you will get the results.

The first worksheet works for Excel 10 and above, the second worksheet will work with lower versions.

Lastly, the criteria for returning information had a conflict. Your first Criteria was all stocks that equal High which by definition would pick up stocks both above and below 75 therefore the >75 AND High does not pick up new items. Also the information provided were outside the criteria so the log would result in no finds so I added more data that meet the criteria.

Hope this helps.

TomD

Dear MNN:

Does the below help. These notes were made when I put together dependent drop down.

Create Multiple Drop Down Lists Based On Prior Selection

The real Trick to getting this to work is the excel function INDIRECT()

Example:

Company has 4 Regions – Americas Europe Asia Latin America
Each Region has between 10 to 60 Countries
Each Country has between 1 to 10 Legal Entities

You want the user to first select a Region then Country, and lastly the Legal Entity

Region Country Company

Range 1 Range 2 Range 3
Austria Company 1
x Company 2
x Company 3
x Company 4
x
x
x
Venezuela

For Range 1 – Create a Data Validation rule using the “Data” Ribbon – Data Tools – Data Validation

Under Settings for “Source” Put in “=Regions” where Regions equals a range name with the four regions

For Range 2 – Create a Data Validation rule using the “Data” Ribbon – Data Tools – Data Validation

Under Settings for “Source” Put in “=INDIRECT(Prior Selection Cell)” where prior selection cell equals the exact cell where the previous region name was selected

There must be a predefined range name that exactly matches any Region a user may select. This range will including all the
Countries for a particular Region (Canada, Mexico, Bermuda, Puerto Rico, etc.)

For Range 3 – Create a Data Validation rule using the “Data” Ribbon – Data Tools – Data Validation

Under Settings for “Source” Put in “=INDIRECT(Prior Selection Cell)” where prior selection cell equals the exact cell where the previous Country name was selected

There must be a predefined range name that exactly matches any Country a user may select. This range will including all the Legal Entities
Located in a Country such as “Austria” Range would include (Company 1, Company 2, Company 3, Company 4)

Regards,

Tom D