Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (5)

1
vote
1answer
51 views

How to Index-Match upwards from a specified cell until non-blank

I've got data in sort of a directory tree layout (See picture) Basically I want to use one lookup code as a reference point and have excel look upwards in the respective columns to display the ...
0
votes
1answer
31 views

time format in Excel [duplicate]

How to get desired output in MS Excel? note that the outage duration is in mins Start time Outage Duration (in Mins) DESIRED OUTPUT 3:12 AM 60 4:12 AM 4:05 PM ...
0
votes
0answers
46 views

Excel - reference to a last non-empty row

I am trying to make Excel find the cells from the last non-empty row, containing a certain character. I am attaching the picture and further explanation below: So, DK3 value is 4, DL3 value is 4, ...
1
vote
1answer
16 views

Input “minutes:seconds” format in cell, have Excel convert to seconds and report in same cell

I'm trying to have Excel convert "mm:ss" input into "ss" format in the same cell. For example I want the user to enter "1:21" (not time of day, but mm:ss) in Cell A1 and have Excel convert it to ...
1
vote
1answer
55 views

MINVERSE only returns a single number

For the life of me, I can not get the MINVERSE function on Excel 2011 on my Apple Macbook to work. I entered the function into a matrix. I type =MINVERSE(A1:C3), the location of my matrix to be ...
0
votes
1answer
56 views

Extracting data from an excel sheet into another excel sheet

I have a sheet with certain data arranged like this: X8 - something - something2 X12 - somethingelse - asdfg X8 - asft - sastaryraavt X8 - random - random2 I need to extract only and all of the ...
0
votes
1answer
35 views

Change a cell number in formula from another cell [duplicate]

Basically I have this formula: =(MIKE!$B8) and I have a bunch of cells withs Numbers like so: Cell | Cell Number Change C1 | 10 C2 | 16 C3 | 29 C4 | 40 C5 | 61 What I want to do is to be able ...
0
votes
2answers
40 views

Keeping time format in a concatenate

I am working on a spreadsheet that shows durations in Column C2 as hh:mm:ss. I need to transfer this to Column N2 in mm:ss. Column N2 is part of a CONCATENATE in Column E2. When I put time in Column ...
1
vote
1answer
11 views

Can I preload a formula for a future tab

I am building a template workbook with a summary table of preloaded formulas to extract desired info from a sheet that is yet to be pasted. I am getting a #REF! result because the source info is not ...
1
vote
1answer
20 views

Excel | How Can I Increment A Formula By 1 For A Group Of Formulas

Currently what is happening is that Im attempting to increment the formula +1. for example here is what is in b1-b4: Cell | Formula b1 | =(mark!A8)& " - "& (Config!A4) b2 | ...
2
votes
1answer
42 views

Why does putting a INDIRECT inside WEEKNUM return “#VALUE”?

I have tried this on both the Mac and Windows version of Excel. They both return #value. Any ideas of how to fix this? =WEEKNUM(INDIRECT("C[1]",0),2) When I evaluate the formula: , Example of ...
0
votes
2answers
34 views

How to add alphanumerics in excel

I am maintaining stock of flavors in a row: Cell B1 : 5vanilla,5cola,5mango Similarly Cell C1 : 3vanilla,3cola,3mango Is there a sum formula with which I can get auto sum in Cell F1 as ...
0
votes
2answers
46 views

How to keep the values of a column unchanged whether the values of other column are deleted?

I want any value written in cell A1 to be shown in cell B1. For this, I am using the formula =A1 in cell B1. Similarly, to show the content of cell A2, I applied =A2 formula in B2 and the same ...
1
vote
1answer
22 views

Excel 2013 How can I reference data across multiple worksheets + Randomize Table content?

I need to create a complex spreadsheet in my opinion because I am not advanced skilled in excel and that is why I am here.I will also provide pictures for better understanding. Here is my scenario: I ...
0
votes
0answers
15 views

Bi-weekly summing and conditional formatting in Excel

How can I sum only the daily wages of the dates belonging in bi-weekly range and format the borders to look like this? (Sorry not enough rep here). In this case, the cell the arrow is pointing to ...
1
vote
2answers
39 views

Retrieve the array indexes from a range

