[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Excel

135K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I need assistance in writing the following formula in VBA

=IFERROR(VLOOKUP(data_lookup!$A$1,CHOOSE({1,2},'Sales'!$G:$G,'Sales'!$B:$B),2,0),"")

Please help
0
Bootstrap 4: Exploring New Features
LVL 12
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Hi,

I have a table of 2 columns: Orders and Operations.
An order normally starts with an operation 0010 and if needed, an operation 2 is added with the code 0020 and so on.
IE1: If an order needs 5 operations, we would have: Operation 0010, 0020, 0030, 0040 and 0050.

However, it is possible for someone to delete one of the operation.
IE2: If we take IE and someone deletes the 0010 and 0020, the order would only have the operation 0030, 0040 and 0050.

Is it possible to extract all orders that have deleted operation and all the deleted operations?
0
need add an selector with date of high

a55555.PNG
to get this

a888.PNG29121680--2-_4N_to_3N_selector.xlsm
0
I've exported an excel spreadsheet to html and uploaded the file to my wordpress site however when I open the url the spreadsheet is not centered on the page. it is set to the far left. How do I center the pages. I'd also like to have the links on the bottom to the other sheets be at the top instead of the bottom but that's not a deal breaker.
0
I am trying to embed some content from an excel worksheet into a word document so that when the worksheet is updated so is the word document. It works fine when the files are stored locally on my computer however when I try and do the same thing with files on the file server I get an error "Word cannot obtain the data for the
{00020830-0000-0000-C000-000000000046} link." I am trying to get this figured out but have hit a dead end.

Additionally, the issue seems to be limited to Windows 10 as Server 2012 and Windows 7 don't appear to have the same problem.
Screen.png
0
Hi, a user is having an issue with Excel where many times she opens an Excel file it has the filename:2 (on the Excel bar) on top instead of just the filename...and when he tries changing it to a different view it opens up another Excel windows with filename:3 on top...doesn't happen everytime but it does happen to most different excel files I have tried.  

Suggestions?
0
How can I add a pivot table to my list of recommended pivot tables? Let's say I basically create the same pivot table every week - different data, same structure. How can I add it to my list of recommended pivot tables to make it easier to create? Thanks . Using Excel 2016
0
I have data in 'Sheet1' Cells D100:AA:10000.
What VBA do I need that does the following:

1) Assign this data to an SQL string sorted by column E.
2) Paste the results on sheet 2.
0
Hello,

I'm trying to combine match with indirect so that it sums up the entire column once it matches a text value in a particular but I've been having trouble getting it to work.

Help would be greatly appreciated.

Thanks.

=SUM((INDIRECT(MATCH(B250,5:5,0)&":"&MATCH(B250,5:5,0),TRUE)))

Open in new window

0
is there an easy way in excel to use a column in a separate sheet of data as a data filter criteria. I have a sheet of data (sheet 1 with over 250k rows) and one of the columns is recipient-address (which represents an email address). I want to filter the data in sheet 1 recipient-address column to only show rows for certain email addresses, which are those which I have saved on another sheet (sheet 2) column A (about 70 email addresses). What is the best way to approach this?
0
Introduction to R
LVL 12
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Hi Experts,
Wondering if there is a way to link an Access 2003 application to an Excel XLSX file?
if not, can someone post code to convert file from XLSX to XLS?
Thanks
0
i created a document in excel and cannot find the document.  I have looked in the recent documents, unsaved documents and still cannot find it.  Can you provide me way to find the document? I am using windows 10.  Thanks.
0
Hello experts. Here is my issue. We have on our sharepoint site an InfoPath form for quotes and projects. Based on the information to the InfoPath form, we then have an excel spreadsheet with a command button that pulls in this data along with a couple of other databases and does a bunch of what I will call data crunching. Here is my issue. Lets say in my quote a project log I created a test record and setup a ceiling dollar value for $6,000 for a period of 1/1/2019 - 12/31/2023 (five years). We do some costing for labor and material weighting so when running the command button on the spread sheet the expected result should be $100 per month over five years. However, as you can see per my attachments I am only receiving four years of data. The spreadsheet also has many other fields of data being pulled in but I am omitting as it does not apply to my issue. So my question is not knowing a whole lot of vba, why is it not pulling in all the years of data and where do I go in the code to correct this? I attached the code for the macros that get kicked off per the command button leaving out any connection string info. I hope this makes sense. Any help is greatly appreciated.
macros.docx
results.docx
0
I am currently using the following LOOKUP formula in spreadsheet EE_Pub (columns N, O and P) to find 'last match' values in spreadsheet EE_Cat (columns J, K and L), unless the value in column K of EE_Cat spreadsheet = "No Change" or "CX'd" in which case it ignores these rows.

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

