1. E

    VBA code converting cell to Table

    I used this same code in another worksheet but with different ranges without issue. Now when I run the same code it causes the first cell under QTY to become a table. Any idea what would cause this? I did find if I rem out " srcWS.Range("AD4:AD52").AutoFilter Field:=1, Criteria1:="<>" "...
  2. V

    Select, Copy, Paste one by one till last data to new format

    Hi Excel Experts, Myself Srini, new to this forum. I need your help to provide a macro code for the below Input & Output file image links are as below Input File Image-->https://ibb.co/xLQ6dLD Output File Image--> https://ibb.co/f0VMv9c In Input, Rows & Columns can be n numbers. Need to...
  3. X

    How To Populate Date Conditinally?

    Dear All, I do have the following sample data set across A1:F4. <colgroup><col span="4"><col><col></colgroup><tbody> Site SKU Qty Date Maximum Qty Maximum Qty Date a 1 10 01-Oct 35 10-10-2019 a 1 35 10-Oct 35 10-10-2019 a 1 25 24-Oct 35 10-10-2019 </tbody> In column E the formulae is...
  4. S

    Pivot table showing recurring values only once

    Hi, I have an excel sheet given below. The pivot table shows the "Amount" portion correctly but in the "Qty" portion, values which are repeated are shown only once. How to show all values in Rows?? Excel 2007 32 bit ABCDEFGHIJ 1Sum of AmountColumn LabelsComm.L / ULQtyRateAmount 2Row...
  5. I

    Copy columns based on header and array

    Hello, I have data in Sheet1 that I'd like to copy into Sheet2 based on the header. I'd like to create some kind of text array that I can update in the code to include new columns if they become relevant. I'd like the relevant headers array to be something like sWords = {"Date", "Qty"...
  6. V

    Complex function

    Good afternoon Dear All, Firtsly i would like to apologize if my question contains any misunderstandings. I have a workbook where there are many sheets with many informations but i want to take a specific value from all those sheets. Imagine that there are lists of products per category in...
  7. P

    Bills of Materials - Total Quantities required Help

    Hello all, I need to create a summerised parts list from a Bill of Material, ensuring that all items are properly accounted for as the BOM is multi-leveled. A sample of the data: - <tbody> Item Level Part Number REV Qty Per Qty Total 0 0 HD IBC 7500 F50T 2 2 1 1 FHF57 AM132/M 01 1 2 2...
  8. T

    Copy data on sheet2 to sheet1 base on column data.

    Please help me out. I want a vba that can automate this process that copy sheet2 to sheet1. For example on sheet2. Column D4 fruit column separate by a blank line with a new fruit with dates, purchase orders and quantity. There's six types of fruit that I need to keep track. What I want to do...
  9. N

    VBA Insert a formula in a cell

    Hello, I have a userform to add items to sheet1 and I'm using the code below, and inserting manually formulas to columns ("C" "E" "F" "G") Private Sub cmdAdd_Click()Dim ws As Worksheet Dim MsgBoxResult As Long Set ws = Sheet1 nr = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1 ws.Cells(nr, 1) =...
  10. D

    VBA Loop through column, count multiple different values and output total of each in a cell

    Hello, I'm quite awful at VBA and am trying to make a macro that does this: <tbody> E F G H I Item# Item Name Qty Total Tracking 1000 xxxx 2 xxxx 1000 xxxx 1 3 xxxx 2000 xxxx 1 1 xxxx </tbody> For this format, with the letters on top being default excel columns, I am trying to have...
  11. MUKESHY12390

    Vlookup ---> then QTY Extact match or Greater Qty

    Hi All I'm trying to build formula to lookup one column (Product) (has Duplicate value in Table Array) and if "Product" matches with lookup table ("A:B") then ---> it should look for Exact Qty or Greater Qty (column B) but the first preference is "Exact match" for reference I have uploaded...
  12. A

    Help Calculating Average Cost using a Vlookup?

    Hi, I am hoping someone can help! I am trying to set up a spreadsheet for an online business that updates the 'cost of purchase' depending on which stock order has been sold. For example, if I order 10 units of soap at $10 then the cost is $10. If I make a second order of 10 soaps at $12...
  13. C

    IF Statement - brain fog!

    Good morning everyone, can anyone help please as Im having a Real brain fog day! My if statement is =IF(G4=E4,F4,IF(G4>E4,SUM(C4-D4*G4))) which is not returning the value expected and I cannot work out where I have gone wrong. Column info C=price 1, D=price 2,E = sum of qty received 1...
  14. L

    SUMIF/SUMIFS with multiple criteria

    Hi, Im stuck with a formula can anyone help? I want to sum the bottom row in the image below when two criteria is met, which is 'A' and 'Qty' The problem is the sum range needs to be the bottom row only(figures). The criteria 1 (A,B,C) is a merged cell if that makes any difference! Any ideas...
  15. B

    Looking for a lookup formula

    Hi, I'm trying to match actual orders to budgeted orders. Column A: Part number Column B: Actual Order Qty Column C: Display Part Number Most of should be the same as part number in column A, but not all of them map. Column D: Budgeted Order Qty I would like to add a column to show for...
  16. T

    HELP with INDEX formula. Need IMMEDIATELY

    I created a table to record ordering items, and have 2 columns (a Date, and a Qty) for each week of the year, per quarter, to record the Date Ordered and Quantity Ordered. I have 2 columns (at the end of each quarter) which I want the Last Date Ordered to appear in one column, and Last QTY...
  17. gheyman

    Forecasting Linear?

    If I have two Quantities and two Unit Prices, is the a formula that will forecast another qty? Qty 100 = $25.00 Qty 500 = 10.50 Is there a formula that would forecast Qty 300 or any other quantity I enter that is between 100 and 500 (Using a straight line)? Thanks
  18. C

    Explode a list based on a quantity

    I need some help from the experts please. I have a list of materials, which varies in length by day. I need to explode this list into individual line items that repeat based on Qty, kind of like a reverse Pivot Table. This list is typically hundreds of line items per day. For example...
  19. M

    Conditional Formatting On Multiple Ranges

    Hi, Can you please help me with conditional formatting (highlight both cells) as with the below example to highlight the name "Mary" in the "NAME and "Qty" Columns on ranges A3:B7, C3:D7, E3:F7. I'm new here and its my first ever post,Thank You in advance. <tbody> SHIFT 1 SHIFT 2 SHIFT 3...
  20. O

    SUM and VLOOKUP (I think!)

    Hi there, I have a sales sheet that I use VLOOKUP for to return an item value for SKU which met my needs until now. The problem that I have is that the current VLOOKUP formula that I have at present will only work out the value of one item as it only references the SKU and performs the VLOOKUP...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back