I am facing an issue where I need to input column indexes from 1 to 1220 in the VLOOKUP function {=SUM(VLOOKUP(A2,sheet1!$A$3:$AC$11, {1,2, 3, 4, 5, 6, ..., 1219, 1220}, TRUE))} The only solution I ...
0
votes
1answer
43 views

how to convert table to one column (show ONLY what i mentioned in table shopping list)

I have a table of shopping list that look like that example: (a1) vegetables (b1) amount (c1) fruits (d1) amount (e1)drinks (f1) amount and in row 2 and so on there is the list. so I want to see ...
0
votes
0answers
38 views

Excel: value between two times [duplicate]

I have an Excel sheet which gives me the number of workers working on a given hour. However I cannot seem to get it to work for overnight shifts. I know it will work if I add a date to the time but ...
1
vote
1answer
35 views

DCOUNT With Function in Criteria

I have a table of data in Excel with each row possessing multiple columns. For the sake of simplicity, let's call them A and B. I want to count the rows wherein which the sum of A and B is less than ...
0
votes
0answers
21 views

Excel Formula Help Please

Can someone help me figure out this formula. I would like the "vlookup" for MSA, Non-MSA and Capital, but for Fill-In I would like the user to be able to fill in a number. Everything works fine ...
0
votes
1answer
62 views

How to drag multiple instances of a range at the same time in excel

Edit: This question is completely revised to incorporate screenshots as suggested by the commenters. I track student grades in Excel using a spreadsheet something like this I often find myself ...
0
votes
2answers
76 views

Excel 2013, combine CountIF + VLookup

I have the following issue, and I need advice on which formulas to use or if I'm better off trying some VBA codes? (I have no idea!): I'm working at an Excel 2013 sheet which has the following data ...
0
votes
0answers
50 views

Excel - Alternatives to array formula for multiple criteria lookup?

I'm comfortable with array formulas, but my co-workers are rebelling against them. Is there an easy way that I'm overlooking to accomplish the following? I've got data in the following format: | ...
2
votes
2answers
60 views

Set a cell to “Yes” or “No” if a range of cells are not empty (have any values)

I have a worksheet with the columns A, B, and C. I wish to have a "Contains Something" column that will either be yes or no depending on if the A, B, or C have anything in them. If A, B, or C do not ...
1
vote
0answers
84 views

VBA code to select by a cell value and date range to move data from one sheet to another

I am having trouble getting a VBA code that I have adapted for my program to run. There are things in it that I don't understand and I don't know how to fix. I am trying to filter data from a "Sales" ...
0
votes
1answer
48 views

Auto-populate certain rows in excel [closed]

Not sure if excel has the power to do what I'm asking but I would like to create a worksheet and then from that worksheet choose specific rows to populate another work sheet simply by clicking on the ...
2
votes
1answer
32 views

Nth occurrence of day of week for NEXT month

I need a formula to calculate the Nth occurrence of the specified day of the week, BUT if that date is past, I want to know the Nth occurrence in the NEXT month. I'm using this to get the Nth day: ...
0
votes
1answer
31 views

Subtract certain hours from a time in Excel

I want to know the time when 12 hrs and 30 mins is subtracted from a time let us say 10:00 AM(it should display 9:30 PM). But since Excel, by default, stores this date as 1st Jan 1990, I am getting a ...
1
vote
1answer
82 views

Extracting multiple lines of data from single cell to multiple cells

I have a mailing list that's setup in Excel. There is only one column and each cell has a full mailing address. I need each line of data from each cell extrapolated to the appropriate columns in order ...
0
votes
0answers
27 views

Sum csv-entries in a field in excel without a macro

Let's say I have in my Excel sheet in field A1 the content 1, 2,3,4,5, 10 then I want to have a classic excel formula combination to sum the entries up, e.g., I want to type in A2 a forumla = ...
4
votes
3answers
61 views

Separate text string into separate columns in Excel

I need an Excel formula to separate multiple names currently in one column into three. One single cell currently contains: Jackson Hakel Brynn Holohan John Holohan. I need three columns with first ...
1
vote
2answers
540 views

Extract Text from String in Excel

Help with a formula to extract text clips out of strings. Turning SP BILL(DL/ON-SITE CONT) into DL/ON CONT ST MISC(TRAVEL) into TRAVEL I'm having trouble removing "-SITE" from one cell without ...
0
votes
1answer
36 views

Google Spreadsheets: how to generate a report for the most commonly used words

