Microsoft Excel

136K

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

count from last to top

in column LM

as show

a12.PNG

file
29143233_to_base_show.xlsm
0
Expert Spotlight: Joe Anderson (DatabaseMX)
LVL 13
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

I have some field headers in a work sheet that are in Row2.  What I need to do is, no matter what row/column ActiveCell is, I need to know what the header value is in Row2.  So if ActiveCell is R20, I need to know what is the value in R2, and set it to a variable like "HeaderNameOfActiveCell".  If ActiveCell is Q100, the HeaderNameOfActiveCell = [Whatever value is in Q2], and so on.  Even if HeaderNameOfActiveCell is blank.

In the first record that was easy which is Row 3, I used: HeaderNameOfActiveCell = ActiveCell.Offset(-1,0).Value, but because the randomness of rules that can select the ActiveCell, it could, skip a few rows until it finds another cell to act on, so that sort of ruled out using For Loop.  I was hoping to be able to replace the -1 with something that could name HeaderNameOfActiveCell?

Looking for ideas on how I can grab the header value, and save it as a variable, no matter where the ActiveCell is.  Please advise and thanks.
0
Greetings experts,

I need help creating a code that will find and delete embedded objects.

As of now I have found this:
  ActiveSheet.OLEObjects.Delete

Open in new window


However, it also deletes all my other buttons, shapes and pictures..
I only need it to embedded objects like this:
1.JPG
Any help is much appreciated! :)
0
I need to extract a date from the middle of a string in an Excel cell (see sample data)
Sample-Data.xlsx
0
matchthe numeber from column BT to shoe the 2 digit or 3 digit from column AY

a7.PNG29142835--2-_to_match_the_key_num.xlsm
0
gurus,

could you please, if there is a way in excel as attached ,
i have four tabs , where i add
1. List
2. Submitted
3. Pending
4. Won

List page contains data
where i select submitted , i want that row to have a copy in submitted sheet automated
similary for Pending and Won .

is this  acheivable

BR,
SiD
Eg1.xlsx
0
Hi Experts

Hope you can help

I have a spreadsheet with some performance data extracted and entered manually from some reports

There is a summary section (in green) to the right of the graphs and now this is being calculated per week (after receiving some superb assistance yesterday), I need to present a weekly summary for reporting purposes

For this ive created a drop down in cell A2 from the weeks presented in the summary (in green) and underneath have replicated the various categories over which I intent to insert some simple pie charts

However, Im struggling to use INDEX MATCH to successfully extract the data from the summary section (in green) for the corresponding week when selected against the appropriate category

For example, if week 13 is selected from the drop down, the all the values under 13 in the summary section should assign to the corresponding category. For example, for the first one, Int to Rec, the result should be 0 in cell B6 and if 14 is selected, then this change to 3 in B6 and so on for the other categories.

If anyone can help with creating a formula to perform this it would be greatly appreciated

J
Copy-of-Copy-of-USA-KPI-Draft--2.xlsx
0
Excel - copy and paste into multiple columns

I need to copy cell A1 and paste into multiple columns
What I have:
A1  =  ,Point Name,Drop,Description,Characteristics,Alarm Status,Value,Qual,Point Status
What I want:
A2 = Blank
A3 = Point Name
A4 = Drop
A5 = Description
etc....
0
Greetings experts,

I have a very weird problem...
I have a code embedded in "This Worksheet" and what it does is that it will reset Option Button 1 and 2 and hide some cells whenever the the workbook is re-opened. I also have a button that will show and hide another range of certain cells. If I press any option button, hide the cells and save, when I reopen, all the objects in the hidden range are deleted.
Capture.JPG
Attached here is an excel file for your reference: Book1.xlsm

Any help is much appreciated! :)
0
HI Experts

Hope you can help

I have a performance sheet – HISTORICAL - that contains around five weeks’ worth data that is entered manually by our admin team based on reports we extract from our USA warehouse

There are several categories we report on that are displayed to the left of the table and above each ‘week’ the week number is displayed (ie March 24th to March 30th = Week 13)

Im trying to summarise (sum) the values per week based on each category to the right of the graph display however Im struggling to find a formula that will look up both the week number and category and return the sum of the values for that particular week based on each category

Ive tried several combinations of SUMIF and an array formula I discovered online but I cannot get it to return the desired results

I have manually entered the values under Week 13 as an example to show what Id like to see returned and have attached the file in question

Any help you be much appreciated
J
Copy-of-USA-KPI-Draft--2-.xlsx
0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Hi

I want to use Excel VBA to copy everything from a specific sheet called "data" into a new Excel csv (Comma Delimited) file.
How would I do this?
0
Greetings experts,

I have two option buttons, option button 114 and 115 on a sheet.

I need help creating a code to put into Private Sub Worksheet_Change(ByVal Target As Range)

The requirements:
1. If both option button 114 and 115 are left unselected, row 12, 14 and 16 are left hidden
2. If option button 114 is selected, row 12 and 14 appears and  row 16 is left hidden
3. If option button 115 is selected, row 16 appears and  row 12 are left hidden


