All Questions
48
questions
0
votes
2
answers
70
views
Copying Sheets and cell references
Let's say I have "Sheet A" and "Sheet B" within the same workbook.
Sheet B has a cell that references Sheet "A" i.e. ='Sheet A '!Q20
I want to copy Sheet B and Create &...
0
votes
2
answers
121
views
In Excel, Format a Number with Intermediate Spacing AND Optional Decimal
I have a series of numbers in Excel that I would like formatted in a certain way and I need some help.
Background:
I work in construction, and am using the Construction Specification Institute's (CSI) ...
0
votes
1
answer
114
views
Conditional Formatting Not working (maybe pilot error?)
Excel Version 16.68.
I want to compare numbers in Column D to a Reference Number and use Conditional Formatting Formulas in Column E to highlight the corresponding cells.
I use Formulas because the ...
0
votes
1
answer
121
views
Excel Conditional Formatting - Using Formula as Part of a Cell Reference
I am trying to use conditional formatting to highlight a cell if it is blank and a cell in the first column of the same row is not blank. For example, if the conditional formatting is for cell D4, I ...
0
votes
1
answer
114
views
How to decrease the number of cells that are selected in the formula in Excel?
I'm using the AVERAGE function and drag/expand it along one row (e.g. from A1 to D1), the cells that are selected should decrease by 1 row in each new column.
For example:
A1
=AVERAGE(A2:A10)
B1
=...
0
votes
5
answers
2k
views
Excel: Sum all numbers separated by new line within cell
How do I create a new cell that equals the sum all numbers in a cell separated by a new line?
I may not use text to columns as that is messy and there's no new line separator delimiter in text to ...
0
votes
4
answers
2k
views
excel formula of sum with error and cells not in range
I want a sum formula that will ignore errors.
For example something like: =sum(c3+f3+h3+j3+n3) however, some of these cells may contain an error.
Between these cells there is data which I do not want ...
0
votes
1
answer
217
views
Get cells from another sheet based on criteria
I'm trying to build a simple helper to control my expenses.
One sheet has a list of all financial movements (income and payments), with these details:
Date
Amount
Reason
02/02/21
-1000.00
Rent
02/...
0
votes
1
answer
694
views
Excel - Use Formula In Different Cell From Referencing In If Statement
I am currently working on a listing template that uses different things like a title generator. This generator uses a ton of if statements based on the data that it is given. I have broken this ...
0
votes
1
answer
52
views
Prevent User to fill in a specified value into a cell before another cell is filled
Since I am not that well versed in Excel I have stumbled upon the following Problem:
I have the two cells:
A1 and B1
I want A1 not to be able to be filled with the value "1" before B2 is ...
0
votes
1
answer
5k
views
Excel: How to create a list of values in one cell for curent and previous values?
I have data that corresponds to "usage" of a product. I can manually update the value as it increases and save the previous values somewhere else.
However, I would like to have the previous value ...
0
votes
1
answer
183
views
simple sum in Excel but replacing one of the addends in its own cell
Am an Excel novice and have looked in several MS office books I got from the library but none say how to do the following, and I've performed a couple of general internet searches to no avail as well ...
0
votes
1
answer
621
views
Excel 2010 - CELL function not returning Worksheet
All over the INTERNET I found that CELL function returns the full file path, the Drive letter, Workbook and Sheet name in a format of C:\Folder\[workbook.xlsm] sheetname more or less by entering the ...
0
votes
0
answers
201
views
Case sensitive formula in excel
I’ve been playing with this formula because I couldn’t get the original syntax to work and it’s nearly doing what I would like. Please can you help me make it case insensitive?
Here is the data and ...
1
vote
2
answers
53
views
Sum cells based on the value of other cell
I am facing a problem that seems trivial in Excel. What I need to do is simply this:
In short, I have a large file of prices linked to a few people and I need to know how much each person paid. I've ...
0
votes
1
answer
50
views
Not using a cell as variable, whilst inserting a (excel-)formula in several cells
I'm facing a small, basic little problem, for which I can't find the solution:
I'd like to insert the same formula in several cells at once. Quickly googling this problem provides a solution:
Either ...
0
votes
2
answers
229
views
How to step up cell reference in function by numeric values based on known number of rows
This is in reference to the formula I need for cell F2 in the below image.
F2 shows the result I WANT:
I NEED: The Concat Formula to concatenate the data in Column B for each variant of whatever is ...
1
vote
2
answers
184
views
Robust linking of cells Excel
Is there a way to link cells so that not only content updates automatically, but also format (fill, border, font, etc.)?
Also, is there a way, other than doing each cell individually, to avoid blank ...
0
votes
2
answers
2k
views
Increment number with letter in cell
I have a column of random numbers that all begin with 'B*****' where the stars are numbers.
I need a formula to add 1 (+1) to the cell on the right of the number and take away 1 (-1) to the left of ...
0
votes
1
answer
79
views
Excel: Data across multiple columns and rows
Currently have a table like this:
How can I consolidate and count the hours per person spent on each day. Need to be on seperate lines due to working on different tasks but want to bring in all ...
3
votes
0
answers
348
views
Formulas Changed to Reference Last Row When I Close and Open Excel File
I have no idea what is causing this issue, but when I reopen an Excel file, formulas that reference cells will change the reference cells at the very end of the sheet.
Example:
Super simple spread ...
1
vote
0
answers
335
views
LibreOffice Calc: when copying an empty cell, it turns into a zero?
In a LibreOffice spreadsheet, when I want to duplicate a cell's content, I use =<source coords>. For example if I want cell A2 to contain a duplicate of whatever is in A1, I fill in =A1 in A2.
...
0
votes
1
answer
5k
views
How to link cells from access to excel?
I have a database in MS Access and I need to link some cell in MS Excel.
I need to link only few cells from a huge table that I update automatically.
Maybe it is easy but I know how to import table ...
0
votes
1
answer
453
views
Find a text cell in a range dynamically
I am trying to find a cell in excel using a formula from a range.
The range will be as such:
╔═══╦═══╦═══╦═══╦═══╦═══╦═══╦═══╗
║ ║ A ║ B ║ C ║ D ║ E ║ F ║ G ║
╠═══╬═══╬═══╬═══╬═══╬═══╬═══╬═══╣
║ ...
0
votes
1
answer
73
views
How to apply a fomula multiple times at different intervals to entire column? OO Calc
I am using Open Office Calc on windows 8.1 Pro
I have the following data already sorted nicely.
A B C
--+-----------------------
1 | Bmw Gold £80
2 | Bmw Silver £60
3 | ...
0
votes
1
answer
693
views
How do I calculate the ratio for a range of cells in excel?
I'm trying to calculate the number of females per 100 males in each state and display the result using cell arrays. If the data for any state is unavailable or producing an error, then empty cell ...
1
vote
0
answers
296
views
Excel: VLOOKUP with cell containing multiple references
I have a table which contains multiple references per cell separated by a blank space.
In an other table that contains one line per reference, I want to use VLOOKUP to retrieve the name of the ...
-1
votes
1
answer
2k
views
Lock Drop Down Cells and Enable Use - Excel 2013
I would like a drop down list in a cell. I would like to lock the cell so it cannot be moved or altered but changes other than selecting from the drop down menu.
0
votes
1
answer
2k
views
Formula or VBA for cell formatting using value based on a cell but formatting from an other cell
I have a fixed set of values, (0%,20%,40%,60%,80% and 100%) in column E.
If I fill a cell in column E with any of the above values, the cell G should pick formatting from another cell.
For example,
...
0
votes
1
answer
9k
views
Multiplication of cells containing words
I'm making a finance plan, where I have the columns 'Quantity', 'Cost' and 'Total'.
The quantity column will contain how many of the item I buy.
The Cost column will have the individual cost of the ...
0
votes
1
answer
324
views
Count based on multiple alternate conditions in Excel
I need a formula to count any trailers that are due in but do not have a cell that says "yard" or have a date next to that trailer. Please see screenshot. I tried the COUNTIFS formula and was ...
1
vote
1
answer
97
views
Formatting date from decimal in Excel [closed]
How do I format a decimal value like 1.30 as "1 year & 3 months" in Excel?
The value needs to remain the same but just needs to be displayed as 1 year & 3 months.
I need to do this just ...
1
vote
1
answer
215
views
How do I create a summary of the same few cells on many different sheets in Excel 2013?
I have an Excel workbook with 140 sheets. I'm trying to summarize the same 3 cells in each sheet on a summary sheet. How do I grab the value of the respective cells on each sheet without manually ...
1
vote
2
answers
605
views
Excel 2010 Array Formula (CSE) Dynamic cell range/reference; index? offset?
This might be an easy question to some but I am completely lost so I would really appreciate any help out there!
A
1 10
2 20
3 30
4 40
5 50
6 60
7 70
...
0
votes
1
answer
7k
views
How to Mirror cells in excel 2013 [closed]
I want to make an excel file for sales management of my company. the excel I have created has 4 sheets and every sheet is handled by different persons.
I want to dedicate the first column of these ...
2
votes
1
answer
305
views
Excel - Exclude Cells from Computation
Say I have 5 columns of data with 100 rows (so from A1 to E100). For each row I want to compute the averages of the 5 entries while I always exlude one of the entries. E.g. for the first row I would ...
0
votes
1
answer
3k
views
Conditional Formatting for one cell value in a list, another cell blank
Looking for how to do Conditional Formatting for the following rule:
If a number in a cell equals one of a list of particular numbers, then information MUST be entered into an additional cell. Both ...
1
vote
1
answer
3k
views
Dragging a cell reference in intervals
I have data as follows (simplified) in two worksheets on a document:
Sheet 1 data:
A1=1 -- B1=(Blank) -- C1=(Blank) -- D1=2 -- E1=(Blank) -- F1=(Blank) -- G1=3 ...
Sheet 2 data:
A1='Stage Plan'!...
0
votes
1
answer
2k
views
How to have excel paste a value in a specific cell according to another data table
I'm working with excel 2010,
I have an excel sheet with two tables. The primary table contains a calender and each calender day has a specific numeric value in the cell below the date.
The numeric ...
-1
votes
1
answer
4k
views
Updating another Excel spreadsheet from current spreadsheet
I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...
0
votes
1
answer
3k
views
Formula to display text from multiple cells from one sheet in another
I have a list of sales reps and the names of the customers they've sold product to, on one sheet. I have tried VLOOKUP, but it will only return one set of text from the cells. I would like to know, ...
0
votes
3
answers
470
views
Excel - Referring to a certain cell based off of data in another
I have a spreadsheet where there is one table with the headings:
Coordinate, Lat, Long
Another with headings
Triangle, Coordinate 1, Coordinate 2, Coordinate 3, Area
What this is for, is taking ...
8
votes
5
answers
239k
views
How do you write an Excel formula that will paste a specific value in a different cell?
So basically I want to write a formula that will check a condition, and if that condition is met, then I want to paste a specific line of text in a different cell. I should note that I do not want the ...
2
votes
7
answers
209k
views
How do you add extra value to a cell that already has an existing number in it
I find this hard to explain, but I'm stocktaking beers, spirits, and liqueurs and I want to keep one column for my ullage count for each product. However, as I go through the days, I often come across ...
1
vote
1
answer
2k
views
Call on a cell in Excel up to the first semi-colon
I am using the =CONCATENATE function, and one of the cells I'm calling on I only want to take the value in the cell up to the first semi-colon. Is this possible, and if so how?
0
votes
2
answers
121
views
Excel Formula For Re-arranging Numbers
I have got a bit stuck with trying to make a formula in Excel, and last time I asked a question here I got great help, I have come back again. At the moment I have column A with sets of product id's. ...
1
vote
4
answers
9k
views
Create a hyperlink from URL and title in Excel
I'm trying to construct a hyperlink from a URL and a name in different cells.
a1 = http://www.example.com/
b1 = example
c1 = <a href="http://www.example.com/">example</a>
What I want ...
0
votes
3
answers
7k
views
Sum only if any one of six cells have a value
I would like a cell (total) to sum 6 other cells only if one of those 6 cells have a value. If none of the six have a value i do not want anything to appear.