Search results

Hi Narayan, Thank you very much. Nice explanation, I got it. Pablo

Hi Narayan, Thanks for such a quick response. Impressive. It works great. Could you please explain why do I need to use Transpose and how does it work? Also, why I can't use cell reference inside the curve brackets? {...} Thanks again. Pablo

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table Name", CSV Filename

Hello, I have a report for different units, and based on a list or criteria, I need to add the values for only those units. How can I get a formula that will work with a regular list (no empty rows to match the rows)? I got it to work with an array formula, "SUM(IF(...", but only when the...

Thanks Narayan for your help, it's much appreciated. I did come across the 1st and 3rd links that you suggest and it's hard to apply those concepts to my model, but I will try again. I will try the other 2 as well. Thank you. Pablo

Hi, If you attach the file it will be easier to help you, but based on my own experience it will be better if you test the filter results 1st, something like this: (insert after each Range("B3").Select) If Not IsEmpty(Range("B3")) Then Range(Selection...

Hello, I found the link to the original post, where you will find more details, and the VBA UDF, which is a better solution. http://blog.contextures.com/archives/2009/03/24/convert-number-to-words-in-excel/ Cheers,

Dhaka, Try this file I found in the Internet some time ago, no VBA, just a long formula which you can customize to get what you need. Just study the name formulas described on the 1st tab. Pablo

Hi Narayan, Thanks for getting back. Here are the answers: 1. Each one of the 4 tabs is a data table and each get populated by different people across the country. The files are saved in Sharepoint and then I collect the data in one workbook and put the report together (see Report tab). In...

Any help?

Hi SirJB7, Thanks for your reply. Here is the file. I made a sample file with all the info for 4 items. When the actual is positive the formula works, but when it's 0 or negative I need to add the prior period Projected on Hand, which in regular Excel is very simple, see the report tab, but with...

I love to learn new Excel techniques and apply them to my job, specially when I can help others to be more efficient.

Hello, I am creating a PowerPivot report for supplies consumption, which contains: Beginning Inventory Forecasted Demand Actual Demand Plan Receipt and the measure is: [Projected on Hand] = If([Actual Demand]>0, [Beginning Inventory] + [Plan Receipt] - [Actual Demand], [Plan Receipt] -...

Hui, Thanks for following up. Unfortunately I have no access to any of those sites (Internet access restrictions), but I pasted a small sample right below, you can copy and paste this in Excel. ccenter itemid sc_debit_gl sc_credit_gl alt_descr total_value MTH 6153508 163226 610000 130150...

Hi all, I have an inventory count list of few thousand items. The fields are: location, item number, description, quantity and date. In the same file I have data from June and July. By using a Calculated Field in a Pivot Table, how do I calculate the inventory change from June to July (the...

Hi Luke, Excellent suggestion, thanks. I added a calculation field to the pivot table and then extracted the data that I need by using GETPIVOTDATA and the results are impressive, everything works very fast. Now I don't need to have the spreadsheet in manual calculation mode anymore and I...

Hello Chandoo and Excel community, I am currently using “SUMIFS” to compile data using 3 different criteria (Business Unit, Account and Department). The formula covers over 1,000 lines for different results. The raw data that I am pulling from has 40,000 lines. I have set up named ranges and...

Hi Luke, Thanks for your help. It did work, very nice solution.

Hi Aklewe, It works fine for me. The macro should run only when you change the cell, not when you select it. You said any change, but if you need a validation, set it up 1st, to do that: - Create a list anywhere in the sheet - It's better to name that list - Click Data on the Ribbon, then...

Thanks Luke, I will use your recommendations and let you know how it goes. I never thought mixing SUMPRODUCT and TEXT.

Hi all, I need to track severance payments, which are paid every other Friday (bi-weekly). My schedule (reconciliation) is done on a monthly basis, so I track the monthly activity to the accounting system (ledger). Now the problem I have is how do I estimate the right number of payments each...

Hi, Here is the code. Just make sure you save it in the actual sheet where you have the data, NOT as a module. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$4" Then Application.ScreenUpdating = False Range("C8:C120").Select For Each cell In Selection If cell...