# formula

1. ### Conditionally format cells to red color if cell 4 rows to the left is not blank

Hello everybody, I currently have the following list: I want to create a formula (probably through conditional formatting i'd imagine), that basically says: If the cell 4 turns to the left of any designated cell (A1) in row E is not = blank, color the cell in row E with a red...
2. ### How to average same cell across worksheets given a condition

Hello - I'm trying to create a master Workbook that can take the average of the values of an individual cell across multiple workbooks, but given a certain condition is met. For example, I want to average all of the values of C3 (which is a number) across all worksheets, but only when cell A4...
3. ### + and - (plus and minus) in Excel list

Hello everybody, I am currently currently managing a stock overview in Excel (manually), where I have a set quantity. I want to create two columns next to my quantity column where I can enter numbers that should be substracted and added to the quantity, so it looks like this: Now obviously I...
4. ### Need help with macro

Hello! I am trying to use the below macro to flag an error message when a date range exceeds 7 days. I have used the below which only applies to the first cell in the column. What must I do to apply it to the whole column, I have 350 rows. Just to let you know column K is a calculation of...
5. ### Formula result other than displayed

Hello people, The formula I've written shows a zero as result, whereas when clicking the fx-symbol to check the formula, it shows the result will be 1; as expected. Who can tell me why I see a '0' as result in the cell?
6. ### Help With A Formula

Hi, I have a column (D) in my workbook that shows when a file was last updated. I need help with a formula (or whatever approach is right) to determine how long it has been since it's last update. I would like to use the current date comparing against what the date is in the column it was last...
7. ### Formula help

Anyone know the formula to pull the filename only and pout it into a cell. Like just the files without the file name extension?
8. ### post information below a merged cell table

So I have a table with a huge amount of information and it is broken up into 31 sections, one for each day of the month. On the left column are names of associates... So row 2 has a series of merged cells with numbers... 1,2,3 Under that in row 3 are 6 categories which repeat 31 times under...
9. ### VBA - update formula when creating a new sheet

Hello, I have several sheet here - Total is the calculation of all sheets (exclude Template) - Template is the master sheet of Nancy, Quincy, Bella, etc Step on macro : 1. Add new sheet 2. Copy and paste 'Template' sheet 3. Calculate the total on 'Total' sheet (sum formula) The problem is...
10. ### Get value based total column in range minus Blank columns

Hi. I have data in range from Columns G to R. I have value "a" at column J and then 2 Empty Columns. After them I have data at column M and formula give me value "d" but I want to be "b". Also for Column Q, I have "h" but I want "c". If I have Data, this is my formula at Column M( change Column...
11. ### Sum if some condition match

Hi All, We have the following sheets Sheet1 ABCDE1d & TNameOTNEWP/l22/20/2018 0:38:52Tom Van52466521425832/26/2018 16:47:52652302547 Sheet2 ABC102-20-20182Tom VanTest 1pass3Test 2o.k.4Test 35Test 46Test 5 we want the following result if Sheet2!A2 will match Sheet1!B2:B3 and Sheet2!C1 date...
12. ### Filter and Index match range if some condition match

HI All, Thanks in advance, we have two sheet and want some result First Sheet ABC1O NoDatedesc265859521-07-2015o is still pending3359624621-07-2015o is sent to us465894710-07-2015o is pending due to stock562547808-07-2015O is in dispatch process Second Sheet ABCD1I...
13. ### Concatenate Unique Values but remove the "/" where the valuue is unique

Hi all, I need your guidance if possible on how to amend the below array formula? =TEXTJOIN("/", TRUE, IF(MATCH(AN4:AN23, AN4:AN23, 0)=MATCH(ROW(AN4:AN23), ROW(AN4:AN23)), AN4:AN23, "")) Currently it adds the "/" as a pefix to the end of all values but i need this removing so it shows like...
14. ### Fill in the start date and end date with each list name with vba

Dear All Master , Please help me to fill in the start and end dates that have been specified in the vba sheet in column B that I marked the color based on the list of names in the vba sheet in column B that I marked the color. The result is in sheet 2 in column A & B which I marked green. I...
15. ### How would I convert time values to words?

Hello. I have a workbook I created to track break times at my work. I have a column of times in a workbook in the custom format [h]:mm:ss. Example 0:06:00 for six minutes. This column tells us how long the person went over the required break time. I would like to, in another column display this...
16. ### Concatenate to show values to 2 decimal points

Hi all, I have tried Google to find a fix to my issue but nothing has worked. The problem that I have is the below formula needs to show 2 decimal points when it concatenates. =IFERROR(IF(\$CJ2=\$CK2,\$CJ2,IF(CZ2=2,(CONCATENATE("£",\$CJ2," / ","£",\$CK2)),IF(CZ2>2,(CONCATENATE("£",\$CJ2," -...
17. ### if some condition match then sum of the value

Hi all, Thanks, We have the following two sheets ABC1O NumberT1T22pk-98417aa568df9563pk-526325aa659df9568 Sheet2 ABC1NumberNo of StuAttent2pk-984175244563pk-526325624634pk-98417235305pk-526325654155556 we required if Sheet1!A2:A match with Sheet2!A2:A and heading of E2 & F2, then total of...
18. ### How to get the specific charachters from the text?

Hello Everyone, I would like to take the specific characters from the texts in the first column into second column with the formula. Could you help me with this? Which formula should I use? We have 5 specific characters ("Correction", "AB Correction", "Secondary Work", "Final Check"...
19. ### Help needed to create a formula

Hello, I am trying to do the following (and I am totally stuck). I have a list of suppliers and their respective deliveries they did for us. For each delivery, I have assigned a scale (green, yellow, red), in order to measure how good the delivery was in terms of timing. What I would...
20. ### Adding number of days to a TEXT date formula

Hi, I am trying to set up a formula that adds 30 days (payment due) to the date on the invoice being sent. I have being able to create the formula that references the invoice date but I can't seem to add on the extra bit that adds on the 30 additional days. ="Due Date for Payment...

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...