I have a spreadsheet with one page for my report and another page that holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)

The problem is my formula only recognizes numbers and the data contains
numbers and text.

You can't SUM text so I do not understand what it is that you are trying to
do. Can you describe what it is gthat you want to do in other words?


--
Regdads


Sandy

Replace@mailinator with @tiscali.co.uk


"sweetsue516" wrote in message
...
I have a spreadsheet with one page for my report and another page that
holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)

Sandy,

I need the formula to tell me what is in the cell.

Example:

On sheet one Column A1:25 list my funds. Column B1:25 list their
performance for the past month.

On sheet two I download the information to update the performance on a
regular basis.

The sumif formula looks up my fund on sheet one then looks it up on sheet
two to load the performance on sheet one.

Let me know if I explained this well enough or not and thank you for your
help.

"sweetsue516" wrote:

I have a spreadsheet with one page for my report and another page that holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)

Hi sweetsue,

Mmmm...... I am not posh enough to have a list of funds <g but let us
see:

I am assuming that:
sheet two is named "Morning Star"

the list of fund names in sheet one is in A1:A25
the list of transferred updates is in sheet one in B1:B25

'Morning Star' also has a list of funds in A1:A25
'Morning Star' has the downloaded updates in B1:B25
the updates are text values like +30 or -50 etc

You wish to transfer the latest updates from 'Morning Star' B1:B25 to sheet
one B1:B25

I the above is correct then I would use INDEX/MATCH in sheet one:

=--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning Star'!$A$1:$A$25,0))
and copy it down from B1 to B25

This looks down 'Morning Star' A1:A25 to find a Matching name with sheet one
cell A1, then indexes down 'Morning Star' B1:B25 the same number of cells to
get the update. The update will be a text value and so to change it onto a
number I have added a double negative at the start to convert the text
number into a number.

If you want the value of the fund B1:B25 to be automatically updated by the
amount of update in 'Morning Star' B1:B25 then set Iteration to 1 and use
the formula:

=B1+(--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning
Star'!$A$1:$A$25,0)))
and copy it down B1:B25

If I am completely off the wall then post back and perhapd one of our posh
friend who look after their own funds in Excel will jump in with a better
solution.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

Oh yes! Always try something new on a COPY of your spreadsheet.........
just in case!


"sweetsue516" wrote in message
...
Sandy,

I need the formula to tell me what is in the cell.

Example:

On sheet one Column A1:25 list my funds. Column B1:25 list their
performance for the past month.

On sheet two I download the information to update the performance on a
regular basis.

The sumif formula looks up my fund on sheet one then looks it up on sheet
two to load the performance on sheet one.

Let me know if I explained this well enough or not and thank you for your
help.

"sweetsue516" wrote:

I have a spreadsheet with one page for my report and another page that
holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)

=B1+(--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning
Star'!$A$1:$A$25,0)))
and copy it down B1:B25


No! I wouldn't do that because it will update every time that the workbook
recalculates.

I'm not really sure that I understand exactly what it isn you want to do.
Can you explain a bit more please?
Do you want to download the updates and have the value of the funds
automatically change?

--
Regards

Sandy

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Hi sweetsue,

Mmmm...... I am not posh enough to have a list of funds <g but let us
see:

I am assuming that:
sheet two is named "Morning Star"

the list of fund names in sheet one is in A1:A25
the list of transferred updates is in sheet one in B1:B25

'Morning Star' also has a list of funds in A1:A25
'Morning Star' has the downloaded updates in B1:B25
the updates are text values like +30 or -50 etc

You wish to transfer the latest updates from 'Morning Star' B1:B25 to
sheet
one B1:B25

I the above is correct then I would use INDEX/MATCH in sheet one:

=--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning Star'!$A$1:$A$25,0))
and copy it down from B1 to B25

This looks down 'Morning Star' A1:A25 to find a Matching name with sheet
one
cell A1, then indexes down 'Morning Star' B1:B25 the same number of cells
to
get the update. The update will be a text value and so to change it onto
a
number I have added a double negative at the start to convert the text
number into a number.

If you want the value of the fund B1:B25 to be automatically updated by
the
amount of update in 'Morning Star' B1:B25 then set Iteration to 1 and use
the formula:

=B1+(--INDEX('Morning Star'!$B$1:$B$25,MATCH(A1,'Morning
Star'!$A$1:$A$25,0)))
and copy it down B1:B25

If I am completely off the wall then post back and perhapd one of our posh
friend who look after their own funds in Excel will jump in with a better
solution.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

Oh yes! Always try something new on a COPY of your spreadsheet.........
just in case!


"sweetsue516" wrote in message
...
Sandy,

I need the formula to tell me what is in the cell.

Example:

On sheet one Column A1:25 list my funds. Column B1:25 list their
performance for the past month.

On sheet two I download the information to update the performance on a
regular basis.

The sumif formula looks up my fund on sheet one then looks it up on sheet
two to load the performance on sheet one.

Let me know if I explained this well enough or not and thank you for your
help.

"sweetsue516" wrote:

I have a spreadsheet with one page for my report and another page that
holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)

Sweetsue,

if the text in N3 is the same as the text in A1:A25, then you shouldn't have
a problem. Obviously, if there is text in B1:B25 then the sum won't work so
you need to extract the text out of the performance values.

Can you provide an example of the data on the Morning Star sheet and what is
in field N3?

Rick

"sweetsue516" wrote:

I have a spreadsheet with one page for my report and another page that holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)

And while you're at it, make sure that the B array is the same length as the
A array. At the moment, one is 25 long and the other 26.
--
David Biddulph

"Rick" wrote in message
...
Sweetsue,

if the text in N3 is the same as the text in A1:A25, then you shouldn't
have
a problem. Obviously, if there is text in B1:B25 then the sum won't work
so
you need to extract the text out of the performance values.

Can you provide an example of the data on the Morning Star sheet and what
is
in field N3?

Rick

"sweetsue516" wrote:

I have a spreadsheet with one page for my report and another page that
holds
my data that is updated on a regular basis so I automated it.

The problem is my formula only recognizes numbers and the data contains
numbers and text. I use sumif because there is not any repeated data.

Here is my formula:

=SUMIF('Morning Star '!$A$1:$A$25,$N$3,'Morning Star '!B$1:B$26)