#### copying a formula down wards

```=INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))

the above formula works in that it shows the first match.

As there could be mulitpule records what do I need to do to so when I copy
it down it will bring the next match or 0 if no further matches in the table
it is searching?

Many thanks

UKMAN1
```
 0
Utf
4/15/2010 4:08:01 PM
excel.worksheet.functions 4936 articles. 2 followers.

10 Replies
586 Views

Similar Articles

[PageSpeed] 20

```Hi,

Try this ARRAY formula. ARRAY enter it and it will return the first match,
drag down for the second etc. It will return an error if there isn't a second
match so you could wrap the whole thing =isserror(formula etc

=OFFSET(IF(ROWS(B\$7:B7)<=COUNTIF(Proj_code,\$E\$1),INDEX(\$F\$7:\$F\$198,SMALL(IF(Proj_code=\$AQ\$6,ROW(Proj_code)-ROW(\$E\$1)+1),ROWS(B\$7:B7))),""),-6,0)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

"UKMAN" wrote:

> =INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
>
> the above formula works in that it shows the first match.
>
> As there could be mulitpule records what do I need to do to so when I copy
> it down it will bring the next match or 0 if no further matches in the table
> it is searching?
>
> Many thanks
>
> UKMAN1
```
 0
Utf
4/15/2010 5:02:05 PM
```Select the range of cells in which you want the results, say L18:L25.
Assuming you have numeric values in D4:D11 and the corresponding
values to return in E4:E11, enter the following array formula and
press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
formula into an array of cells, rather than a single cell.

=IF(ROW()-ROW(L\$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b",E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))),0)

Change the reference to L\$18 to the first cell in the results range
that contains the formula.  Change the "b" to the value you want to
look up in D4:D11.

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
<UKMAN@discussions.microsoft.com> wrote:

>=INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
>
>the above formula works in that it shows the first match.
>
>As there could be mulitpule records what do I need to do to so when I copy
>it down it will bring the next match or 0 if no further matches in the table
>it is searching?
>
>Many thanks
>
>UKMAN1
```
 0
Chip
4/15/2010 5:21:10 PM
```Chip, thanks for the reply but :(

below is the formula and my changes but it says too many arquements?? excel
hights the first ",0".

For clarity in cell ref in the hope I have done the correct changes:

AN\$9 is the first line/cell for the report results
f7:f198 is the range of value in AQ6 (both text)is to match
b7:b198 is where the value (i.e. PC01) to be return to an9

IF(ROW()-ROW(AN\$9)<COUNTIF(F\$7:F\$198,\$AQ\$6),LARGE(IF(F\$7:F\$198,\$AQ\$6,B\$7:B\$198,0),ROW(INDIRECT("1:"&COUNTIF(F\$7:F\$198,\$AQ\$6)))),0)

I do thank you for your help as I am trying to understand the nore indepth
formulas

UKMAN1

"Chip Pearson" wrote:

> Select the range of cells in which you want the results, say L18:L25.
> Assuming you have numeric values in D4:D11 and the corresponding
> values to return in E4:E11, enter the following array formula and
> press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
> formula into an array of cells, rather than a single cell.
>
> =IF(ROW()-ROW(L\$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b",E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))),0)
>
> Change the reference to L\$18 to the first cell in the results range
> that contains the formula.  Change the "b" to the value you want to
> look up in D4:D11.
>
> This is an array formula, so you MUST press CTRL SHIFT ENTER rather
> than just ENTER when you first enter the formula and whenever you edit
> it later. If you do this correctly, Excel will display the formula in
> the formula bar enclosed in curly braces { }. You don't type in the
> braces; Excel puts them there automatically. The formula will not work
> correctly if you do not enter it with CTRL SHIFT ENTER. See
>
>
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> 	Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
> <UKMAN@discussions.microsoft.com> wrote:
>
> >=INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
> >
> >the above formula works in that it shows the first match.
> >
> >As there could be mulitpule records what do I need to do to so when I copy
> >it down it will bring the next match or 0 if no further matches in the table
> >it is searching?
> >
> >Many thanks
> >
> >UKMAN1
> .
>
```
 0
