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

learn more… | top users | synonyms (5)

1
vote
1answer
14 views

Replace contents of Excel column with realtional data from another column/sheet?

I have two excel sheets that have been exported from MySQL, with relational data in them, and I need to replace the reference ID's with the actual data from the relative column. For example, in one ...
0
votes
1answer
19 views

Excel Office Pro 2010 SUMIF

Hi Im trying to SUMIF(range;"<>#N/A";range) but the cells im selecting to sum are not in a row adjacent, its every 4th cell in the row. But when i try it gives an argument error each time i try ...
0
votes
3answers
45 views

calculating end times with working hours and skipping weekends

In my worksheet, I want to calculate estimated end times of processes. However, i want to restrict this to a predetermined time constraint. So for example, when i add 4 hours to 14:00 I dont want the ...
3
votes
1answer
23 views

Excel, how to use text functions on lookup array without putting in another column?

If I'm using a vlookup, or a match function but I need to do some mumbo jumbo on my lookup material, is there a way of doing this in the lookup array without simply adding another column? Let's say I ...
0
votes
1answer
19 views

VBA highlighting a cell if corresponding range in the same row is not blank

I am trying to enforce food name in column B of a file if column D to DX (Qty) has any value in it. Rational is if any new request of food is received which doesnt appear in the file, the user can ...
0
votes
0answers
16 views

Column order and names for table objects in Excel

I have a table in Excel that is connected to MS SQL Server table in the database. When I created a connection I specified a query that returns col1, col2, and col3 in this particular order. Then I can ...
1
vote
2answers
12 views

Finding repeating digits at the end of a string of digits

I have a list of 10 digit toll free phone numbers. I would like to identify those that contain at least 3 repeating digits in the last 4 digits of the number. For example: 8xxxxx5444 8xxxxx2220
0
votes
1answer
36 views

Excel - find multiple pieces of data in several different columns at once

I've currently got a spreadsheet on the go which lists contracts. I need to know if a contract is a renewal, new to product, or new to company. I'm currently struggling with the logic behind it and ...
-2
votes
1answer
27 views

Attendence Points Rollover

I have an attendance worksheet where we calculate employee attendance based on points, if employee absent they get 1 point and if tardy or did not complete shift get 1/2 points also if employee has ...
0
votes
1answer
27 views

Functions (specifically text functions) within a countif function

Is there any way to nest text operators within a countif? I want to check if the first 4 characters of a file are in the form A**. =Countif(Left(A1,4),"A??.") doesn't work, but I can put in a ...
1
vote
0answers
28 views

Matching a column range based on a column label

I have written a spreadsheet for Excel 10 and am struggling with the last bit of formula. I wrote this: =SUMPRODUCT(--(ISNUMBER(FIND(B7,'PO SHEET'!$K$7:$K$74))),('PO SHEET'!$W$7:$W$74)) However, ...
-1
votes
1answer
23 views

How do I isolate certain characters in cell content of an excel to new cell?

How do I isolate certain characters in cell content of an excel to new cell Example: I have a big paragraph of information wrapped in a cell and I need to separate only few characters of that data ...
0
votes
1answer
19 views

Run formula from different sheet

Can you run formulas from different sheets? (not simply showing the formula as a string/static text) Say I type =Sheet1!A1 where in that cell I have a formula =concatenate(ice&cream) ...
0
votes
1answer
21 views

Copy every nth column on a specific row from one sheet to another (in Google Sheets)

I want a function to select every 2nd column on a particular row and display it in another sheet. I have two sheets: Sheet1 and Sheet2 In Sheet1 A2 I have a list of numbers that start from 1 to ...
0
votes
2answers
32 views

Return data based on matching date range

Our group has an Excel sheet that's used to track rotating responsibilities. A simple example would be laid out like this: Column A: START has dates defining the start of the time period represented ...
-1
votes
0answers
20 views

how to add all values above row that meet multiple criteria criteria [on hold]

I want to add up all values above and including the row that meet two criteria. Unfortunately i cannot post a picture In this example i want to sum the values including and above the row that meets ...
1
vote
2answers
26 views

Generating coefficients for SERIESSUM formula in Excel

I'm modelling a cash collection spreadsheet where a company sells products each week and wants to collect cash over a number of weeks (n; between 42 and 336 weeks). Assuming n is ≤ 52: To calculate ...
0
votes
3answers
62 views

Excel Formula average without blank cells

