Replies

Zeddy:

Yes Totally Awesome!!!

I have never used CountIf and not only did you use CountIf you put it on Steroids.
Great Solution.

DuthieT

Ankitag85:

Attached is a formula based solution, no Maco needed.

I did insert two rows in the data sheet and put the formulas on the data sheet.

If you don’t want to touch the data sheet then add a new sheet copy the formulas to that sheet and make sure the formula points to the proper cells in the Data and Shorcut sheets. Also copy all forumlas down as many rows as needed

Good Luck

DuthieT

Dear anasali94

I woud suggest using formulas rather than VBA.

Whether you use VBA or just formulas the key is to make sure there is a unique ID for Each Record so Excel can compare the lists.

By Example if COL A5 = Vin123 Col B5 = 5 and Col C5 = 2500
Excel cannot compare until you tell it what to look for in each list.
You could look for duplicates of just “VIN123” or Dupliates for just “2500” or Duplicates of “VIN123 5 2500” or duplicates of “VIN123 2500” So each list may need a formula to create the unique item you want to compare in each list.
The Excel Formula you will need is to check for duplicates is “ISNA(MATCH(D5,Range for other list unique ID,false))” going futher assusming the list unique ID is Sheet2!$D$5:$D$418 the formula becomes =ISNA(MATCH(D5,Sheet2!$D$5:$D$418,False)) then copy down as many rows as needed.

Again no matter what way you go Excel must have a unique ID.

Good Luck

Dear Ric:

Welcome to the lounge. Perhaps the following article can assist. In effect, you have excel find the Maximum, set this as its own chart value and make it part of the chart as a “point” rather than a line. If you needed to have as VBA, turn on the Macro Recorder, go throught the steps and review the Macro VBA lines to determine what your missing in the above code.
The article can be found atChandoo.org

Dear Carmine:

Something like this

Code:

Sub TestIt()
Application.EnableEvents = False
yyy = ActiveSheet.Range("C10").Value 'for this example ActiveSheet C10 has Folder Path
For Each sht In Worksheets
    sht.Activate
    ZZZ = sht.Name
    ActiveWorkbook.SaveAs Filename:= _
        yyy & ZZZ & ".cvs", FileFormat:=xlCSV, _
       CreateBackup:=False
   Next sht
   
Application.EnableEvents = True
End Sub

Hope this helps

Regards,

DuthieT

Dear Antmart2
Welcome to the Lounge

Attached please find a revised version of you Matrix Lite workbook.

I was not 100% certain what you were looking for concerning your “Ideal” solution so forgive me if this does not completely answer your request.

The attached now uses range names and revised formulas to acheive what I think you are looking for.

Some key items that I think are important.

Using the Excel Function “IFERROR” I have updated all the Vlookup formulas in the Workbook.
Your formula was IF Vlookup ISNA then -0- Else Vlookup
The problem is your makiing Excel do the Lookup function twice. Extensive use of these type of formulas can really slow down Excel. This is the reason Excel now includes the IFERROR function.

On the Holding1 Worksheet the formulas in many cells (see $c$3) are very long and appear overly complex. Again using such formulas in a large number of cells will slow down Excel.
In the workbook I have included new SumIf formulas to replace the original formulas. These are easier to read, understand, and edit. Plus they may speed up computation time.

Lasty, I have made use of Range Names. They make formulas easier to understand and by using the Name Box you can jump to the data rapidly.

If the attached is not what you expected please reply with a better explanation of the current method being used and the “Ideal” method you desire.

I have great confidence that someone in the Lounge can devise fantastic formulas (inlcuding arrays) that may provide a better answer once everyone understands your requirements.

Hope this helps.

Regards,

Tom Duthie

Welcome to the Lounge.

I have attached a possible solution that does not use a macro. This method should be more flexible than a macro approach especially if you keep adding new Worksheets. Just remember to create a new Range Name for any new Worksheets

First, I created a Range Name for each and every Worksheet with Data. by Example for worksheet 101 I created the Range Name “MySheet101” which is the Range $A$2 to $C$54. Of course you can expand the range so it will always be large enough to hold the informaiton you will need