Utf
4/15/2010 6:55:03 PM
```Try this...

Enter this formula in A1. This will return the count of records that meet
the criteria.

=COUNTIF(F\$7:F\$198,AQ\$6)

Enter this array formula** in B1 and copy down until you get 0s.. This will
extract the records that meet the criteria.

=IF(ROWS(B\$1:B1)>A\$1,0,INDEX(Proj_code,SMALL(IF(F\$7:F\$198=AQ\$6,ROW(Proj_code)),ROWS(B\$1:B1))-MIN(ROW(Proj_code))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

"UKMAN" <UKMAN@discussions.microsoft.com> wrote in message
> =INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
>
> the above formula works in that it shows the first match.
>
> As there could be mulitpule records what do I need to do to so when I copy
> it down it will bring the next match or 0 if no further matches in the
> table
> it is searching?
>
> Many thanks
>
> UKMAN1

```
 0
T
4/15/2010 10:02:58 PM
```Too many arguments is referring to this part of your formula:

IF(F\$7:F\$198,  \$AQ\$6,  B\$7:B\$198,  0)

because an IF function only requires 3 arguments.  I suspect you meant:

IF(F\$7:F\$198=\$AQ\$6,B\$7:B\$198,0)

"UKMAN" <UKMAN@discussions.microsoft.com> wrote in message
> Chip, thanks for the reply but :(
>
> below is the formula and my changes but it says too many arquements??
> excel
> hights the first ",0".
>
> For clarity in cell ref in the hope I have done the correct changes:
>
> AN\$9 is the first line/cell for the report results
> f7:f198 is the range of value in AQ6 (both text)is to match
> b7:b198 is where the value (i.e. PC01) to be return to an9
>
> IF(ROW()-ROW(AN\$9)<COUNTIF(F\$7:F\$198,\$AQ\$6),LARGE(IF(F\$7:F\$198,\$AQ\$6,B\$7:B\$198,0),ROW(INDIRECT("1:"&COUNTIF(F\$7:F\$198,\$AQ\$6)))),0)
>
> I do thank you for your help as I am trying to understand the nore indepth
> formulas
>
> UKMAN1
>
>
> "Chip Pearson" wrote:
>
>> Select the range of cells in which you want the results, say L18:L25.
>> Assuming you have numeric values in D4:D11 and the corresponding
>> values to return in E4:E11, enter the following array formula and
>> press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
>> formula into an array of cells, rather than a single cell.
>>
>> =IF(ROW()-ROW(L\$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b",E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))),0)
>>
>> Change the reference to L\$18 to the first cell in the results range
>> that contains the formula.  Change the "b" to the value you want to
>> look up in D4:D11.
>>
>> This is an array formula, so you MUST press CTRL SHIFT ENTER rather
>> than just ENTER when you first enter the formula and whenever you edit
>> it later. If you do this correctly, Excel will display the formula in
>> the formula bar enclosed in curly braces { }. You don't type in the
>> braces; Excel puts them there automatically. The formula will not work
>> correctly if you do not enter it with CTRL SHIFT ENTER. See
>>
>>
>>
>> Cordially,
>> Chip Pearson
>> Microsoft Most Valuable Professional,
>> Excel, 1998 - 2010
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
>> <UKMAN@discussions.microsoft.com> wrote:
>>
>> >=INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
>> >
>> >the above formula works in that it shows the first match.
>> >
>> >As there could be mulitpule records what do I need to do to so when I
>> >copy
>> >it down it will bring the next match or 0 if no further matches in the
>> >table
>> >it is searching?
>> >
>> >Many thanks
>> >
>> >UKMAN1
>> .
>>

```
 0
