Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Microsoft Excel

131K

Solutions

37K

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 have this information on another sheet and all his monthly payments to pay of $100 per month to pay the$4000   are entered on this another sheet.How can I link the two sheets so then I can automatically have the balance showing on the first sheet.Please explain clearly and give me the best and shortest excel method
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE
LVL 4
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

I tried this substitute formula to replace the decimal separator (. to a ,), but it does not work:

=SUBSTITUTE(H8/-('Innestående; totalt förvaltat'!G2+'Innestående; totalt förvaltat'!K2+'Innestående; totalt förvaltat'!O2+'Innestående; totalt förvaltat'!S2+'Innestående; totalt förvaltat'!W2+'Innestående; totalt förvaltat'!AA2+'Innestående; totalt förvaltat'!AE2+'Innestående; totalt förvaltat'!AI2+'Innestående; totalt förvaltat'!AM2+'Innestående; totalt förvaltat'!AQ2+'Innestående; totalt förvaltat'!AU2+'Innestående; totalt förvaltat'!AY2+'Innestående; totalt förvaltat'!A17);".";",")

(I use semicolon instead of comma for separating elements in a formula.)

All the other part of my formula is correct, it's only when I add the substitute part and the last part with . and , that it gets completely wrong result.

How should I change it?
0
In Excel 2007, how do I get the minus sign entered immediately before the number? Can I custom format like that?
0
Hello,

I do IT for a health screening company.  Basically a data review person will download a list of people who participated in a health screening event.  This file lists all the people that attended and their personal information.  This file name changes.

The second file is almost identical but is generated by our health screeners from our own system.  This file name also changes.

We do a comparison between these two files and I'm trying to create a macro that will work no matter what the file names are.  I'm not a programmer at all, macros are easy, but above that I just don't have that skill set.

So the two files would be open Book1.xls and Book2.xls
In both file we're working with Last Name, First Name, Unique ID columns, but there are many more columns present.

Book1.xls
Add column to right of Unique ID (say Column "J" for this example)
Concatenate Last Name, First Name, Unique ID Columns

Book2.xls
Add two columns to the right of Unique ID (say Columns "I" and "J" for this example)
In first column, Concatenate Last Name, First Name, Unique ID Columns (same as in Book1)
In second column, Vlookup on first cell to the left which is the concatenated value, then highlight Column "J" in Book1.xls, then add comma 1, then add false.

=VLOOKUP(E2,[Book2.xlsx]Sheet1!$J:$J,1,FALSE)

It's just comparing the columns for accuracy.
I can make macro work, but then of course the file names change and that's the end of the story.

Just trying to make …
0
Hi,

Whats the easiest way to calculate the date of each of the following for any given year...

 New Year's Day
 Good Friday
 Easter Bank Holiday
 May Bank Holiday
 Spring Bank Holiday
 Summer Bank Holiday
 Christmas Day
 Boxing Day

Obviously I know some like Christmas be the 25/12, boxing day, 26/12 etc, but Good Friday, Easter Monday etc harder to identify...
0
I have date A in cell F5 and date B in cell AG11, and both are customized formated as "ÅÅÅÅMMDD" (Swedish for "YYYYMMDD"). Then I want to calculate time elapsed in years with one decimal since date A and date B. For example, it might have elapsed "0.5 year" (20171231-20170630).

Then I need the result formated as "0.5 year" or "2.5 years" for example (if more than 1.999999 then "years"; otherwise "year").

Is this possible to achieve without VBA?
0
I formated the cell as a customized date format as "ÅÅÅÅMMDD" (Swedish for "YYYYMMDD"). But when I do that I get #### displayed. Why, and how do I correct it? The width of the cell is more than sufficient.
1
The basic constituents are these:

'LOPP 1'!F5: date (current/source)
'LOPP 1'K5: place (current/source)
'Travbanor (tabell)! column B: place (to lookup)
Value for spring: column M (to lookup)
Value for summer: column N (to lookup)
Value for autumn: column O (to lookup)
Value for winter: column P (to lookup)

The function I need is this:

Find place from current/source ('LOPP 1'!K5) in 'Travbanor (tabell)'!column B, and
  if 'LOPP 1'!F5 is in the date interval 1st March-31st May, then for place="Jägersro" or "Åby" or "Kalmar" go to 'Travbanor (tabell)'!column M and retrieve this value to 'LOPP 1'!O28
 
 or if 'LOPP 1'!F5 is in the date interval 1st April-31st May, then for place="Solvalla" or "Färjestad" or "Örebro" go to 'Travbanor (tabell)'!column M and retrieve this value to 'LOPP 1'!O28

  (etc. etc.)

I will add quite a lot more of above in this same cell.

How should this formula best be entered, and how long can it be in one single cell?

I have formated the date as "ÅÅÅÅMMDD" (Swedish for "YYYYMMDD") in O28 and F5.
0
I need a crash course in using excel basics. I just started a new job and I need to work with spread sheets. When I update them or, paste info into them, I often wipe out the grid, especially when high lighting a row a different color. I wiped out several sections of the grid. How can I get it back?
0
Hi All,

