Search results

1. Find the lesser closest date by given date and then multiply the intersect values.

Hi I need a VBA code to get the below results. In the attached workbook Sheet1, Cell D9 is the given date and the cell B12: H12 is the data range Sheet2 A2: A15 is the list of dates where we need to lookup by VBA code In the list of dates in Sheet2, none is matching Sheet1, D9 dates of...
2. How to get data between two dates in vba

Hi The below formula I tried to convert VBA code but I struck how I can tell VBA that between two dates and only in sales data to be extracted. IF(P1=EN2,(SUMPRODUCT((J8:J30495<=EN1)*(J8:J30495>=EO1)*(G8:G30495="Sales")*CF8:CF30495)) Cell J8: J30495 is sales date Cell G8: G30495 is having...
3. Sort data by date

Hi The attached sheet having Sheets of Formulation and Batch card with VBA code which is giving desired results from the sheet "Formulation" according to the C4 and F4 cells value in the sheet "Batch Card". In the cell E3, I have a Date and also in the sheet formulation Column A, I have a...
4. How to understand each line of VBA code

Hi The attached sheet has a below VBA code to calculate the expected results. Here I want to understand each line of the code what it does and where it is looking the data. Because I want to rewrite the code according to my other requirements which are the similar worksheet or nearby same...
5. VB Code required for one cell with multiple results

In the attached sheet, I got multiple results in the sheet COA and cell B8, B10 and B16. To get the results I have a helper column in the sheet Master Data Column M, N and O. How can I avoid those helper columns to get the results in the sheet COA and cell B8, B10 and B16 as like now...
6. How to find what formula uses more memory in excel

Hi I have Excel workbook with a memory of 8MB which is having normal formulation, array formulation and macro codes. I saved the same file in binary format then it is reduced to 5MB. I want to determine which formula is taking more memory or what are all taking memories in the sheet. Can...
7. Find highest value between given range

Hi I have numbers from 1 to 100, 1000 to 3000, and 4000 to 5000 in column B. I need the highest value from the number 1000 to 3000 in cell C5. Expected answer in cell C5 is 2999. Please find the attached sheet. Can anyone help me out? Thanks in Advance.
8. Run recorded macro only in recent modified rows

Hi In the attached excel sheet, I have recorded the macro to run the calculation steps as below. Macro Key is Ctrl + g Step 1 - Copy formulas from S2 to DK2 Step 2 - Paste it to S8 to DK32 Step 3 - click F9 (Manual Calculation) Step 4 - Copy S8 to DK32 Step 5 - Paste values in S8 to DK32...
9. How to reduce heap of formulas in a sheet

Dear Excel Ninjas Please find the attached sheet with the formulation which I'm using to maintain our company details since 2015 to till date. The formulas from S4 to DK4 and it is continued up to S9600 to DK 9600. This excel sheet is getting stuck frequently with these heap of formulas. Is...
10. Multiple Numbers in one cell

Hi In the attached sheet, cell A10 to A19 I have a value of 10080 to 10089 and the cell A20 I would like to add 10090&10091 and the cell A21 10092&10093&10094 In cell A9 I have used =MAX(A10:A20). My desired result is 10094. It means I want to add more than one value in a single cell also it...
11. Pivot table calculation field results

hi Please find the attached copy of excel sheet with pivot table data which is showing two different % of revenue from the same data source and value. Can you check How it's calculated the both value and guide me to get the correct answer?
12. Subtotal, Index & match

Hi As per attached sheet cell Q3 i have entered below formula and i got a result is 174 instead of 334. This wrong value due to Column F sales date is not in A-Z order. =SUBTOTAL(109,S11:INDEX(S11:S2000,MATCH(G2,F11:F2000,1))) Please do the needful to get the right answer. Thanks in Advance!
13. how to make single graph view by quarter and month?

Hi, As usual i have my company sales quantity by month wise in graph. It's from Jan'2013 to Nov'2016. Totally 47 months. I need a graph with view of quarter division except current month I would like to make a graph view in quarterly from Jan'2013 to Dec'2015 and month view to Jan'2016 to...
14. Need IF formula, subtotal result upto given date

Hi As per the attached sheet B1 cell drop down list, i got result in B3 cell by using If condition and subtotal formula. But i need subtotal result upto given date in I2 cell. At present i got a value of 2176 in cell B3, but i need 485.5 in B3 cell. Please guide me & Thanks in advance...
15. Sumproduct or choose

Hi As per attached sheet i've Permanent and temporary formulations in two separate sheets. In (3rd sheet) Master sheet A4 cell i've product name. as per the product name i've got formulation by using =SUMPRODUCT((Permanent!\$A\$3:\$A\$14='Master sheet'!\$A4)*(Permanent!\$B\$2:\$K\$2='Master...
16. To get raw materials consumption, sales and profit by using Power pivot

hi, Please find the attached copy of Power Pivot 1 which is having production, Sales, products formulation, raw materials price and raw materials receipt. i have producing 4 types of products by using 4 types of raw materials and selling to different customers by different price. I need a...
17. Find the missing number

Hi, As per below pasted details, I've heading of Sales / Free sample in column B and Invoice Number / SR No. in column C. Sales have separate invoice series numbers and free sample have separate SR No. in Column C. I want to find out the missing number in the column C, Please find the attached...
18. Count, Index, match and Select

Hi, I have 12 no of products with different formulation and the formulation by 12 no of Raw Materials, each raw materials have unique code. In attachment sheet name Formulation you will find the same. I have to prepare the batch card as in the attached sheet name Batch Card. If i type the...
19. Sales value comparing to last month in chart

Hi, I've attached the sheet with Pivot chart showing that each month sales quantity. I need all previous months sales value depend by current month value up to today. For example today is 15-Mar-2016 and the sales value is 150. So in graph all previous months should show the value of sales up...
20. TEXT & Vlookup help

Hi, As per the below data, D column Batch Number and B column Date. by using the formulation in B, B1 '=TEXT(VLOOKUP(A1,A:E,2,1),"dd-mmm-yyyy") then the result is 00-Jan-1900. But my expected result is 19-Mar-15. Note that in column D Batch Number two cells have a same value which is the...
21. Sumproduct or Vlookup Help!

Hi Please find the attached file. In that file Batch number sheet i've used sumproduct formulation to get the no of batch number from master data sheet. i got the answer is 4 by counting the cells, but i need to get the values from that 4 cells (AB24, AB25, AB26, AB27). My Input will be in...
22. Pivot Chart

Hi, In the attached sheet, i've made pivot table for production quantity and employee working hours by month wise, I would like to make a single graph with the details of, % of production increasing vs % of employee working hours as comparing to previous month for each month. % Value should...
23. Need pivot chart help

Hi, In the attached sheet, i've made pivot table for production quantity and employee working hours by month wise, I would like to make a single graph with the details of, % of production increasing vs % of employee working hours as comparing to previous month for each month. % Value should...
24. Pivot Table Help

Hi, As per attached sheet i got my products stock as on today but i would like to know the stock of product in specific earlier date. Please guide me to get the product stock on 01-May-2015 in any of the sheet like master data or pivot table. Thanks in advance.
25. Filter data for various output result..

Hi, If i filter some data using data filter option (Row 6), can i get different output in the words as well as values in top of the sheet (any of row 1 to 4) Can any one give me a model excel sheet for getting various results by using data filter..