Steve
4/16/2010 10:43:55 AM
```Mr T,

many thanks and it worked perfectly.

To all others many thanks as well for your help.

Regards

UKMAN1

"T. Valko" wrote:

> Try this...
>
> Enter this formula in A1. This will return the count of records that meet
> the criteria.
>
> =COUNTIF(F\$7:F\$198,AQ\$6)
>
> Enter this array formula** in B1 and copy down until you get 0s.. This will
> extract the records that meet the criteria.
>
> =IF(ROWS(B\$1:B1)>A\$1,0,INDEX(Proj_code,SMALL(IF(F\$7:F\$198=AQ\$6,ROW(Proj_code)),ROWS(B\$1:B1))-MIN(ROW(Proj_code))+1))
>
> ** array formulas need to be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
> key then hit ENTER.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "UKMAN" <UKMAN@discussions.microsoft.com> wrote in message
> > =INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
> >
> > the above formula works in that it shows the first match.
> >
> > As there could be mulitpule records what do I need to do to so when I copy
> > it down it will bring the next match or 0 if no further matches in the
> > table
> > it is searching?
> >
> > Many thanks
> >
> > UKMAN1
>
>
> .
>
```
 0
Utf
4/16/2010 11:26:01 AM
```Steve,

UKMAN1

"Steve Dunn" wrote:

> Too many arguments is referring to this part of your formula:
>
> IF(F\$7:F\$198,  \$AQ\$6,  B\$7:B\$198,  0)
>
> because an IF function only requires 3 arguments.  I suspect you meant:
>
> IF(F\$7:F\$198=\$AQ\$6,B\$7:B\$198,0)
>
>
>
> "UKMAN" <UKMAN@discussions.microsoft.com> wrote in message
> > Chip, thanks for the reply but :(
> >
> > below is the formula and my changes but it says too many arquements??
> > excel
> > hights the first ",0".
> >
> > For clarity in cell ref in the hope I have done the correct changes:
> >
> > AN\$9 is the first line/cell for the report results
> > f7:f198 is the range of value in AQ6 (both text)is to match
> > b7:b198 is where the value (i.e. PC01) to be return to an9
> >
> > IF(ROW()-ROW(AN\$9)<COUNTIF(F\$7:F\$198,\$AQ\$6),LARGE(IF(F\$7:F\$198,\$AQ\$6,B\$7:B\$198,0),ROW(INDIRECT("1:"&COUNTIF(F\$7:F\$198,\$AQ\$6)))),0)
> >
> > I do thank you for your help as I am trying to understand the nore indepth
> > formulas
> >
> > UKMAN1
> >
> >
> > "Chip Pearson" wrote:
> >
> >> Select the range of cells in which you want the results, say L18:L25.
> >> Assuming you have numeric values in D4:D11 and the corresponding
> >> values to return in E4:E11, enter the following array formula and
> >> press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
> >> formula into an array of cells, rather than a single cell.
> >>
> >> =IF(ROW()-ROW(L\$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b",E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))),0)
> >>
> >> Change the reference to L\$18 to the first cell in the results range
> >> that contains the formula.  Change the "b" to the value you want to
> >> look up in D4:D11.
> >>
> >> This is an array formula, so you MUST press CTRL SHIFT ENTER rather
> >> than just ENTER when you first enter the formula and whenever you edit
> >> it later. If you do this correctly, Excel will display the formula in
> >> the formula bar enclosed in curly braces { }. You don't type in the
> >> braces; Excel puts them there automatically. The formula will not work
> >> correctly if you do not enter it with CTRL SHIFT ENTER. See
> >>
> >>
> >>
> >> Cordially,
> >> Chip Pearson
> >> Microsoft Most Valuable Professional,
> >> Excel, 1998 - 2010
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >> On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
> >> <UKMAN@discussions.microsoft.com> wrote:
> >>
> >> >=INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
> >> >
> >> >the above formula works in that it shows the first match.
> >> >
> >> >As there could be mulitpule records what do I need to do to so when I
> >> >copy
> >> >it down it will bring the next match or 0 if no further matches in the
> >> >table
> >> >it is searching?
> >> >
> >> >Many thanks
> >> >
> >> >UKMAN1
> >> .
> >>
>
```
 0