I need to update the lookup formula to incorporate additional conditions:

If Column L of EE_Cat spreadsheet (Stage_4) column is blank, use the current lookup formula that finds last match:

=IFERROR(LOOKUP(2,1/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$I$2:$I$1200=$M2)/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"CX'd")/('C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$K$2:$K$1200<>"No Change"),'C:\Temp\[EE_Cat_Test.xlsx]Sheet1'!$J$2:$J$1200),"")

If Column L of EE_Cat spreadsheet (Stage_4) column contains a date, then lookup formula needs to change from last match, to row that has the latest date in EE_Cat spreadsheet (Stage_4) column (based on Column M value in EE_Pub-Test spreadsheet as per the current lookup formula).

Note that Column L is always formatted as: AA–08 Jan 16  13:02

I have provided the two sample spreadsheets. I have highlighted the cells and included comments on the EE_Pub spreadsheet containing the results I am looking for with the …
0
I want to extract the product description from a 10-k report for my master thesis (new at programming, finance background). This product description is between "ITEM 1" and "ITEM 2" from the reports. What I did until now is to download all the 10-ks in .txt form, remove html tags and make all text uppercase. My problem is now when I try to select the text I need and save it into another directory. I tried doing the selection on my own, but with unsatisfactory results. Currently, I am using a code made by a guy "iammrhelo" on GitHub. His code is for selecting "ITEM 7" to "ITEM 8". With a bit of tweaking, made it search for what I need. Link to his code: https://github.com/iammrhelo/edgar-10k-mda
The code is able to idetify this:
the code is able to identify thisBut the code is not able to identify this:
the code is not able to identify thisMy problem is now that the parsing he does not work for all 10-ks. To give a little context, I need to find the right syntax that the code has to look for. The words that is looking for are in the list item1_begins variable. The code I am using to select the text, is the following:
import argparse
import codecs
import os
import time
import re

from pathos.pools import ProcessPool
from pathos.helpers import cpu_count

class MDAParser(object):
    def __init__(self):
        pass

    def extract(self, txt_dir, mda_dir, parsing_log):
        self.txt_dir = txt_dir
        if not os.path.exists(txt_dir):
            os.makedirs(txt_dir)

        self.mda_dir

Open in new window

0
My Windows 10 laptop has an extension screen on its right hand side.

MyProject xl file should position a microsoft visio window precisely & dynamically over part of the xl Main window. The visio file is made into a child of the excel file, so whenever the xl file is moved around, the visio window moves along, ontop. This Parent-child relationship is stable and the visio window CANNOT move independently of the xl window.

I use the API and VBA code below to position the Visio window ontop of the xl window.

When I dynamically 'drag' MyProject xl window to change its dimensions, the xlWorkbookEvent: Private Sub Workbook_WindowResize(ByVal Wn As Window) calls Sub window_vis_setSize() [below] which recalculates the position of the visio window.

SUCCESS: This works perfectly WHEN MyProject xl is on my extension screen.
My PROBLEM: when MyProject xl (plus visio window) is dragged onto the laptop monitor, win10 automatically automatically shrinks the windows to fit the smaller laptop monitor.
However, the resizing calculation for the visio window overcompensates the required reduction in its size, making the visio window SHRINK relative to the Xl window, and to position inaccurately within the xl window.
But the PROBLEM is REVERSIBLE: When MyProject xl (plus visio window) is dragged FROM the laptop monitor ONTO the Extension monitor, the visio window reSizes & positions perfectly onto the xl window.

Please can you tell me/ point me toward solutions to this effect …
0
I have a java program which will connect to DB and write into an excel sheet.

Now I would like to make each sheet in workbook as a table and print in outlook and sent via mail also attaching the mail excel sheet using java program.

Any thoughts
0
Plz see The sample file i need a fomula
instructions.pngPlz have a look
FORMULA.xlsx
0
Excel - three columns.  

Write something in column C & press enter / tab, etc.  As soon as the data is added to cell in Column C corresponding cells in Columns B & A auto fill with Date & Time.

So that, if I added - ordered cotton fabric to cell C2 I'd get

(A2) 10/6/2018  (B2) 3:55pm  (C2) ordered cotton fabric

Any way to achieve this?

Many thanks!

OT
0
Fundamentals of JavaScript
LVL 12
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Hi All,

How to copy some rows of data from excel sheet to new excel sheet in mac using java and if possible throw mail with the attachment of new excel sheet?

Note: Mac office 2011

 Thanks.
0
How to edit 2 cell fields in multiple excel files in a folder/subfolders automatically using VBA based on starting name of file

For example I have a root folder of c:\master templates
Under that folder I have subfolders with clients names as the folders
Under each client folder I have folders with job numbers
Under those job number folders are the excel files that I want to change
They all start with the name of set_up sheet *
There could be 2 set_up sheet excel files or 3 or 5, it all varies based on client and job, but they are all named the same starting with "set_up sheet*"
I want to change 2 cells in each of these set_up sheet*.xls files for every directory/subdirectory that contains those files
Basically replace 2 fields
M14 with "Stick Out"
S14 with "Vending #"
Then I want to save each excel file back and close and then loop through and do the same thing for all the other excel files named the same thing in each directory/sub

Here is the code I have so far which I know isn't correct. It's just what I wrote by watching a youtube video on it. I know I am on the right track I just need some help

Sub LoopThroughDirectorytoedit()
Dim MyFile As String
Dim Filepath As String
Dim q As Long

Filepath = "C:\mastertemplates\"

MyFile = Dir(Filepath)

Do While Len(MyFile) > 0
If MyFile = "bookz.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
For q = 1 To Application.Worksheets.Count
Worksheets(q).Activate
Range("M14") = "Stick …
0
I have been asking Microsoft if there is a way to get a list from the accelerator keys table for Excel.

These are keys to access menus - Alt,F,S - in addition to the shortcut keys like Ctrl+S.

I have posted requests but haven't gotten any response.

https://docs.microsoft.com/en-us/windows/desktop/learnwin32/accelerator-tables

Defining an Accelerator Table
An accelerator table is a table of keyboard shortcuts. Each shortcut is defined by:

A numeric identifier. This number identifies the application command that will be invoked by the shortcut.
The ASCII character or virtual-key code of the shortcut.
Optional modifier keys: ALT, SHIFT, or CTRL.
The accelerator table itself has a numeric identifier, which identifies the table in the list of application resources. Let's create an accelerator table for a simple drawing program. This program will have two modes, draw mode and selection mode. In draw mode, the user can draw shapes. In selection mode, the user can select shapes. For this program, we would like to define the following keyboard shortcuts.
0
I have this formula

=SUMPRODUCT((1-ISERROR($M$2:$M$23))/COUNTIF($M$2:$M$23,$M$2:$M$23&""))

it excludes the count those cell that has Errors.

I want include two more conditions that do not count if the cell is empty and do not count If the cell has text which is simply a space  like this  " "
0
Hi - We have an excel 2016 spreadsheet that we use as a product list/price sheet, that is about 5mb in size. When we open it and do copy/pastes or what seems to be small modifications, it either crashes or freezes. We are accessing it on a terminal server/Server 2016 that has 4 Xeon processors/25 gb RAM. When this Excel file is open and changes are being made, it utilizes 15-20% of the 4 processors. I've turned off hardware acceleration in Excel (because I've heard this can cause issues), it helped for a little while, but it still is an issue. Any help with this would be much appreciated.
0
Any alternative to this formula to extract unique values using formula?

currently, I use this =IF($E2>$D$2,"",INDEX(HelperProject_Id,MATCH(SUM(COUNTIF(HelperProject_Id,F$1:F1)),COUNTIF(HelperProject_Id,"<" &HelperProject_Id),0)))

it works but it is too slow.

I am wondering if anyone knows how to use a different approach using formula to be faster.
0

Microsoft Excel

135K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.