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

**0**

votes

**0**answers

35 views

### Auto Extracting Data from Excel file on Onedrive

Our team is using One Drive for file sharing. What we wanted to do is to be able to Auto Extract data from 1 excel file on OneDrive to another excel file to be saved on another OneDrive ...

**0**

votes

**1**answer

50 views

### Why does excel tables break VLOOKUP formulas

I have a worksheet using raw data that Excel does not identify as Tables (ie. plainly putting values in adjacent cells), and on another part of my worksheet, formulas doing lookup in that data, like
...

**0**

votes

**1**answer

94 views

### reference to first visible cell above in Excel

How can I get a reference to the first visible cell above the active one after the data in a table has been filtered.
NOTE: I don't need this for summing up the data above. I know how to do that on ...

**0**

votes

**0**answers

27 views

### paste data with blanks that does not erase formatting in destination sheet

I am trying to collect data using ms excel mobile which does not support a number of the functions I want to utilize. I have set up identical spreadsheets for mobile and full featured excel. The ...

**1**

vote

**1**answer

24 views

### Formula to check 2 Excel 2010 data columns on Win 7 PC

I have 2 adjacent Excel sheet columns that contain numbers and I can insert an "IF" formula in a 3rd column to check for a certain condition without any problem but when I try to combine checking for ...

**0**

votes

**2**answers

28 views

### Excel SUMIFS not working for subtotals

I have a worksheet of data with unique column headers (numerical IDs) and non-unique row labels. (1a, 1a,1b, 1b,2a,2b, 2b, 2c, 3a, 3a etc.). Both of these are defined ranges – HEADERS and LABELS. The ...

**1**

vote

**1**answer

37 views

### In Excel 2010, how do you show the last time a row of cells was updated?

I have a project table that my whole team has access to. Each row is a specific project and they update the appropriate cells in the for those projects. The problem has become that the Last Updated ...

**2**

votes

**2**answers

200 views

### Calculating time between two dates excluding weekends when an input is during the weekend?

I am attempting to calculate the time difference between two dates to exclude weekends and holidays in Excel. The issue I am running into is that sometimes an event happens during a weekend.
For ...

**0**

votes

**1**answer

34 views

### MS Excel for Mac - conditional formatting

I would greatly appreciate help in writing the code to do the following:
cell B7 is the sum of H7+I7+J7
when the sum in B7 is greater than 2 and less than 5 (i.e. either 3 or 4), I would like B7 to ...

**1**

vote

**0**answers

17 views

### Return cells with values in a column when we change the date on a different sheet and remove blank cells

I have one excel sheet which I normally record the expenses everyday. Column 1 will contain the money a person is given and Row 1 will contain the date of the month. What will keep on changing is the ...

**2**

votes

**1**answer

40 views

### Summing a single cell Across Several Worksheets

I can easily sum a given cell, say B9, across multiple worksheets with something like:
=SUM(Sheet7:Sheet11!B9)
This is the equivalent to :
=Sheet7!B9+Sheet8!B9+Sheet9!B9+Sheet10!B9+Sheet11!B9
...

**0**

votes

**1**answer

60 views

### How do I create a percentage completion formula?

I have a project plan with different tasks, each with a reference number i.e. main step, key activity and task. I have planned start date and planned end date for each in two separate columns. I ...

**0**

votes

**1**answer

88 views

### Counting values in a column if a certain value exists in another column

I'm trying to create a Summary tab in my spreadsheet that will summarize info from another tab. Primarily, the parent tab is a list of tasks, status of the tasks (open or complete), and the persons ...

**0**

votes

**1**answer

30 views

### How can I import a TAB delimited file with double quote text qualifier into Excel if a cell value already contains a double quote?

I am trying to import a TAB delimited file with the following format into Excel 2013
"Column A" "Column B"
"tjc0056421151r" "I\gs]hü"nsf^f\F*lqog\düûWp"
"tjc0058088269r" ...

**-1**

votes

**1**answer

41 views

### How to insert a button in MS Excel?

Friends I am using MS Excel 2010 and I wish to insert a button in Excel sheet whose name should be ‘1 ADDER’ and I want that whenever I press this button, it should insert 1 in the cell of column A ...

**1**

vote

**2**answers

39 views

### Excel - Find ID-related Value and fill in another workbook

I'm working in a small hospital in India having following problem:
For a special drug we're having a list with DATE,ID,DOSE,DURATION,NEXT VISIT which we us as a daily working list (workbook 1), means ...

**0**

votes

**1**answer

92 views