Utf
4/16/2010 11:28:01 AM
```Mike,

UKMAN1

"Mike H" wrote:

> Hi,
>
> Try this ARRAY formula. ARRAY enter it and it will return the first match,
> drag down for the second etc. It will return an error if there isn't a second
> match so you could wrap the whole thing =isserror(formula etc
>
> =OFFSET(IF(ROWS(B\$7:B7)<=COUNTIF(Proj_code,\$E\$1),INDEX(\$F\$7:\$F\$198,SMALL(IF(Proj_code=\$AQ\$6,ROW(Proj_code)-ROW(\$E\$1)+1),ROWS(B\$7:B7))),""),-6,0)
>
> This is an array formula which must be entered by pressing CTRL+Shift+Enter
> and not just Enter. If you do it correctly then Excel will put curly brackets
> around the formula {}. You can't type these yourself. If you edit the formula
> you must enter it again with CTRL+Shift+Enter.
>
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "UKMAN" wrote:
>
> > =INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
> >
> > the above formula works in that it shows the first match.
> >
> > As there could be mulitpule records what do I need to do to so when I copy
> > it down it will bring the next match or 0 if no further matches in the table
> > it is searching?
> >
> > Many thanks
> >
> > UKMAN1
```
 0
Utf
4/16/2010 11:28:11 AM
```You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"UKMAN" <UKMAN@discussions.microsoft.com> wrote in message
news:E1C30917-3CAB-4935-879E-3FC0555D09D5@microsoft.com...
> Mr T,
>
> many thanks and it worked perfectly.
>
> To all others many thanks as well for your help.
>
> Regards
>
> UKMAN1
>
> "T. Valko" wrote:
>
>> Try this...
>>
>> Enter this formula in A1. This will return the count of records that meet
>> the criteria.
>>
>> =COUNTIF(F\$7:F\$198,AQ\$6)
>>
>> Enter this array formula** in B1 and copy down until you get 0s.. This
>> will
>> extract the records that meet the criteria.
>>
>> =IF(ROWS(B\$1:B1)>A\$1,0,INDEX(Proj_code,SMALL(IF(F\$7:F\$198=AQ\$6,ROW(Proj_code)),ROWS(B\$1:B1))-MIN(ROW(Proj_code))+1))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
>> SHIFT
>> key then hit ENTER.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "UKMAN" <UKMAN@discussions.microsoft.com> wrote in message
>> > =INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
>> >
>> > the above formula works in that it shows the first match.
>> >
>> > As there could be mulitpule records what do I need to do to so when I
>> > copy
>> > it down it will bring the next match or 0 if no further matches in the
>> > table
>> > it is searching?
>> >
>> > Many thanks
>> >
>> > UKMAN1
>>
>>
>> .
>>

```
 0
T
4/16/2010 2:36:17 PM
```Hi,

You may refer to my article here -
http://office.microsoft.com/en-gb/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"UKMAN" <UKMAN@discussions.microsoft.com> wrote in message
> =INDEX(Proj_code,MATCH(AQ\$6,F\$7:F\$198,0))
>
> the above formula works in that it shows the first match.
>
> As there could be mulitpule records what do I need to do to so when I copy
> it down it will bring the next match or 0 if no further matches in the
> table
> it is searching?
>
> Many thanks
>
> UKMAN1

```
 0
Ashish
4/26/2010 2:41:57 PM

Similar Artilces:

Copying and pasting with a macro
Hi! I posted this up yesterday and am very pleased with the corrections I got. I do have another question though: How can I make it so that the copied information is inserted into sheet 2 and takes on the format of the target cell rather than keeping the original one? Sub Wombat() Sheets("1").Cells(3, 12).Select x = 3 y = 24 Do Until Cells(x, 12).Value = "END" If Cells(x, 12).Value <> "" Then Cells(x, 12).Copy Sheets("2").Cells(y, 1) x = x + 1 y = y + 1 ...