I have a google spreadsheet, I use to log programs that occur in my work everyday, I have a column, I use to register the names of each individual issue. What I want is to somehow generate a report ...
0
votes
1answer
46 views

Find specific text within larger text string

I’m developing a personal financial budgeting and cash flow application in Excel 2016 on windows 10 and need help in developing a set of formulas that will: Extract specific phrases - one at a time, ...
0
votes
1answer
50 views

How do I calculate the value in a cell and replace with new value in same cell?

The cell value is currently 85.49. I need to add 50%, then add 6%, then add 10.00. THEN that new calculated value needs to replace the original value. So the calculation I used is ...
1
vote
1answer
101 views

Using VBA code to move data from an invoice to a sales summary page

I am creating a database for a friend of mine. She owns a small shop where people can purchase artisan pieces. I have created a Artisan list, and inventory list. on 2 sheets then I have an invoice ...
2
votes
1answer
61 views

Excel Consistently Insert Blank Row at Top of Sheet (after frozen first row)

I am trying to create an Excel sheet that'll have a blank row at the top for my coworkers to request things. Unlike this post "Consistantly insert rows from top of page in Excel" my excel sheet has a ...
1
vote
0answers
29 views

Optimize row height/column width in Excel = minimize white space

I often create tables in Excel where some cells contain a lot of text while others just contain a single digit or some other short value. With wrap text in cells enabled and then a combination of ...
0
votes
1answer
60 views

Display value based on value from dropdown-list

I need help creating a formula in Excel 2010. What I want to be able to do is: When I choose a name from a dropdown-list in a random cell in one column, I want that name to be coupled up with that ...
0
votes
1answer
162 views

Excel VBA Code to move data from 4 specific cells to another worksheet in the next available row [closed]

I have been learning about VBA code but this has me stuck. I am using Excel-2013. I have 4 cells in my "Invoice" sheet (Cells E3, C3, E36, and E34) that I want to transfer to my "Method of Payment" ...
1
vote
2answers
52 views

how to merge data in multiple rows into one row?

Here is an example of how the data looks, and how I would like it to look
1
vote
0answers
22 views

Aggregating Repeating Labels in Excel Sheet into Proper Table

I have an excel file with two columns: the first contains a repeating sequence of 14 labels, while the second contains the text values associated with those labels. What I'd like to do is aggregate ...
-1
votes
2answers
53 views

Return nothing if cell contains a character, value from other cell otherwise

I have a quote number (a string) in cell I2. If it contains Q, I would like M2 to be blank. If it does not contain a Q, it should contain the quote number in I2. How can I do this with a formula? The ...
1
vote
1answer
36 views

Formula to Autofill 2D matrix in excel [closed]

I want to create a formula to fill in a 2D matrix of arbitrary length (8x8 in my case) with numbers starting from 1 and going to 64. I can do this manually however I want to create a formula that ...
0
votes
4answers
44 views

How can I tell if sheet a exists in a workbook?

I have the following code that's not working as expected: If Sheets("a") <> "" Then MsgBox ("sheet a exists") How can I tell if sheet a exists in the workbook?
0
votes
2answers
29 views

Issue with excel and Match

I have the following in an excel table: A1: Labeling B1: [L-1.1.2-A] - Not for use in patient environment according to IEC 60601-1 (3rd edition) (in e-manual or printed manual)[L-1.1.2-B] - Hardware ...
0
votes
1answer
24 views

Excel Make a string based upon values

I'm facing the following problem: I have eleven columns of that represent product origins, sometimes the product is taken from one origin and sometimes it is taken from two origins. The columns have ...
0
votes
1answer
31 views

Creating a Macro to sort items by date entered (results triggered from formula)

I have Sheet 1 where I am entering in new data on a worksheet, and on my Reference sheet I am pulling the same values from sheet 1 in so that I can sort them by time entered. My goal is to have a ...
0
votes
1answer
26 views

Excel Need to Populate one column with the value of another based on the original value

Column "I" has the values already, I want column "J" to populate with the information on the KEY I tried the formula IF(I153=2.12,W185) and that gives me one result but I need it to choose the right ...
0
votes
1answer
36 views

Determine first and last qualifying records for each date in Excel

I need to find the first and last of each unique day (column A) that qualifies (column B). In column C I need to write "first" or "last". A B C date use result ----------------- 1-1 yes ...