### Excel- find match between to columns but copy a different cell

I would like to be able to find matches between two columns in Excel. Lets say Column A and Column C. However Column A's values are linked to Column B. When a match is found in A, can it copy the ...

**0**

votes

**2**answers

35 views

### Excel 2010 formula to delete repeated content based on value (instead of second highest the lowest)

I've read a Question topic about picking the second highest value. I need the same but a bit different:
Excel 2010 formula to delete repeated content based on value
Maybe it is a bit rude, but I ...

**3**

votes

**4**answers

127 views

### How to identify duplicates within text in two columns in Excel

I have a worksheet where column A has various names in varying formats:
A1 John Smith
A2 Jones, Mary
A3 Sally Gomez
A4 The Gonzalez family
Column B has similar data:
B1 ...

**0**

votes

**1**answer

19 views

### Find multiple cells with the same value in col A then search col C for a particular number

Say I have multiple cells in column A with the same value (e.g the number 22). I want to identify those rows then search for the existence of a particular number in column B (e.g. the number 10); ...

**-1**

votes

**1**answer

37 views

### Extracting values from an excel table to build a graph

I have the risk register shown as an excel table. I want to extract the entry corresponding to a specific KRA value and plot its maximum on a graph. I need the method to be automatic so that any ...

**0**

votes

**2**answers

35 views

### Excel - Dynamic Cell Update using multiple criterias

I would like to know if there is a way in Excel to create a cell that will display a value using a formula that will calculate the latest entry by date and campaign.
If I have 3 columns DATE | ...

**0**

votes

**1**answer

48 views

### How to search in column A for specific data and return an answer in column B (“Yes” or “No”) [closed]

Using Excel for Mac 2008.
I'm looking for a formula that will do two things: search in Column A and provide a result from the search in Column B. Column A has a long list of comma-separated data in no ...

**0**

votes

**1**answer

35 views

### How to shift the data from one cell to another cell in excel?

Friends I am using MS Excel 2010 and I want As I type any value or word in a1 cell, it should automatically be transferred into d1 cell and a1 cell should get blank again. Similarly if i type any ...

**0**

votes

**1**answer

111 views

### Excel colon in function

I am learning excel functions and modeling from Google and trial and errors so just want to clarify the following point to get my fundamental understanding correct:
When it comes to colon in excel, I ...

**0**

votes

**1**answer

37 views

### How to automatic transfer the values in another cell?

Friends I am using MS Excel 2010 and I want As I type any value or word in a1 cell, it should automatically be transferred into d1 cell and a1 cell should get blank again. Please suggest me any way, ...

**0**

votes

**1**answer

68 views

### How to highlight a cell when I double click on another cell?

Friends I am using MS Excel 2010 and I want when I double click on a cell say a1 it should highlight c1 cell. Similarly when I double click on a2 cell it should highlight c2 cell. It is possible by ...

**1**

vote

**3**answers

43 views

### How to write a function once and have it update in many cells?

It often occurs that I write a function in one cell and then copy it into many cells. The copies then refer to their own correct rows. But if I change the function in the first cell the copies are ...

**0**

votes

**1**answer

81 views

### Is there a limit on the number of rows a sum function can calculate in Excel 2007?

I am trying to sum a column of 50938 values in Excel 2007 but keep receiving a "#VALUE!" error. The cells in the column do contain formulas. I also tried to sum the column after copying and pasting ...

**0**

votes

**1**answer

52 views

### Excel formula - is it possible to return a value adjacent to a list of values, depending if that list of values matches another list?

In Sheet 1, I have a list of document numbers (alphanumeric) in Column A, which have a corresponding title listed in Column B.
I need to compare another list of document numbers (alphanumeric), ...

**0**

votes

**1**answer

22 views

### SUMPRODUCT where the product is computed from the row

I have a range AG6:AS38. It looks like this:
AG AH AI AJ AK AL AM AN AO AP AQ AR AS
--------------------------------------------------------
6 | 1 A B C D ...
7 ...

**2**

votes

**1**answer

13 views

### Excel Autofill Linegraph with Time

I must admit that I am not the expert on excel, but I need help creating a graph with some data. In column A, I have over 500 cells filled with temperature values from a probe. Now, technically each ...

**0**

votes

**0**answers

26 views

### How can I identify filtered columns? [duplicate]

My file has many columns.
its difficult to know whether there are filters or not and I have to scroll to the right to verify each and every column.
Is there a formula to put in a cell indicating ...

**0**

votes

**2**answers

34 views

### Excel formula to identify and group figures

