Microsoft Excel

137K

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

Hi,

I would like a formula to replicate from a certain cell in a series.
Please see attached.
I have placed the correct outcomes in column F which refer to column A (yellow)
Please use column G for the formula

Many thanks

Ian
cell-value-formula.xlsx
0
After adding rows into a spreadsheet is there a way to check the spreadsheet to make sure other cells did not change? Maybe after updating the spreadsheet all changes in the worksheet cells including the added rows would be in yellow or flagged in some way along with any other changes in other cells. Maybe a conditional statement or vb code to recognize any changes in the cells after adding records. I would like to use this for testing. If there are no changes to the worksheet cells then only the added records would be in yellow or flagged in some way. Or maybe a way to compare the old and updated worksheet to look for changes and identify what changed. Any ideas thanks
0
Hi,
The below formula finds bottom row.
Can someone please help me modify it to ignore formula cells.
I only want last cell that contains a value.

=ROW(OFFSET(A9,COUNTA(A:A)-1,0))

Many thanks

Ian
0
I am trying to use sumifs between two work books.

I am trying to sum the net weight column from SECONDARY.xlsx

IF
primary.JPGThe carrier is MP

AND

The date in the column C "Date" is between two dates located in the Primary.xlsx A1 and B1


secondary.JPG
0
Hi,

I was just helped  with graphs numeric column value.  Just ran into a question, in the image below,  Is there a way to increase range within range?  For example, based on the image below:

Can I include or change the display from the bottom part of 0 to 1000, like to 0, 50, 100, 150, 200, then 500, 1000 (and have the rest at top of 1000 stay the same)?

GraphRangewithinRangeValues
(The purpose is to have the highlighted in yellow lower values more visible in scales)
0
Greetings Experts,

I have two files, one is the file with the data and the other is the file where I want to put the data into. I have figured out how the data extractor file will select the other file it wants to take data from. I need help with using the find function and copying and pasting the correct offset cell(s).

Below is what I am trying to do:
Desired-Outcome.png
The reason why I have to use find instead of range select is because all the data files will have different range but the arrangement is always the same. That's why I am trying to find the "DO number" and then just offset one cell to the right. For now, all data files will only have data on the first sheet and the way it should be pasted into the data extractor is just paste values. But I don't know how I can make it copy and paste the part number and quantity like that...

This my code for now:
Sub DataExtraction()
Dim swb As Workbook, sws As Worksheet
Dim twb As Workbook, tws As Worksheet
Dim ExtractFileTabName As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim rFound As Long

ExtractFileTabName = "Step 3 - Final Forecasts"

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Folders to Extract Data!"
    .ButtonName = "Confirm"
    If .Show <> -1 Then
        MsgBox "You didn't select any Folder to save the Forecast Files", vbCritical
        Exit Sub
    End If