Any help is much appreciated! :)
1
I need to count cells in a range that are colored with conditional formatting. I used UDF that counts cells in a range that are manually colored but could not find te right function that does the same thing for those cells that are colored with CF.  Could someone give me a  sample code to achieve this please?
0
Hi

In Excel VBA (2013, 2016 or 2019) to add a button to the quick access toolbar when you open a workbook that will run a procedure when clicked?
0
I have a list of numbered entries - they are as follows - number then blank row, then number plus one etc. (See attached file).  Yes, it would be better to have no blank rows but that suggestion was shot down :-(. Any suggestions on what formula I could use so that if - for example - I have to insert an entry between the current 3 and 4 , all the subsequent numbers auto correct to the new sequence?
EE_alternating-row-numbers.xlsx
0
What I am trying to do
      I need to print a slip without giving the option to the user to print the same slip again or guess the next MySerial Number
      
      
      
      What I wish is some VB Coding, whereby
      When I click on "Print the Slip" Command Button
      Value only is pasted in $L$1 is using the formula =VLOOKUP(L2,MyRange,2,FALSE)
      Value only is pasted in $L$2 is using the formula =PrintSr
      User is given a timer message of 20 seconds to check whether the printing is OK or REPRINT the Slip again if required
      If the User chooses "OK' on the message box OR if the 20 second time window expires, then
a      the Cell in row Corresponding to Value given in cell $L$2 above (i.e., PrintSr) Column C on sheet RangeNo in MyRange is Marked "Yes"
      e.g., if we Press "Print the Slip", then the Cell C8 on Sheet RangeNo corresponding to serial number 7 in Column A is Marked "Yes"
      resulting in changing the value of "PrintSr" to 8 and consuming the Number 7 in the List
b      MyRange is sorted in ascending order based on Column D

EEE0001.xlsm
0
will need create an 3 digit and 2 digit
show in columns LJ  and LL
a3.PNG
file
29142786a_to_LJ-and-LL.xlsm
0
Hello Folks,

I'm trying to extract the word that starts with " CHARS " from the string below within a cell, can anybody kindly provide any ideas on how to?
ABCDEFGH MMM Upgrade Patch (MMM111x) - Win 7,ABC006A8,TXT_CCC_Install_Collection_jazzmen,Required,CCC ABCDEFGH CCC Upgrade Patch (QWERTYU),Deployment failed,No additional information,0x87D00213 (-2016411117),Timeout occurred,CHARS10133X166,19,4/12/2019 7:03 PM,Error,Script,No,(SYSTEM),No,No,Yes,

Open in new window

0
Dear Gurus,

I have a scenario where in the attached excel sheet  , if i have identical part number , it should highlight as conditional formatting .

but if i  tag as R in Column B, it shouldn't highlight as duplicate
also the count in total should exclude R's in this case total count should be 4 not 5

could you please advise, how can i acheive in excel

BR,
Sid
Tst.xlsx
0
HTML5 and CSS3 Fundamentals
LVL 13
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Gurus,

i have a scenario where i have two sheets in excel , Sheet 1 and Sheet 2 .
i would like to know, if
1. if i enter data in Sheet 1 , it should automatically be entered in Sheet 2
2. whereas in Sheet 2 , i need to enter data manually that shouldn't go to sheet 1

is there a way to achieve in excel , kindly advise howto

BR,
Sid
Eg1.xlsx
0
need count each digit from column EG

a1.PNG
results goes to column LG , LH, LI

a3.PNG
file
3N_to_count_pair_digit.xlsm
0
Hi, I have an excel 2003 file (on windows xp) which imports data from a website via some vba code (originally provided by a very helpful expert on this forum). It did work before, but then stopped working so I am trying to resurrect it.   The result is a leaderboard from various golf tournaments (it's that time of year!). For each new event, I update the url to pull the most recent table. The attached sample file has the details - the macro shows the code which I am using.  Thank you for any help.
EE_sample_20190412.xls
0
In excel I have about 250 entries with dates shown as this: "Fri Dec 8 12:57:00 CST 2017"  I want to change them to this: "8/12/17".  What is the formula for doing this?
0
I've been exporting data from Excel to CSV manually using Save As....

The intention is to then use  ADO to query the data in the CSV files and everything is fine apart from one thing - the first field name is being prefixed with some strange characters.

For example, if the first field/column in Excel is SiteName  then it's appearing in the CSV file as SiteName

This is obviously causing problems when it comes to queries as I can't use SiteName in the field list or criteria.

Does anybody know why this is happening?

PS I know there are other ways to get the data into CSV format, and I'll probably end up using one of them, but I'm kind of curious to why this is happening and whether, from within Excel, there is
0
Gurus,

As per attached, if i have assigned two sheets to be prepared by two people, if i need to consolidate  , could you please advise how can i do it
will it be index and match or dget etc

also if there is any discrepency between sheet 1 and sheet 2 ( which i have highlighted in orange) can i get notified in dashboard .

Question is modified for below
1. i have dashboard sheet , where when i enter the code, i should get info from two sheets . These two sheets are similar in data, except 1 or 2 which i have highlighted

could you please advise how to

Regards,
Sid
Eg.xlsx
0

Microsoft Excel

136K

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.