I have two columns A and B
In column A, I have figures in some currency say US Dollars. In column B, I have account codes to which the figures should be grouped.
A B
USd Codes
2,500 ...

**0**

votes

**2**answers

62 views

### How do you get excel to ignore zero or blank values in an Average?

I am making a spreadsheet to score recipe ingredients, averaging scores for quality of the ingredient, packaging, and accuracy of serving size, and then using that data to create an average for the ...

**0**

votes

**0**answers

29 views

### SUMPRODUCT & ROUND

Is there a way to get the product of the array with conditions to round to the 2nd decimal place before summing?
Current formula w/o rounding:
...

**0**

votes

**2**answers

34 views

### How can I prevent changes to formula results after first use?

We have an Excel 2013 workbook that we use when we create new user accounts (column A for first name, column B for last name, column C for the password, etc.).
First name Last name ...

**0**

votes

**1**answer

40 views

### Excel Month() function does not detect some date format

I have a simple table like this in excel:
The formula i used was =IFERROR(IF(LEN(B3)>0,MONTH(B3),""),""). I check if a cell is blank by using LEN, if so ignore, else, convert it to month no.
I ...

**1**

vote

**1**answer

23 views

### How to use a variable once, across the equation, in Excel 2007?

okay, is there a way to declare a variable inside a cell, without calling the cell name 3 times, across the equation(?)?
I came up with this, to calculate the age from a DOB entered using the ...

**0**

votes

**1**answer

32 views

### How to create a dynamic date title in excel 2010 using different tables and data

My friend has asked for help with an excel assignment and they have 2 tables, HRM and Data_Tables.
Data_Tables has corresponding month numbers from 1-12 in column I from I3-I14. In J column there is ...

**3**

votes

**1**answer

38 views

### Taking an indirect reference in Excel and making it a direct reference

For many somewhat complicated reasons – it has to do with transposing columns and rows because of third party software limitations, etc. – I have had to use the INDIRECT function in order to make ...

**0**

votes

**1**answer

39 views

### Simple sum of dropbox values for scoring Excel 2013 worksheet

I would like to make a worksheet with dropboxes for scoring ingredients and summing the scores per ingredient. I already made the dropboxes with number 1-3(1=poor, 2=fair, and 3=good), but I would ...

**2**

votes

**0**answers

48 views

### How to output a CSV file from a matrix of Excel formulas using multiple files?

I am receiving on a weekly basis a list of Excel spreadsheets from a supplier (around 40 spreadsheets) with data in a specific format. Since the format is a bit complex and can't be converted to CSV ...

**0**

votes

**2**answers

131 views

### Check if a cell contains any of words from a dynamic list

I want to use a formula to populate the "RESULTS" cells. I need it to check if the adjacent "CHECKED CELL" contains any of the words listed in the "KEYWORDS" cells.
Ideally, I would be able to add ...

**1**

vote

**1**answer

24 views

### How to create a text mask in Excel 2007

I have a list of numbers:
59046
64940
77505
101409
140331
1036135
3166909
I want every number to be exectly 9 digits.
For example, i want 59046 as 000059046, 1036135 as 001036135.
How can i create a ...

**0**

votes

**1**answer

48 views

### Attendance Tracker - Deduct points if no points within pay period

I have a spreadsheet where I keep track of whether or not someone called in, was late or left early. On the 1st sheet I have their names, the number of times they've called in, were late or left ...

**0**

votes

**1**answer

11 views

### Hyperlinking or linking in another way to a cell without it being able to change

How do I hyperlink or link to a cell from another cell in a way that when rows are added, and the row numbers keeps changing due to new rows of information being added throughout the sheet, the link ...

**-2**

votes

**1**answer

26 views

### Add a cell content to another cell if a different cell has a specific content

How can I fill a cells value (e.g row 29 col A) with =sum(B1:Z1) but then only add the content of a cell in that row 29 if row 4 of that column says "UK IR". That way I can add all the cells together ...

**1**

vote

**1**answer

35 views

### Sumifs function where I want to sum a range not just a single row or column

I am stuck.
I am entering timesheet date into an excel sheet:
Column A - Resource Name
Column B - Project Phase
Column C - Resource Team...
Column D to AAA is a date
Therefore a single resource could ...

**0**

votes

**1**answer

50 views

### Multiple Formulas in one Cell for hotel

I am trying to create a housekeeping spreadsheet where when a room number is entered into a cell a 0 or 1 (0 is for studio and 1 is for a 1 bedroom) is automatically populated in another cell. I have ...