I have an excel workbook and would like to create a macro that deletes all rows where the value in column N = "N".

I don't want to use filtering as there are other Macro's running currently and the people that will be using the workbook are not Excel Savy!"

Any thoughts?

Thank you
0
Concerto's Cloud Advisory Services
LVL 4
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Hi,

It's taking me very long time to clear out (clear contents) and paste formulas containing any of the above formulas, particularly match() and countif().
Range size is roughly 7000 rows
Calculation mode is set to manual

Is there anything I can do to speed this up?
0
Hi experts,

   Here is the code that i would want to make it work on only filtered data (visible data). I have been trying various ways to make them count only the visible data but it either shows error or wrong value. Hope someone could help. Thank you.

Code:

=COUNTIFS(INDEX('Current Month'!$A:BJ,0,MATCH("Subject Name",'Current Month'!$A$1:$BJ$1,0)),"pampers",INDEX('Current Month'!$A:BJ,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0)),"POSITIVE")

Purpose of the formula: The data set is already filtered. Along with the filtration i have 2 other criteria stated in the formula. The formula has to count and show the number.


I also tried:

SUBTOTAL(103,INDEX('Current Month'!$A:BJ,0,MATCH("Subject Name",'Current Month'!$A$1:$BJ$1,0)),"pampers",INDEX('Current Month'!$A:BJ,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0)),"POSITIVE")
0
As you see from the attached screenshot, I am trying to create queries base don the information in the columns. Unfortunately, the date value in the NEXT_DELIVERY_DATE field does not transfer into my query as a date. I know there a way to format it using TEXT within the formula. Any help will be greatly appreciated.screenshot
0
Excel 2010, have a user with docs that have shaded cells, docs will print with shading, but when viewing the docs, no shading is visible.  This is true for all Excel docs.  Assume it is a setting somewhere, just cant find it.  Any ideas?  Thanks.
0
Is there a way to make the Macro Window larger?
I have a lot of macros and would like to see more of them in one shot. You can't resize the window.
Is there a way to make this window bigger?
Macro Window
0
Hi,

I am using the Sort function in the Win32::OLE package for Perl.
I would like to sort all my columns based on the first 4 columns (sort by: col1 then col2... col4)
For now I am able to sort by the 3 first columns but when I try to do on 4 I get an error in GetIDsOfNames for "Sort" at MyFile.pl

Here's my code:


my $SortOrd1 = $SheetC->Range("A2");
my $SortOrd2 = $SheetC->Range("B2");
my $SortOrd3 = $SheetC->Range("C2");
my $SortOrd4 = $SheetC->Range("D2");

my $Range = "A" . ($dcmRowA+2) . ":" . chr($LastColC+64) . ($LastRowC);
my $Selection = $SheetC->Range($Range);
my %hash = (Key1 => $SortOrd1, Order1 => xlAscending, Key2 => $SortOrd2, Order2 => xlAscending,  Key3 => $SortOrd3, Order3 => xlAscending, Key4 => $SortOrd4, Order4 => xlAscending);
$Selection->Sort(\%hash);

Open in new window



again, if I remove the Key4 => $SortOrd4, Order4 => xlAscending) the code works fine, but I need to sort according to the first 4 columns

Would you know why I get this error with 4 criterias but not 3?

Thanks in advance
Have a good day :)

Rapha
0
Hi there Experts,

I have an Excel workbook with various Formulas and it is expanded from cell A1 to Cell EZ598.
The actual size of this Excel file is 6,6MB and has various records from 2014 till 2017.

I was planning to reduce the Size by splitting the Excel file in two pieces.
Hence I have tried to delete the rows dated before 2017.

When I press the “Delete Rows” option I receive the message:

operation-warning.jpg
After confirming the above action I receive one of two following messages:

Error1.jpgError2.jpg
I am running a Vitual Machine with Win7 Pro and Excel 2013 Std x64.
I have tried to increase and allocate the RAM on the VM to 60GB with 8vCPUs with no luck.

It seems that there is some corruption with this Excel file since I receive the above errors
even when I try to delete a single row (for example from A598:EZ598)

Any ideas?

Thank you in advance,
Mamelas
0
hi,
I have about 50 sheets in my excel with  different headings for each sheet. When i search by selecting Options and then Within workbook it is searching only the content not the sheet names.


lets say my excel name is world.xls
i have different sheets like usa, uk, australia, india

usa sheet i ahve state names like georgia, michigan etc


if i search on usa by Options and then Within workbook it is searching only the content not the sheet names and i never get search results which i am looking(like usa state names)
how to include sheet names also in the search.

please advise
0
I'm creating an excel file from a database using a vbscript.  It all works great... but, in a few columns, I have a very long column name, for example, "Number of Missed Appointments" and I want to put that on 3 lines in a cell so it looks like:
Number of
Missed
Appointments
And I want the cell to autofit to the width of the widest line (in the above example, Appointments).

