|
Formula that will recongize text
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) |
Formula that will recongize text
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) |
Formula that will recongize text
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) |
| All times are GMT +1. The time now is 02:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com