Pivot Table Formulas 06-06-10
I need to create a variance formula for the data selected in a pivot table. My problem is that the data is coming in from one column and I don't know how to distinquish the data by year in the formula. I have put a small selection of my data below. I would like to show the variance between 2009 and 2010 for all months selected in the pivot table. WDS# EA UOM PRICE EXTENDED Month Year 0623864 6 EA 9.73 58.38 Jan 2010 4286879 2 EA 2.784 5.568 Jan 2010 0623864 9 EA 9.73 87.57 Feb 2010 4286879 1 EA 2.784 2.784 Feb 2010 3514738 1 EA 13.72 13.72 Feb 2010 0623864 5 EA 7.85 39....

"Filling Down" Formulas without changing values
I want to copy a formula down to the next 20 cells. When I highlight the cell and the next 20 blank cells below, and hit the "fill down" button, it copies the cells, but also becomes additive. For instance, the D45 changes to D46, D47, etc. Before reformatting recently, that only did as above if I dragged a formula down by grabbing a corner. How do I change the fill-down command so that it only copies-down? Hi change the formula to =\$D\$45 -- Regards Frank Kabel Frankfurt, Germany "Portland Ken" <Portland Ken@discussions.microsoft.com> schrieb im Newsbeitrag n...

Hided formulas appearing with copy/paste
Is there a way to prevent copying hiden&protected formulas (cells) on form ?. When copy/paste (on a different worksheet) is used all the formula appear on, you need only to select the range where the hiden formula are. Because the files may have different names it is impossible to link th formulas to a base ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements If I did Format|C...

copying data validation to another workbook
am i able to copy my data validation cell that i created in one workbook over to a new excel file not associated with the original. I have no problem copying validation from one area to another within a workbook but can't seem to bring the validation commands over to a new file. Just select the column or range on which you are having the Data Validation and give Cntrl+C and Press Cntrl+N to open a new workbook or open the workbook on which do you want to paste the Data Validation. Now select the Column or Range of cells and do Right Click>>Paste Special>>select...

Excel and Notepad: how avoid additional inverted commas after copy and paste?
Hi, If I write a multi-line text in a cell (then go down with alt+enter) and after copy the cell pasting on Notepad, it display before inverted commas and after textual content. Instead, if I select directly the content from the formula bar, it isn't happen. Do exist a way for copy and paste directly from the cell without select from the formula bar? Maybe with a macro? Thank Marco __________________ http://www.idee-regalo.biz/catalogo-stereogrammi-3.html http://www.ghisirds.it/ http://scuo.la/ http://www.righettofabrizio.com/pantografo-taglio-plasma.html I used the PutOnClipboard rout...

Copies of sent mail go to my Sent Items folder instead of other mailbox Sent folder.
Experts, I have two Exchange 2000 mailboxes, of which I use the Outlook client to access. In this other mailbox, from which I occasionally send email, I noticed copies of sent mail go to my own mailbox Sent Items folder instead of the other mailboxes Sent Items folder. Is this a feature that can be changed? -- Regards, Spin ...

copy of users emails
how can I as the administrator get copies of a user's "sent" or "received" email put into a copy folder? Thanks Raul Rego rrego@njpies.org Do you want "all" users, or just a few? If it is everyone, then you can simply enable Journaling. On the properties of the Mailbox store, there is an option to archive a copy of all messages sent or received by the store to a specific mailbox. Of course, you need to be aware that this will be absolutely UGLY, as it just creates a copy of all messages and sticks them in the same Inbox. If you want a better sol...

Formula #40
I need a formula that would allow me to type a cost center and once I type the cost center the cost that I type in a seperate column will roll into several places. Hi! Assume you enter the cost in B1 and also want that cost to populate cells A20,F15 and H2. In A20,F15 and H2 enter this formula: =IF(B1="","",B1) Biff >-----Original Message----- >I need a formula that would allow me to type a cost center and once I type >the cost center the cost that I type in a seperate column will roll into >several places. >. > Thanks Bif!! Here is a anot...