Maybe this will help convey what I'm trying to do/want:

Example of what I get, don't want, and need
I'm using:

WorkSheetObject.Cells(2,13).Value = "No of missed" & vbCrLF & "appointments"
WorksheetObject.Cells(1,13).EntireColumn.Autofit

Open in new window

0
How to Use the Help Bell
LVL 10
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Hi There,

I'm having a lot of difficulty with a project that I'm working on, about transferring data from Access to a premade Excel worksheet. What I need to do is:

-From an Access table that contains the data, create a query to be able to pull the data for a specific instructor in regards to their schedule.
-Export that data into the appropriate fields in an Excel worksheet that we are required to use.

I've attached both the Excel file that it needs to go into and the database that contains the information that needs to be transferred. The fields that are highlighted in the Excel worksheet are the ones I want to populate from Access, as well as the schedule block at the bottom if that's possible.

If it's possible to do this in an easier way just using Excel, I'm also open to that solution! The only caveat is that this will be shared with colleagues that have minimal Excel and Access skills, so it needs to be user friendly for these people.
1518_InstructionalPlanningWorksheet.xlsx
2018SP.accdb
0
This project would take me two hours and I don't have time this week.
If you are an expert who already has similar code in your toolbox, please post it.

If you can't help, do not worry because I  will be able to get to it next week.


I have many workbooks which have from 1 to 20 sheets. A few of the sheets have some common misspellings which I want to review, and possibly change.  

For instance  curent should be current  and 'ore' should be 'or'.   My cells occasionally have aberrations like <<send us iron ore>>, so I must eyeball all cells before any cell gets changed.


I want three macros: myFindAll   ReviewNext   ChangeAll:

dim allCells as collection  ' static module level variable
sub myFindall(list as string)
    ' example  myFindAll("curent to current; ore to or; disastor to  disaster; etc to etc;")
    ' this would find the text in every worksheet and collect it

function ReviewNext()
   static currentSheet as long, currentArea as long, currentCell as long
   ' this function would advance to the next cell in the collection.  
  '  if the cell should not be changed I manually insert an ague ` character in the middle of the string
  ' for instance  <<send more iron or`e>>

Function ChangeAll(list as string)
 '  will use excel's find a replace.
 this code is trivial and I will code it myself
0
Hello

I am new to Experts Exchange, I came across it when searching the internet for help with VBA UserForm coding.  I landed on https://www.experts-exchange.com/questions/28706684/VBA-Code-to-Populate-Userform-with-Existing-Records-Edit-them-and-add-them-back-to-the-worksheet.html  which not only answered my search but helped me to complete my UserForm.  Basically, the form on that thread was exactly what I was doing.  So I downloaded the solution and set about completing my form, copying and editing the code.

All was going well considering I haven’t used VBA for years (10+) and my coding skills are a bit rusty.  My problem is when I test my form I get “runtime error 9: subscript out of range” for the SEARCH, ADD and UPDATE buttons.  Set ws = Sheets("Sheet4") seems to be causing the problem.

I’ve also noticed that if I start the form from any other sheet than the sheet with the data in it the form is basically empty.  Since I have set the Workbook to open on the USER GUIDE sheet, as this has a button to open the form, this causes a problem.

Well as I say I’m a bit rusty and I’ve been at this for days, reading and re-reading the code, search after search online as well as VBA Help, and now I think I have gone code blind (can’t see the wood for the trees) and my rusty skills are making it hard to find what’s wrong.  If someone could take a look at the attached file and point me in the right direction I would be most grateful.

The above thread was solved by …
0
Have a worksheet that is subjected to monthly updates. One field in a line is a unique number (say Control). The monthly update may be a new addition, a deletion, or a change - all keyed to Control number.

Want to get the monthly Update, bounce it against the Master, and get a list of additions, deletions, and changes. The changed items should indicate where the changes are (perhaps colour code the before and after change per column)

I am sure this is a routine problem or I hope it is.

Thanks
0
Hi,

I have a large excel file. When I click on name manager it does not pop up. I am trying to delete names with errors using
Sub Delete_Ref_Names()
Dim nName As Name
    For Each nName In Names
        If InStr(1, nName.RefersTo, "#REF!") > 0 Then
            nName.Delete
        End If
    Next nName   
End Sub

Open in new window


When i run this, my excel shuts down. What can I do to clean this excel up?

Thanks
0
Hi

Can anyone spot the error in the formula, its mean to count the number of records that match 3 different criteria

=SUMPRODUCT(--(INDIRECT("'Tasklist Detail'!K$3:K"&ROW(rngEndCompleted))=$X$28),--(INDIRECT("'Tasklist Detail'!N$3:N"&ROW(rngEndCompleted))=$AC$5),--(INDIRECT("'Tasklist Detail'!A$3:A"&ROW(rngEndCompleted))=$X$23))

thx in advance

Keith
0

Microsoft Excel

131K

Solutions

37K

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.