Then on the main sheet I created two formulas.
In Col “L” Using information in Col A it creates the Range Name of the desired worksheet..
In Col D I placed a Vlookup formula that includes and Indirect Function. It will go out and find your information.

Lastly, at the top of Main, I put the formula as a label. That label can be copied and pasted anywhere you want and then later copied to D2 and L2 of Main. which can be turned back to real formulas by removing ‘ at beginning of the formula. Then copy those formulas as many times as needed.

Hope this helps.

I found this out on the Web perhaps this could solve the issue.

What I had to do was recreate the profile on the computer and also recreate a new roaming profile from the
server. This solved the issue. I think what might have originally caused it was going from each version 8, 9, and 10
back and forth too many times

Tom

One thought, when the User opens the spreadsheet does his Excel also load add-ins? This can cause issues.
Try to load Excel with no add-ins and see if they can now edit.

Alternative when the User has the spreadsheet open try this – open the VBA editor then open the immediate window. Type Application.EnableEvents = False then Enter, now have the User go back to Excel and see if the User can Edit.

Hope this helps.

TD

Quick suggestion. Open Excel on File Ribbon Select New Template. I went to “Monthly” and found may usable calendars. Find the one you want and download it to your local drive in the Templates Folder. Now whenever you need the Calendar just do File – New – Select and use. I selected the 2012 to 2019 calendar and pasted one month into my active workbook in less than 30 seconds.

Good Luck

Happy New Year to you.

You did not say which Excel your running so I hope its 10 or above.

After looking at the problem my recommendation is to abandon all the complex formulas. Instead use the power of advance filter to find and retrieve the information. To do this you will need to do the following

1. Create a range name for the grades worksheet large enough to include both current and future expansions
2. Create the Criteria for the data you want
3. Create the Exact Column names for the desired output
4. When running the Advance Filter make sure to select “Copy to another location.
5. Run the Filter and the results will be at the Copy Location Selected

Attached is an example, to run the advance filter. Make sure to type the range name “Groups” then check the box to copy to another location then run. Results will be there. Change the Criteria name to “Test 2” and run advance filter and see the new results

I think this may be better since everything is being run by “Criteria” that is part of excel which you can quickly change. Far more flexible.

Lastly, as long as the criteria is correct the results are values rather than complex formulas. This should keep the file from getting too large and allow for quick access to any data in the grades database.

Hope this helps.

TomD

See the attached.

Should work as long as the unneeded rows are blank or have the same words as the examples.

You can copy down the formulas to as may rows as you desire and tweak as needed

Hope this helps,

TD

No Macro Suggestion

What I would suggest is using Excel “Custom Views” This handy little item allows you to present not only the way a report is viewed on screen but also remembers all the filters and print settings.

In the particular case I would set up 3 Custom Views

1 Full View
2 Summary1
3 Summary2

Make sure you have the workbook exactly the way you want it to be viewed and printed and then give the view its name.

Laslty in a blank cell put an IF function that tests G3 and if Zero shows the message “Select Custom View Summary2 for this report.

Hope this helps.

Dear zmagic:

Rather than a Macro see the attached. This would have been a bit easier if the text showed the commas in the proper places for the totals. To get around this you will need to use find and replace to eliminate the commas from the text. Once that has been done I have put together various string formulas and created a table in the columns to the right of the data. Next I created a Pivot Table to summarize the information from the text file.

Hope this helps.

Tom D

Genej313:

A better way to tackle this project may be to segregate you links to be in their own area.

So in the Summary Workbook, you should put in a place below and to the right of the Summary links all the summary monthly informaion being provided by each workbook. That way your links show all available data. Next, in the Summary Report put in formulas that reference the cell where the data is now shown seperately in the Summary Workbook.

This allows you to use that information as needed by the Summary Report. Or any other future reports that may be needed. This way you keep things far more flexible.

See the simple illistration attached.

Hope this helps

Regards,

Tom D