Copy Contacts from Outlook 07 to Outlook 07 ??
I'm running Outlook 07 in Win XP on one partition and in Vista on the other. (Dual boot Hdd-0) How can I copy my Outlook 07 Contacts from one to the other? What is the file extension? Please help I'm frustrated enough as it is with Vista. Why copy them? simply point Outlook 2007 to your .pst file in the other partition. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Dave K wrote: | I'm running Outlook 07 in Win...

Can you copy an Excel cell without the automatic line break?
Whenever you copy a cell (CTRL+C), Excel automatically appends a line break at the end. This makes it impossible to paste the cell directly into a single-line text field. Can you copy an Excel cell without the automatic line break? Is there a hidden line break in the cell that I can remove? Is there way I can get Excel to place the result directly in the clipboard without the automatic line break? Is there a way I can get VB to interface with Excel so that I can get a copyable field that doesn't contain an automatic line break? I don't know if you can stop the way excel works, b...

basket with fruits : numbers and formula
Hello all ! I m back with another pb... I have a basket with fruits : 5 apples, 2 oranges, 9 tomatoes. Some of them are ripe, others go rotten... How to get a chart with : the number of rotten apples and the number of ripe tomatoes ?? Before trying, I thought it was easy... :-) Thanks for your help !! rb To create a chart, you could enter the data in columns, e.g.: Ripe Rotten apples oranges tomatoes Type the numbers for each category. Then select a cell in the list, and use the Chart Wizard to create a chart. rbus wrote: > Hello all ! > I m back with another pb... ...

