approach

1. How do I approach this?

Hi all, I’m having some difficulties as to how I should approach this issue. I’m confident with VLOOKUP, COUNTIF etc but just don’t know what combination to use to solve this problem. Please see the table in the link below first – https://imgur.com/orj2UIh I want to be able to calculate...
2. Indirect Function Alternative

I started using the INDIRECT function to create a super dynamic Sumif that references a named range based on criteria. However, I noticed that my calculation time has increased significantly since taking this approach. I read online that this is a very volatile function that performs slowly...
3. Breaking split at not 0

Hello guys, I have a sequence: <colgroup><col></colgroup><tbody> 00000001 00000005 00000009 00000013 00000017 00000021 </tbody> I want to extend it indefinitely, well until it fits, but i was not sure what the best approach would be. I was thinking of taking it as a string...
4. Truncating A Full Punctuated Name

I must, after pasting a long list of patient names, truncate to display only the first two letters of the last name, for privacy. Beethoven, Ludwig V must become Be., Ludwig V Doe, Jane X must become Do., Jane X I've been using this: =(LEFT(D66,2)&"."&","&(RIGHT(<wbr style="color: rgb(34...
5. If statement with grades

I need an IF statement that keys off grades of C- to A+. Something like this: IF(B1>="C-",A1,""). I know that statement doesn't work, while using a series of OR statements does, but I wondered if there was something simpler than the OR statement approach which uses 9 substatements, e.g...
6. Using Application.OnTime

Hello guys, I'm trying to setup a timeout for a macro I'm creating, it reaches out to a file, if that file is open it times out for 10 sec and then tries again. My initial approach was to make a do loop for x seconds, but that was an awful idea because it prevents other actions from happening...
7. Single or Multiple Variables to Populate Message Boxes in Different Subs

I normally use a string variable (Var_MsgText) as an input to message boxes but I'm currently writing my biggest piece of code to date that has multiple subs that all use message boxes. I don't want Var_MsgText to be a public sub as that would risk incorrect carry over of the content between...
8. Spaghetti code or not?

Is writing spaghetti code always bad? Of the two approaches below, which is preferable, or should there be a better third approach? Approach 1: If Condition1 Then Do something1 Else Do something2 End If If Statement1 Then Do something1 Else Do something2 End If...
9. Excel VBA assign onaction to shape

I have created a tool that presents charet views based on which shape or combination of shapes is selected by user. Each shape is a toggle that turns a chart element on or off. The tool is replicated for multiple data sets. The shapes will not accept an onaction assignment. The problem is...
10. Providing raw data to "non IT" subject matter experts vs a centralised, non excel IT approach

Asking for a friend who works in an organisation where there are some challenges in terms of providing raw data to non IT people... Seems the preference is to centralise requirements in IT with "programmers", ques and an output which is somewhat limited in functionality and usability...
11. Lottery / Programs

https://drive.google.com/open?id=1rqjKPqFt8RpTVWolb1u3pPw11gfHFs2A I see there are plenty of people here looking for different ways to randomly pick or perhaps guess lottery numbers using Excel. I would like to share my spread sheet and also see what approach others have taken. Like jak68 or...
12. Conditional shading between two lines in a graph?

So I found plenty of help online about shading the region between two lines and successfully got it working for my data (specifically using this website: https://peltiertech.com/fill-under-between-series-in-excel-chart/ ) Well - now I need to only have a shaded region if one of the lines is...
13. Paste Filtered Sheet

I have a sheet of rates. Column A is the rate. Column B is the effective date. Supposed I wanted to escalation the rates for 2020 by 5%. My first approach would be to filter the date column for 2020 and then enter a formula in column C like, =A2*1.05. Then I would copy column C and paste values...
14. Feeling really lost. Anyone up for skyping/chatting?

Would really like to talk to someone about how to approach a problem. I have tried everything I could think up. I have looked online and even asked friends. I have wasted a lot of time trying to figure this out and would just like to see if anyone really good with excel might want to talk and...
15. Need help VBA - Run procedure of project in another Workbook from current Workbook

Hi everyone, I want to run a procedure of project in another Workbook (wb2) from current Workbook (wb1) -This procedure was not created by me and too complicated for me to understand. -This procedure has Arguments, like "Case 1","Case 2",etc -I do have wb2 open I was searching and found two...
16. Running Solver on Multiple Rows of Data

Hi Everyone, I am working on a problem to update pricing throughout a database of about 800 parts. After looking around online, I think Solver is the best way to approach this problem, but I couldn't find any VBA code to execute this. Here are the rules (also illustrated below) Primary Logic...
17. Remove blank cells from an array

Hello - I have an array with different names (Array 1). I want to remove the blanks in this column & create an output in another column, where the blanks are removed. I have attempted to do this however I am struggling to achieve this. :(Any help on this would be much appreciated. Any advise in...
18. Better/More Elegant Formula question

Hi there, I've got a formula: SUM(A1*B1, A2*B2, A3*B3, A4*B4, etc..). Is there a more elegant (or better practice) way of writing it or is this the best approach?
19. Copy a formula right, but cell reference going down.

I'm currently using a formula IF(B6=1,"W","L") I need to copy the formula right, but when I do so I then get if(C6=1,"W","L"). I'd like the cell reference to go down instead so B7, B8. I'm not sure what the best approach to this would be?
20. How to approach multiple countif scenarios

Hi, I have an enormous amount of data (again) that I am currently using filters to disseminate. It basically is a ton of raw surveys, with responses that could be categorized as either A - text (like guest name), B - information data (like arrival date, whether they used mobile check in, etc)...