With ThisWorkbook.Worksheets(1).Find(What:="DO Date", 

Open in new window

1
Hi,

U guys have helped me successfully with my Excel graph problem Excel graph problem (very grateful).  Working with graphs noticed that the column of values (numbers), are not with the range of the actual values.  For example, in the image below, see the "Trial 12" item where the values ranges from 566 to 3,573, if u noticed the line ending, it's close to 5,000, yet the point should be between 4,000 and 3,000 (the example is circled in red).

GraphValuesNotEqual
How can I fix the numbers values to relate to the actual values of the worksheet cells?
0
I have a sheet that needs simple start stop reset timers built into these  groups
When the timer expires if we can have some kind of indicator that would be great. Flashing time, or flashing cell or something.
 timer start stop reset - flash when timer finishesThanks
Chris
0
I have a Clear Form Button when clicked on my Excel Spreadsheet that is not clearing 2 records for some strange reason, can anyone possibly tell me why this is happening...Please review my code below....

Sub ClearForm()
'
' ClearForm Macro
'

'
    Range("Table_Query_from_MS_Access_Database").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("Table_Query_from_MS_Access_Database_1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Range("B3").Select
    Selection.ClearContents
    Range("B5").Select
    Selection.ClearContents
    Range("B7").Select
    Selection.ClearContents
    Range("Table_Query_from_MS_Access_Database[[#Headers],[GroupCode]]").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub
0
It should have the following steps:
1. Allow user to choose the folder where all the pdf reside
2. Convert the .txt files
3. Ask user to select the designated folder to store the .txt files = "OK | CANCEL "

I am currently using "PDF-XChange Editor" to open .pdf files and then "Save As" .txt into a designated folder.

The .txt files should have the same name as the .pdf files.

Thanks.
0
Dear Experts:

I need a code snippet that does the following action:

If no graphic or graphics is/are selected a msgbox should say 'No picture selected'.

Thank you very much in advance.

Regards, Andreas
0
Dear Experts:

Below inputbox code (which resizes selected graphics) allows for entering values between 1.5 and 3.8

If I enter a value between 1.5 and 1.9, then I get the message MsgBox "Size must be between 1.5 and 3.8 cm!"
If I enter a value between 2 and 3.8 everything works fine.

What is wrong?


Help is very much appreciated. Thank you very much in advance.

Regards, Andreas


Sub resize_graphic()
Dim graphic_size As String

graphic_size = Application.Inputbox(Prompt:="Enter the new size of the selected graphics!" & vbCrLf & _
"Enter a comma for the decimal place!", _
Title:="Resize graphics", Default:="Enter number between 1.5 and 3.8 cm.")

If Not IsNumeric(graphic_size) Then
MsgBox "You can only enter a number in this field"
Else
Size = Val(graphic_size)
If Size < 1.5 Or Size > 3.8 Then
MsgBox "Size must be between 1.5 and 3.8 cm!"
Else
Selection.ShapeRange.Height = Application.CentimetersToPoints(graphic_size)

End If
End If

End Sub

Open in new window

0
VBA: Compare two separate excel sheets column and delete unique value Rows from sheet #2 only.(Not two sheet)
Previously, I already got a perfect VBA to delete unique lows from two sheet, so Sheet#1 and sheet#2 have identical data.

Now, I need to compare two sheet and delete non matched rows from only 2nd sheet. The result is sheet #1 data does not change, sheet #2 have same or less rows.
Sheet #1 data have only 1 column in A.
Sheet #2 data have 6 columns and need to compare with B column.

Please ask any question.
Thank you.
0
In my Excel 2016 I have Power query based on Store procedure with two parameters running on SQL Server database as source.
 On top of my worksheet where I have result I have a table named "param" with two fields. Here I input my parameters that I  am passing to sp.
Table  "param"  has two fields:  ExchangeRate and CheckDate.

I am getting this error in data model .
"
Expression.SyntaxError: Invalid identifier."
Error point to part of code [Name="param"] not sure why this is happening.

Here is code part:

let
    ExchangeRate=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”ExchangeRate”],
                                                                                  ^^^^^
    CheckDate=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”CheckDate”],
    Source = Sql.Database("MyServerName", "MyDatawarehouse",
    [Query=”exec [MyDatawarehouse].[reporting].[My_StoreProc] ‘”
    & Number.ToText(ExchangeRate)
    & “‘, ‘”
    & CheckDate
    & “‘”])
in

      Source
0
My background is as a computer programmer specifically using access databases and VB for apps. My new job requires me to work with spreadsheets in excel. I understand the fundamentals in regards to creating tables from a master worksheet in excel and manipulating the information, vlookups, etc. Part of my job will be updating and adding information onto the master spreadsheet from various tables created by various department. I have not worked with the master spreadsheet yet but understand that there are various macros and formulas embedded in the worksheet. My concern is that I know when adding or updating information via rows or cells that this can have an effect on the entire worksheet. I am maticulas and do not want any mistakes. How would you recommend the checking process prior to entering on a production worksheet? I understand checking for duplicates on the table, checking formating in table cells to match the master sheet, etc. The master sheet has a large volume of data and I don't want to make a mistake by updating one cell and affecting another. Thanks for taking the time to review and offering suggestions on a checklist to adhere to accuracy.
0
Hello experts,

I have the following attachedxml file which allows me to select specific tables through ADODB.Connection
Here is the procedure:
Sub SQLQueryOut2(wsName As String, strSQL, strInitialRange, strServer, strPort)

Dim objRS
Dim SQL