Need help with formulae comparing date-based numbers
I'm trying to write a Excel formula that enters an integer value for a cell, the value representing an age in days. To get this value, I'm comparing values in two cells. One cell may or may not have a value, but it establishes the upper limit for the integer value. The problem is this: I have tried using both the IF and DATEDIF functions, and neither returns an integer value. Both give me date values, such as: 4/29/1900. (Kudos to the programmer who sent my data aging in reverse; perhaps Einstein could learn something from this relativity.) The syntax in my formulas (bel...

I just got a new computer and upgraded to Outlook 2003, and I check email from 3 different accounts (all POP3). I have it set to leave copies of the message on the server, but every time I check for new mail, each message gets downloaded again. I've never had this problem before. Any ideas? Any help is greatly appreciated. Are you using dialup or broadband? If outlook gets disconnected before sending the final commands, it doesn't know it already downloaded the messages and redownloads them. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours ...

legal copies
I have recently purchased i new legal copy of office 2007 but it keeps telling methatthe copy is not legal how do i stop this hasppening when i know i purchased a legal copy you should contact the seller. the software you have may been activated on a different machine. -- db·´¯`·...¸><)))º> DatabaseBen, Retired Professional - Systems Analyst - Database Developer - Accountancy - Veteran of the Armed Forces - @Hotmail.com - nntp Postologist ~ "share the nirvana" - dbZen ~~~~~~~~~~~~~~~ > > "Auz&qu...

Can inserted rows automatically include existing worksheet formula
I frequently need to insert rows to a worksheet which contains a formula column. Is it possible to insert a row without having to manually copy down the formula from the previous row? Thank-you! Have a look at David McRitchie's site for a macro to insert rows and maintain formulas. http://www.mvps.org/dmcritchie/excel/insrtrow.htm Gord Dibben Excel MVP On Tue, 20 Sep 2005 11:49:03 -0700, "tgdavis" <tgdavis@discussions.microsoft.com> wrote: >I frequently need to insert rows to a worksheet which contains a formula >column. Is it possible to insert a row wi...

Help with autofilling a formula. TIA
Ok, I have a Excel sheet that is 42 row by 28 columns. The Rows each reference a kH (carbonate hardness) value from 0.5 to 20 in 0.5 degree steps. the Columns each reference a pH value from 6.0 to 8.6 in .1 degree steps. The formula I'm using 3 * KH * 10( 7-pH ) calculates the CO2 level of the water. I would like to be able to just somehow fill all these cells with the formula that references the correct row and column and not have to enter the C and R values in each formula. I'm sure this is possible but I'm not that experienced of an Excel user. Thanks. Hi Cannibul I assum...

Copy Last 13 items #2
I have 2 worksheets no1 is a continuous row after row of monthly listings i.e. date, amount, no of items from 1999 to date. Worksheet no2 just shows the last 13 months. I would like a formula to go in worksheet 2 so it will automatically update its self after the current months figures have been added to the bottom of worksheet no1. I was told =offset(a1,count(a;a)-1,0) would give me the last item in a row less 1, but have not been able to get it to work successfully, Any other suggestions gratefully received. Bob Sorry originally posted this to programming board by mistake - not cross p...

Formulas don't work in certain cells #3
One formula is: =SUM(C10:C15) another: =SUM(O16/6363) another: =SUM(C19:C36) these same formulas work in the cells right next to these wit different cell references. However, no matter which cell I put th above formulas in, the result is the same - either blank with a dash i the cell or zero. I checked all the cells that the formulas refer t and none has any errors. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View...

cell reference in a formula is called
Please confirm the correct answer to the below question cell reference in a formula is called? I'm not sure what you are asking for. In a user defined function in VBA, Application.Caller will return a Range reference to the cell which called the function. In a formula, ADDRESS(ROW(),COLUMN()) will return the address of the cell containing the function. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "???????????" <???????????@discussions.microsoft.com> wrote in message news:22EA36DA-A680-4414-87DF-EBD3BA96820...

Formula for different sheets
Hi I would like to create a formula that relates to different sheets in m workbook when a selection to it's name is made. The book contains 13 sheets. Sheet 1 - sheet where I do the lookups etc Sheets 2 to 13 are the 12 months of the year. Could I create a formula in Sheet 1 that would automatically refer to Sheet with the name of the month I'm looking for if I created a dro down list. Eg, Sheet 1 in A2 = drop down list with month names A4 - a lookup or IF formula finding data from one of the other sheets The data in each sheet is exactly the same. Many thanks Joe -- Message p...

Copy cell appearance but not conditional formatting
I have a spreadsheet with some conditional formatting. How can I cop the appearance of the cells without the formatting condition -- Message posted from http://www.ExcelForum.com Hi AFAIK this is not possible >-----Original Message----- >I have a spreadsheet with some conditional formatting. How can I copy >the appearance of the cells without the formatting condition? > > >--- >Message posted from http://www.ExcelForum.com/ > >. > Hi! I suggest you copy the formatting and then remove the conditiona formatting. This latter should be fairly quick, using t...

Using VBA to copy a chart into Powerpoint
Does anyone know how to do this? I've got to the point where I have powerpoint open but I can't create new slide. The following opens powerpoint then falls over on the lin "pres.slides.add 1 , pplayoutblank". Set ppt = CreateObject("powerpoint.application") ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy ppt.Visible = True Set pres = ppt.presentations.Add pres.slides.Add 1, pplayoutblank ppt.ActiveWindow.View.Past -- Message posted from http://www.ExcelForum.com Ed - Check out the example...

Confirm File Replace when copying mails to network drive?
Is there a "trick" to overcome the problem that arises when copying a mail to a network drive that has the same name as an existing mail already in the drive? I save useful mails onto my network rather than only in Outlook archives, but as each project has the same name I very often get the "Confirm File Replace" messge that says "this folder already contains a file named xyx. Would you like to replace the existing file with this one?" As these are two separate and different mails, I actually need to keep both! It seems that if all mails are sav...

Copy only visible cells after applying "Subtotals"
Hello, in an XL-list I need to copy only a block of subtotals to another area, without the detail rows in between. I believe there once was a command like "paste only visible cells" (or similar), but I can't find a solution. Anybody who knows how to do this ? Thank you in advance, H.G. Lamy You can copy the visible cells. Show just the subtotals select the range to copy Edit|goto (or F5 or ctrl-g)|special|visible cells only Then edit|copy and paste where you want. "H.G. Lamy" wrote: > > Hello, > > in an XL-list I n...