Using SUM + INDEX in array formula
I'm having problems getting an array formula like this to work:
Basically, I have two columns of numbers that represent the row and column
indexes from a data table (ranges Row_Index_List and Col_Index_List). I want
to look up the numbers in the corresponding row/column of range Data_Table,
and return the sum of all returned values.
More detail: Row_Index_List and Col_Index_List are columns of (let's say)
100 cells, and Data_Table is a 7*5 range on the same sheet.
It looks like the combination of SUM(INDEX(..)) doe...How do you copy a cell's content verses it's formula?
I have 2 cells and combined them into a third cell with the following
formula... =a1&" "&b1. I was combining a person's first name (cell 1) with a
person's second name (cell 2) so cell 3 included the first and second name.
Now I want to copy and paste cell 3, but it copies the formula... I need to
paste in the content (first and second name) not the formula.
You could use a macro see JOIN macro on it's page
not what you actually asked because if would change
column A with the concatenated const...Can I show a reference value with a string?
For a single field, can I show a refernce value (ex: =B2+B3)
and then follow up some strings?
the final result looks like this:
The price is 20
Thank you very much.
="The price is "&B2+B3
goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838
View this thread: http://www.excelforum.com/showthread.php?threadid=486137
If you want to use a format like currency you need to use the TEXT function
="The price is &q...formatting cell numbers
I've tried everything I can think of and still can't get a cell to hold a
whole number. I've tried the format number with no decimal, using general
number, and custom. Yet it all returns less than whole numbers. Any ideas?
Could a setting in MS Money cause this problem?
On Thu, 2 Oct 2008 19:04:01 -0700, DRKENNE <DRKENNE@discussions.microsoft.com>
>I've tried everything I can think of and still can't get a cell to hold a
>whole number. I've tried the format number with no decimal, using general
>number, and custom. Yet it all returns l...Tricky question about pasting references
I have a group of cells that are right next to each other. Call these,
The cells reference cells in another sheet, but the cells they are
referencing are not necessarily adjacent. For example, A1-D1 will
contain (in order) references to Worsheet!C3,Worsheet!D3, Worksheet!D3,
I'd like to start a new worksheet where the arrangement of the
references is maintained, except that I am now referencing a different
worksheet where the referenced cells are moved to the right.
For example, cells C3, D3, D3 and E3 are now NewWorksheet!E3,
NewWorksheet!F3, NewWorksheet!F3...CF to Color-Fill Row B:G w/Specific Text in One of the Cells
I'm in Excel 07 and I have a worksheet which is populated from another
worksheet, INDEX-formulas in all cells. Range is B2:G25. Column C gets
populated with either M or F. I'm looking for a CF method so that if there's
specifically an exact M in row C (not just an M in any word) that the whole
row turns a color.
The Fs need to do it too but change a different color and I'm guessing I'd
just use the same formula but have it look for Fs and change the Fill color,
Can someone please help me out with a formula for that? Please.
Thank...combining related data into one row of data
If I have a table like this with fields and data like this:
Acct - Name ------ value1 - value2 - value3
How can I get a query result like this?
Acct - Name ------ value1 - value2 - value3
123--- Bob, Sue------20------------20--------20
Any assistance greatly appreciated!
On Aug 2, 4:54 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> There is a generic concatenate function with sample usage athttp://www.rogersaccesslibrary.com/OtherLibraries.asp...How to avoid black shade fill on the from cell,when drag-and-drop
Please help me. I tried to drag data from one cell to the other, but the
previuus cell is fill with black shade every time I transfer on excel. How to
...Entering array in single cell
I want to create an array in a single cell and use that as a lookup. I
thought I'd found exactly what I need on Chip Pearson's site which describes
precisely what I'm trying to do but when I follow the instrucions my lookup
formula results in #REF!
If I try and enter the array with Ctrl-Shft-Enter I get the "the formula you
typed contains an error " message.
I'm confused! I must be missing something really obvious. The info from
Chip Pearson's site is below. I'd be grateful for an idiot's guide on
exactly how to get this to work.
Thanks a lot...Target cell=range??
I am compiling a series of tables that calculate the occurences o
multiple data sets that satisfy specific conditions. I must apply thes
tables to ever changing source data (within the same worksheet). Th
formulas that exist within the table are absolute cell references eg
is there a method by which the absolute cell reference could be entere
in another target cell such that
($E$1355:$E$1978>=0) becomes (A1>=0)
w...convert equation to formula
Is there anyway to take an equation written in Equation editor and convert it
to an Excel formula?
You'll have to do that manually.
And it is very interesting work. :)
"DW in SF" <DW in SF@discussions.microsoft.com> wrote in message
> Is there anyway to take an equation written in Equation editor and convert
> to an Excel formula?
I just Used the Equation Editor to record a Chemical Equation so I doubt that
the editor can do this.
However there is a "MATHFORMAT" that might do some ...Combining cell values
I have a list which has companyname and contracts numbers in column A & B
Company Name Contract Number
What I could like to do is to write a formula or a macro to combine all the
contracts numbers for a company into a single cell
BBB 888088, 888333
Can this be done?
Pls try this formulae in column C =A1&" "&B1
Note : " " is for spacing
Message...vlookup -- table_array as reference to named range
I have price sheets coming from several sources with the same format and
products, but containing difference prices.
I am trying to build a dataset (table) from these sources which I could then
link to Access for querying.
Given each price sheet source is a seperate worksheet in a workbook, I have
given each sheet the named range of its pricing rate. For example, RATE_165
represents the pricing sheet which contains the prices for the "165" rate;
RATE_180 represents the pricing sheet which contains the prices for the "180"
I want to be able to...Indirect function
I struggle to create a macro with INDIRECT function that would jump to
different cells. What cell I want to jump to depends on a currently selected
cell. This means I need to put a relative reference into the function.
Here is my function - I need to replace R1C1 expression with a realtive one
(currently selected cell address with the same behavior as R1C1 expression).
How can I do that?
Application.Goto Reference:= _
Whatever I do I get follo...merge cells with a twist
A B C
joe @ domain.com
trying to merge cells in a row so that column a(joe) b(@) c(domain.com) and
that result is firstname.lastname@example.org
(No private emails please)
"jd" <jdumont@@novuscom.net> wrote in message
>A B C
> joe @ domain.com
> trying to merge cells in a row so that column a(joe) b(@) c(domain.com)
> that result is email@example.com
> Thank you.
I've following information wanted to group by first
column and show the sum of quantities:
a b c
---- ----- ------
The desired result:
a b c
---- ----- ------
123 10.8 75 -> sum(10.8+20.2+44)
124 2 58 -> sum(2+11+45+0)
note: I don't want to write sum(a1:a3) , ... because
it depends on the number of rows has the same value.
any help would greatly appricieted.
Using M2, Opera's revolutionary e-mail c...show first few letters of a column in another cell
I know how to use left to show the first character of a particular column in
What I need to do is show the first seven characters.
Column A has a value of "McDonald"
I want Column B to show "McDonal"
=LEFT(A1,7) if A1 is your reference cell.
"Glenn" <firstname.lastname@example.org> wrote in message
>I know how to use left to show the first character of a particular column
> another column.
> What I need to do is show the first seven characters.
>...Excel formula #10
In one cell I have hours mins i.e. 7:30. I would like the=20
formula for another cell to calculate cost of this amount=20
of time, for example 7:30 @ =A330/hour. How would I express=20
that as a formula.
I have tried straight forward 7:30*30, doesn't work. Any=20
where A1 stores your time.<format the resulting cell as number
> In one cell I have hours mins i.e. 7:30. I would like the
> formula for another cell to calculate cost of this amount
> of time, for example 7:30 @ �30/hour. How would I ex...relative path...
Using VC++6, I have created an MFC application. My .exe utilizes an
outside folder named 'MyFolder'. Presently, I have used the following
code in my .exe to access 'MyFolder':
CString InitialPath="C:\\Documents and Settings\\Main\\Desktop\
'MyFolder' is located in the same folder as my '.exe' What relative
path could I use so '.exe' can access 'MyFolder'?
Thanks in advance,
"RAB" <email@example.com> ha scritto nel messaggio
news:firstname.lastname@example.org...How do i get excel to format a cell for a negative result.
Can someone please tell me how i can get excel to format the cell when the
result is negative.
eg 2 - 3 = -1 therefore the cell would turn red.
in menu format-cells-number-
choose whichever format for negative n;umber
Cisco <Cisco@discussions.microsoft.com> wrote in message
> Can someone please tell me how i can get excel to format the cell when the
> result is negative.
> eg 2 - 3 = -1 therefore the cell would turn red.
Select the range where you want this t apply, click on Fo...How to add column(s) that show Yes or No indicating whether that record came from a related table
I had to merge three tables together, and now a request has been made
to show (in the merged flat file) in which report the data exists. So
I will be adding three columns, and there needs to be a streaming Yes
or No in each column, indicating whether the Person's name matches to
Is there a way to do this? I am stumped, but mainly b/c I am new to
Access and am learning how to connect some of these queries to result
in the desired output.
Any suggestions would be appreciated.
> I had to merge three tables together
Why? As in why would you want/need to do that? That is comp...inserting $ at every cell mentioned in a formula
I have several long formula's in different cells and I would like t
add a $ sign to every cell mentioned in those formula's. My question i
how can I do this in an easy way, manually filling in these signs wil
take ages, please help!
Message posted from http://www.ExcelForum.com
> I have several long formula's in different cells and I would like to
> add a $ sign to every cell mentioned in those formula's. My question is
> how can I do this in an easy way, manually filling in these signs will
> take ages, please help!
Maybe w...contents in cells are not visible when word wrap is selected
It appears the profile or standard template for excel has been changed on my
machine. Spreadsheets I worked on earlier this week that contained cells
with word wrap as well as new spreadsheets I create have the same problem...
When using word wrap, the contents of the cell "disapear" but are still
visible in the formula bar. I've removed all formating and unchecked any
cell formats with no success. I've checked all the file, edit, view, ect.
settings for anything that appears to be unusual. Any sugestions would be
appreciated... my IT department cannot look at my ...color formatting of partial text of a cell in excel in bulk
i want to make a cell with multicolor text......... how to do it in
bulk(e.g as we do it in replace all)
If the text displayed in the cell is the result of a formula in that cell,
you cannot have more than one color. If your data is a pure text entry, you
can have multi-colored text, but you will need to use a macro to do more
than one cell at a time. If your cell entries are second condition (pure
text), then give us more information about what you want to do.
"chetan" <email@example.com> wrote in message
news:8c6b5711-0808-4f53-8462-94b08d180d91@z16g2000...cell.value comply with cell.value before and cell.value after
In column C which is sorted based on column D, groups of words
emerge. Occasionally a
deviant word occurs within such a group of uniform words, eg.:
row column C
Is there some way I can make 'ord' like 'Word' and loop through the
whole of Column C to fix similar situations.
The condition is that ord lies between two similar words, 'Word'. When
row 7 starts with a new group of Words, eg From, this From word will
be left untouched.