Set wb = ActiveWorkbook
Set DestSh = Nothing
On Error Resume Next
Set DestSh = Sheets(wsName)
Set wsConfig = Worksheets("Config")
On Error GoTo 0

    If DestSh Is Nothing Then
        Set DestSh = wb.Sheets.Add(After:=Sheets(wb.Sheets.Count))
        DestSh.Name = wsName
        wsConfig.Select
    Else
        Sheets(wsName).Cells.ClearContents
        wsConfig.Select
    End If
    
Set objRS = CreateObject("ADODB.Recordset")


' ==> Connections
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={PostgreSQL ANSI};" & _
"Server=" & strServer & ";" & _
"UID=user;" & _
"PWD=password;" & _
"Port=" & strPort & ";" & _
"Database=db;" & _
"sslmode=require"

SQL = strSQL
objRS.Open SQL, objConn

On Error Resume Next
Set rs = objConn.Execute(SQL)
On Error GoTo 0
If rs Is Nothing Then
MsgBox "SQL query reported at row " & rw & "  is not properly set up unable to transfer  data."
Exit Sub
End If

 For Idx = 1 To rs.Fields.Count
        Sheets(wsName).Range(strInitialRange).Offset(0, Idx - 1) = rs.Fields(Idx - 1).Name
    Next

    Sheets(wsName).Range(strInitialRange).Offset(1).CopyFromRecordset rs

Set objRS = Nothing
Set objConn = Nothing

End Sub

Sub SQLQueryoutConfigSheet()

    Dim wsConfig As Worksheet, 

Open in new window

0
I have a list of URL's that I copied that have the Text and the URL.  I know how to get rid of the URL and just have Text but I want the opposite. For instance, if my copied URL is:

Google   <----- This is the link in Excel. Under it is https://www.google.com

I want to extract the URL from the Text so the result is:

http://www.google.com

Is there a formula to do this?
0
I have multiple timesheet tabs for each week until the end of the year.  Each worksheet the user will select a project number and put down hours against that project number.

I populated two tabs Time 10-21 and Time 10-28 with some project numbers and hours.  What I'm asking is on the "hours Tab" that the hours are being added from  each tab.  See hours on the "Hours Tab" for examples.

See attachment
C--Users-lfreund-Desktop-TTE-T-M-20.xlsm
0
Hi,


Is there way in Excel to find duplicate values? I don't want to delete but list duplicated value from a cloumn.
0
Excel 365 crashes when I try to find using "ctrl F"
0
VBA: Compare two separate excel sheets column and delete unique column value rows from 2 sheets.

For example,(compare column B)

1st sheet     2nd sheet
1 2 3 4 5     1 2 6 7 8
2 3 4 5 6     4 4 5 8 9
3 4 5 6 7     2 5 7 8 9
                     3 8 9 0 1
After
1st sheet     2nd sheet
1 2 3 4 5     1 2 6 7 8
3 4 5 6 7     4 4 5 8 9


Thank you~
0
I have a report

Start.PNG
that I need to snap every day, and then stack them into a table

End.PNG  

in order to convert it to SQL Compliant blocks.

I know how to write the vba to do this (with a lot of "Finds" and Range(XLDown), etc), but I was wondering/hoping if there a shortcut formula or just a cleaner Excel technique to do this.
0
An excel macro workbook, which worked fine for many months, all of a sudden, started balking at this code:
Worksheets("Menu").Activate

It is in Sub Auto_Open(), because I want the "Menu" sheet to activate when the workbook is opened.

Now, when it is opened, the following error appears:
Run-time error '57121':
Application-defined or object-defined error


Even when I comment that line out, and then run it in the immediate window, I get the same error.

I don't know if this is related, but at the same time, I started getting an error when using a calendar widget on the same sheet. But I removed the calendar and this error still appears.

Any ideas? I'm perplexed.
0
Text file to excel.
I have two set of txt file and I can import to a sheet of excel.(sheet name: R, S) But I want to import to one sheet using VBA.
Also, I want to delete duplicate value(name column) from each set of data(ex, lee from sheet R, kim and lee from sheet S)
Is it possible ?

Thank you.
0
Hi

I have a workbook and  what im trying to do is at every change in number in column AZ I need to insert a  blank row.

Any ideas?
0