1. W

    Get data from a separate workbook in a different location WITHOUT opening it

    I have an excel file that needs to reference another excel workbook that is in another location (directory) so file path needs to be included. I do not want to have to open this external workbook as it is in constant use by another user and it is a large file and slow to open and close etc. But...
  2. A

    Formula required for work - Lookup Matrix to provide result in cell

    Hi All, I have this assignment at work, and i tried bunch of formulas but cannot get this (hopefully) easy formula right, i have tried INDEX, MATCH, COLUMN, COUNTIFS, SUMPRODUCT but all to no avail; i cannot comprehend the complexity and turn it into simple logic. In the image attached, i have...
  3. B

    Excel closes wrong instance

    Sometimes when I have two excel workbooks open, If I click the cross on one to close it, the wrong workbook closes without saving. This even happens when the excel closed is on a different screen to the one I have closed. Any ideas what could cause this? Many thanks!!
  4. M

    Best way eliminate rows with multiple status from a count?

    I receive data from our ERP system on vouchers that create errors. I want to have a measure that tells me how many voucher lines were closed within the last week. But... a voucher can create multiple errors - one might be closed during that week, but if the same line has an open error I don't...
  5. S

    Formatting Inconsistent Source Data

    I'm trying to solve a problem to automate the formatting of some inconsistent source data we download daily for a partner. The source data is formatted like below. For the all lines we need to pull data into a simplified table. Ex. first line we need to pull the invoice # (11753195), Vendor...
  6. B

    What does @before a function do in new Excel

    I had a popup to explain this change in my formulas but accidentally closed it before reading it... Can someone point me towards an explanation? Example =@IFS(AVERAGE(C14:E14)>=90,"A",A...
  7. gheyman

    Access: Keep Navigation pane closed

    Is there a way to keep the navigation pane closed or keep users from accessing it without proper permissions?
  8. S

    VBA auto email

    Hi all, We have a tracker for stock waiting to be booked in. When our warehouse has booked in the stock, they enter 'Closed' into a particular cell, add a comment, and the line turns blue. How can I set the spreadsheet up so when the warehouse enter 'Closed', an email is created with the...
  9. willow1985

    Drop down box value to trigger macro

    I have drop down lists in column O where you can toggle the status from "blank", to "open" to "closed". What I would like to do is have a certain macro run every time the status is changed in any of the cells in column O. the following vba code that I currently have (that I inserted under...
  10. C

    VBA: Copy data from closed Workbook and paste

    Hi I am using this code to copy data from a closed workbook: Sub GetDataFromClosedBook() Dim mydata As String 'data location & range to copy mydata = "='F:\filepath\filename.xlsm'!NamedRange" '<< change as required 'link to worksheet With...
  11. Z

    Sumifs does not work when a workbook is closed

    =SUMMEWENNS('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$C$2:$C$1703;'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$J$2:$J$1703;B19;'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!$A$2:$A$1703;F6) as well all know whenever i close the other workbook it gives #Value, can anyone help me to translate it...
  12. G

    Merge different workbooks into one workbook. Setting order

    Hi all, I hope you could help me with this. I have 3 closed WB in a folder, in the following terms: A) WB "A". With one sheet called "A" B) WB "B". With one sheet called "B" C) WB "C". With one sheet called "C" I have another workbook, called "SUMMARY", in the same folder. What I need is to...
  13. T

    Find closed date and next one open

    Fellas, I have 2000 service order by VIN's in column A (sheet1) with different closed dates (example, VIN# 111 service order completed on 1 Jun2019. In the sheet2 I need to find if VIN 111 when is next service order open after 1 Jun or next one (see example below: sheet1: <tbody> a b VIN...
  14. P

    Conditional formatting question on multiple rows

    How do I use the conditional formatting option for "Use Formula" option on a per row basis? In my example I have a status field as Closed, once the status is set to Closed it will Strike Through all the text in the next 5 columns in that row. How do I drag this Conditional Formatting...
  15. H

    Copy Row based on Cell Value, Paste to Sheet based on location

    Hi - i have data on sheet1 from A5 to O200. This data is divided at various points with subtotals. Right now i use 3 different versions of the same code to move a row to another sheet when Cloumn J = "Closed" the code for this is below. Is there a way to use only 1 code and move it to Sheet2...
  16. M

    Convert SUMIF formula to SUMPRODUCT as SUMIF doesnt work in closed workbook reference

    Hello, Based on my research on this forum, I've learn't that SUMIFS formula does not work when the source data workbook is closed, once the recepient workbook is refreshed the values go to #VALUE error. I've read that the SUMPRODUCT formula can be manipulated to achieve the same result and...
  17. T

    Pivot Table Dates

    I have a dataset that contains a list of items with a unique id, create date, and close date. How do I get a pivot table to have dates by day in the left most column, a count of records created in that day in the second column, a count of records closed in the third column, and the difference in...
  18. D

    SUMIFS Closed Workbook Workaround with Multiple Criteria Array

    Hello, I am having trouble figuring out a way to make this formula work when the source workbook is closed: =SUM(SUMIFS('Workbook A.xlsx]Sheet1'!$E$2:$E$2000,'[Workbook A.xlsx]Sheet1'!$B$2:$B$2000,$A2,'[Workbook A.xlsx]Sheet1'!$D$2:$D$2000,{"Apples","Oranges"})) This formula is working fine...
  19. O

    VBA Code for Data from a Closed Workbook

    How can I get data from a closed MS Excel 2019 Workbook into an open MS Excel 2010 Workbook? Had they both been the same version I could just import the sheet. Thanks in advance for any suggestions as I will provide feedback. There are many posts with the same solution I'm using, but I need to...
  20. L

    Write to closed workbook, Same time as opened workbook.

    Good afternoon all, For more than 3 years I have been happily entering data into an Excel based database and everything works rather well. However, I need a little advice on a problem I have not got a solution for. All data is entered by way of a multi-tabbed userform, I would also like the data...

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
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 "".
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