I have a row of values and want to find the average of the first 3, not counting blank cells. Example: A B C D E F G 12 blank 8 blank 7 9 4 The average of the first 3 ...
0
votes
1answer
20 views

How can I use the value in one cell inside the formula in SAME cell?

How can I use the value in one cell inside the formula in SAME cell??? A1 1000 B1 5 I WANT ANSWER IN A1 =1000*B1 A2 = 100*B2
1
vote
0answers
20 views

excel formulas not working in structured table protected cells

Structured Table1= A B C QTY Price QTY*Price 1 3 3 =A*B 2 3 Column QTY & Price are not locked while Column QTY*Price is locked & hidden as it has a ...
0
votes
0answers
16 views

Search multiple sheets for a term in one column each, copy all matching rows to a new sheet

We have a 3rd party supplied , multi tab sheet that I would like to filter and copy data to a new sheet. In the original sheet, all tabs can be searched for the term 8A in column C. I want any row ...
2
votes
1answer
12 views

Can you apply names in OpenOffice Calc the same way you can in Excel?

In Excel, you can apply names to a formula (semi)automatically by selecting the formula/formulae in question and then using the Apply Names function. This will change something like =B3+C9*D16 into ...
1
vote
2answers
36 views

Excel 2010 Advanced Filter issue

I ran into something I can't really explain trying to filter a huge pile of data using the advanced filter... I had a document with hundreds of thousands of lines (.xlsb) containing a whole bunch of ...
2
votes
1answer
47 views

Excel Macro Hyperlink.Add Errors

I'm relatively new to VBA code within Excel, so please forgive me if the answer is simple (I expect it usually is). I'm using Excel 2010, for reference. I'm trying to accomplish the following, with ...
0
votes
1answer
12 views

MS Excel cell references shifting after using a macro?

Basically this is what happens: On Sheet1 I have a column of cells that are linked to other cells on sheet2. Looks something like this. Sheet1!A1=Sheet2!A1 Sheet1!A2=Sheet2!A2 Sheet1!A3=Sheet2!A3 ...
-1
votes
1answer
24 views

How to return earliest date

I want to look at the dates of 5 columns of qualification then enter the earliest date in a new column then if it is before today's date turn it red and highlight it. I can do the second in ...
0
votes
0answers
8 views

Formula returning #N/A when referenced cell is filled in?

I have a log I have been using for years with no problems. All of a sudden my formula is returning an #N/A. The formula reads =IF(C6=0," ",VLOOKUP(C6,'ODP LISTING'!B$4:C$35,2)). On the sheet labeled ...
0
votes
1answer
32 views

How can I find duplicate values in a column delimiting the results by ranges in a different column?

A B C 1 RANGES VALUES FORMULA NEEDED 2 1 x yes 3 1 x yes 4 2 x no 5 3 no 6 ...
0
votes
1answer
15 views

Query two fields and select answer from third field (If or vlookup?)

I need to compare two fields in my data to a table and get the resulting fx value from table. How the formula would read I would guess: IF local_in_data = local_in_table AND expense_in_data = ...
1
vote
0answers
20 views

which function would add a row to another set of rows, so it behaves as if they were below each other?

Example: having data in A1:E4 and another data in G1:K1 and i need to use them to look like A1:E5 so that G1:K1 was added below A1:E4. The reason behind this is that excel is asking to give set ...
1
vote
1answer
22 views

How to get the median based on column headings?

Let's say we have: EPS Sentiment Revenue Sentiment Formula1? Formula2? 10 3% 20 5% F1a F2a 9 6% 30 4% F1b ...
0
votes
2answers
28 views

How can I set a cell value based off of what another cell contains?

I'm building a time sheet that also tracks the costs to certain jobs. Employees have set Pay Groups, based off the Pay Group, we know the billing rate. However, the list is constantly changing order ...
3
votes
2answers
39 views

How to automatically exclude maximum and minimum values when calculating an average in Excel

If I want to take a medial average, which excludes the highest and lowest values, is there a way to do this on Excel without having to manually find and exclude the highest and lowest values from the ...
0
votes
0answers
20 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
1answer
39 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
1answer
34 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
0answers
26 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
1answer
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
2answers
19 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
1answer
25 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
2answers
71 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
1answer
31 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
0answers
14 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
1answer
34 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
1answer
37 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
1answer
59 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
1answer
22 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
1answer
31 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
2answers
31 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
1answer
49 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 ...