# complex

1. ### Custom sort

Hi all, I want to sort a pivot based on the options Example: I have a data contains 3 columns, State_name, Lives (*integer), Coverage (*Percentage values) I have to create 3 options 1. Alphabetical 2. High to low - Lives 3. Low to High - Coverage If I choose any of the options my data...
2. ### Complicated SUMIFS/COUNTIFS Formulas Required

Hello, I have two sheets, named: 1. Master Data - This sheet will be updated daily by pasting a system report over the existing data 2. 2022 - I want to calculate some stats from the master data sheet on this sheet The gist of this is, I need to report on people who leave between 0 and 5...
3. ### Find a lower value from dynamic range

I'm trying to replace a dropdown menu, whith a cell. The user is going to input the real distance in this cell. As it's works now, the user select the nearest "visible" distant (there can be several selectables but the one whith the best / longest option is visible) So this works like this...
4. ### Sort values based on colors, then on values using VBA code

Hello, I'm tryin to sort values based on colors, then on values The macro was working very well, but on one sheet, so I have to create a new module for each sheet, and because I have around 400 sheets, This is the simple working macro: Sub A_Sort() ' ' A_Sort Macro ' '...
5. ### Need help with making complex formula

Hello, I am trying to make a formula that goes from row to row and left to right to check if the cell is equal to 1. If the cell is equal to 1, then I want to print the number that's below the cell. I added a picture to illustrate what I mean. Appreaciate any help I can get. I have been...
6. ### Formula to return a number based on the content of another cell

Hi there, I’m hoping someone can help me with this question. I have a series of formula being used to generate unique reference numbers, finishing with CONCATENATE, as seen in the screencap attached. I am using a simple =IF(H60=””,””,ROW()-2) formula to generate the final four digits of the...
7. ### Complex IF formula

Hi everyone, I hope I can explain in detail, what I need. Cell J12 reflects the required date of drawing 101 for PO 123456. For this I used the formula: =IF(ISBLANK(\$B12),"",\$B12+14) Cell J13 needs to reflect the required date of drawing 102, based on the date drawing 101 was sent back to the...
8. ### Challenging/Complex named range or Idea

Hi folks! Can you please help me with the below? I have 2 columns AAA 12/1/2019 AVC 11/2/2019 AAF 11/3/2019 AA 11/4/2019 AAA 11/5/2019 AA 11/6/2019 AA 12/7/2019 AA 11/21/2019 AAF 11/15/2019 AVC 11/11/2019 I want to define named ranges depending on column A value. For...

10. ### Complex IF formula

I'm struggling with a complex IF formula, hoping someone can help. In D2 I have a category number 1-4. In F2 I have a number which could be positive or negative. I need a formula in G2 to work out the following conditions: IF F2 is 0, G2 = 0 IF D2 = 1 AND F2 is GREATER than zero MULTIPLY...
11. ### Complex lookup

I am trying to figure out a rather complex lookup. Here is an example set of data. A B C D E F 1 2 3 4 X 5 2 3 What i need to do is the...
12. ### VBA code to format (un-pivot) table

Hi, I am trying to un-pivot this table- <tbody> A1 A1 A1 A2 A2 A2 STORE RECEIPT WEEK Floorset Target Instore Newness Target Instore Newness FY20 100 89 25 200 193 55 </tbody> To look like this- <tbody> Business STORE RECEIPT WEEK Floorset Target Instore Newness A1 FY20 100 89...
13. ### what is the best course to learn about excel and vba?

Is there a Microsoft course on excel or what do people think is the best course if you want to learn how to code in vba and make complex Spreadsheets?
14. ### Extracting multiple keywords from a cell

I am trying to create a formula that will extract the campaign name that generated the lead in the sheet. The campaign name column looks like this- 2016_00_InboundWeb_Request_A_Demo 2016_04_PPC_Google_Generic 2016_00_InboundWeb_InboundCall&Chat 2016_12_Telemarketing_Infocore_Spa-Salon The...
15. ### Excel Multiple Keyword Extraction from cell

I am trying to create a Formula(s) that will extract the campaign name that generated the lead in the sheet. Campaign Name 2016_00_InboundWeb_Request_A_Demo 2016_04_PPC_Google_Generic 2016_09_Hotleads_EngagementPrograms 2016_12_Telemarketing_Infocore_Medical...
16. ### Complex Macro

Hello all, I am looking for some assistance with a complex macro. I need to pull in all data in tabs between 'Start' and 'End' in my workbook into one tab called 'journals'. The trick is, I need to pull in all the data from each tab in columns JK:MX. Then I need to stack the data so tab 1...
17. ### Nest NPV function within PMT formula as the argument for "PV" .

I've been given a particularly diabolical challenge, and for the life of me, I cannot make it work. The challenge is to execute within a single cell a formula that will calculate "EAC: equivalent annual cost" within a single cell. There are two distinct steps in the calculation; first, one...
18. ### Calculation of Closing Balance in Excel

So I want to apply a simple formula to calculate closing Balance i.e. 'Closing Balance= Opening balance + purchase - sale' But there are many complexities. the main issue is. I have 3 workbooks, Workbook 'O' and Workbook 'T' and Workbook 'CT' Workbook 'O'- containing Opening balance as on...
19. ### Complex Concatenation

I am trying to concatenate a complex string of text and percentages and variances and struggling to get it to work. Can any geniuses out there assist me? A1 = "Revenue" A2 = % which could be +/- which is referencing Cell B2, ie -5.3%. Negative figure not required to be pulled through A3 =...
20. ### A bit complicated.. at least for me

Hi all, I have this specific task at work which I failed to do with VBA. So let me explain and even put a link to my file. I have the below arrays: Alpha: Includes a lot of Index Code|Exchange Code (Separated by "|") Beta: Includes a lot of Date| Exchange code (Seprated by "|") What is...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back