Add numbers accross columns after stripping away text

I have the following data in a spreadsheet:



A1      B1      C1      D1      E1      F1
4.5f    6f       3.5f                 3f       7.25f


I need to be able to add the numbers together to give me 24.25, i.e. strip
the fs away. The numbers will always be less than 10 and the there will only
ever be .25 or.5 or .75 after the number (I don't know whether that is
significant?).


If anyone can show me how to do this I would be very grateful. I have been
messing around with MID and FIND to no avail and then started thinking that
SUMPRODUCT might have to get involved but it all got a bit much for me!
There may be a perfectly simple solution which I have overlooked!



Regards,




Gavin


0
no-one1 (46)
5/20/2005 5:16:07 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
842 Views

Similar Articles

[PageSpeed] 58

Hi Gavin,

One way

=SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"gavin" <no-one@home.com> wrote in message
news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> I have the following data in a spreadsheet:
>
>
>
> A1      B1      C1      D1      E1      F1
> 4.5f    6f       3.5f                 3f       7.25f
>
>
> I need to be able to add the numbers together to give me 24.25, i.e. strip
> the fs away. The numbers will always be less than 10 and the there will
only
> ever be .25 or.5 or .75 after the number (I don't know whether that is
> significant?).
>
>
> If anyone can show me how to do this I would be very grateful. I have been
> messing around with MID and FIND to no avail and then started thinking
that
> SUMPRODUCT might have to get involved but it all got a bit much for me!
> There may be a perfectly simple solution which I have overlooked!
>
>
>
> Regards,
>
>
>
>
> Gavin
>
>


0
bob.phillips1 (6510)
5/20/2005 5:17:28 PM
=SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
if its always "f"

"gavin" <no-one@home.com> wrote in message 
news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
>I have the following data in a spreadsheet:
>
>
>
> A1      B1      C1      D1      E1      F1
> 4.5f    6f       3.5f                 3f       7.25f
>
>
> I need to be able to add the numbers together to give me 24.25, i.e. strip
> the fs away. The numbers will always be less than 10 and the there will 
> only
> ever be .25 or.5 or .75 after the number (I don't know whether that is
> significant?).
>
>
> If anyone can show me how to do this I would be very grateful. I have been
> messing around with MID and FIND to no avail and then started thinking 
> that
> SUMPRODUCT might have to get involved but it all got a bit much for me!
> There may be a perfectly simple solution which I have overlooked!
>
>
>
> Regards,
>
>
>
>
> Gavin
>
> 


0
nh1 (51)
5/20/2005 5:20:01 PM
Thanks so much to Bob and N Harkawat. Both solutions work great and the
speed of reply is unbelievable! I wouldn't have got there on my own so much
appreciated, guys!


Regards,



Gavin


"N Harkawat" <nh@nh.com> wrote in message
news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> if its always "f"
>
> "gavin" <no-one@home.com> wrote in message
> news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> >I have the following data in a spreadsheet:
> >
> >
> >
> > A1      B1      C1      D1      E1      F1
> > 4.5f    6f       3.5f                 3f       7.25f
> >
> >
> > I need to be able to add the numbers together to give me 24.25, i.e.
strip
> > the fs away. The numbers will always be less than 10 and the there will
> > only
> > ever be .25 or.5 or .75 after the number (I don't know whether that is
> > significant?).
> >
> >
> > If anyone can show me how to do this I would be very grateful. I have
been
> > messing around with MID and FIND to no avail and then started thinking
> > that
> > SUMPRODUCT might have to get involved but it all got a bit much for me!
> > There may be a perfectly simple solution which I have overlooked!
> >
> >
> >
> > Regards,
> >
> >
> >
> >
> > Gavin
> >
> >
>
>


0
no-one1 (46)
5/20/2005 5:34:13 PM
Bob,
On reflection can I just ask about HOW this function works? For example, I
have never seen a double hyphen before.


Regards,



Gavin



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:ON5MP$VXFHA.1152@tk2msftngp13.phx.gbl...
> Hi Gavin,
>
> One way
>
> =SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "gavin" <no-one@home.com> wrote in message
> news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > I have the following data in a spreadsheet:
> >
> >
> >
> > A1      B1      C1      D1      E1      F1
> > 4.5f    6f       3.5f                 3f       7.25f
> >
> >
> > I need to be able to add the numbers together to give me 24.25, i.e.
strip
> > the fs away. The numbers will always be less than 10 and the there will
> only
> > ever be .25 or.5 or .75 after the number (I don't know whether that is
> > significant?).
> >
> >
> > If anyone can show me how to do this I would be very grateful. I have
been
> > messing around with MID and FIND to no avail and then started thinking
> that
> > SUMPRODUCT might have to get involved but it all got a bit much for me!
> > There may be a perfectly simple solution which I have overlooked!
> >
> >
> >
> > Regards,
> >
> >
> >
> >
> > Gavin
> >
> >
>
>


0
no-one1 (46)
5/20/2005 7:33:53 PM
Can I ask what the "0&" does? I have tried the formula without it and in my
case it still works fine.



Regards,



Gavin



"N Harkawat" <nh@nh.com> wrote in message
news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> if its always "f"
>
> "gavin" <no-one@home.com> wrote in message
> news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> >I have the following data in a spreadsheet:
> >
> >
> >
> > A1      B1      C1      D1      E1      F1
> > 4.5f    6f       3.5f                 3f       7.25f
> >
> >
> > I need to be able to add the numbers together to give me 24.25, i.e.
strip
> > the fs away. The numbers will always be less than 10 and the there will
> > only
> > ever be .25 or.5 or .75 after the number (I don't know whether that is
> > significant?).
> >
> >
> > If anyone can show me how to do this I would be very grateful. I have
been
> > messing around with MID and FIND to no avail and then started thinking
> > that
> > SUMPRODUCT might have to get involved but it all got a bit much for me!
> > There may be a perfectly simple solution which I have overlooked!
> >
> >
> >
> > Regards,
> >
> >
> >
> >
> > Gavin
> >
> >
>
>


0
no-one1 (46)
5/20/2005 7:41:24 PM
Gavin,

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html, it's all
explained there.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"gavin" <no-one@home.com> wrote in message
news:d6ldsf$5ju$1@news6.svr.pol.co.uk...
> Bob,
> On reflection can I just ask about HOW this function works? For example, I
> have never seen a double hyphen before.
>
>
> Regards,
>
>
>
> Gavin
>
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:ON5MP$VXFHA.1152@tk2msftngp13.phx.gbl...
> > Hi Gavin,
> >
> > One way
> >
> > =SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "gavin" <no-one@home.com> wrote in message
> > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > > I have the following data in a spreadsheet:
> > >
> > >
> > >
> > > A1      B1      C1      D1      E1      F1
> > > 4.5f    6f       3.5f                 3f       7.25f
> > >
> > >
> > > I need to be able to add the numbers together to give me 24.25, i.e.
> strip
> > > the fs away. The numbers will always be less than 10 and the there
will
> > only
> > > ever be .25 or.5 or .75 after the number (I don't know whether that is
> > > significant?).
> > >
> > >
> > > If anyone can show me how to do this I would be very grateful. I have
> been
> > > messing around with MID and FIND to no avail and then started thinking
> > that
> > > SUMPRODUCT might have to get involved but it all got a bit much for
me!
> > > There may be a perfectly simple solution which I have overlooked!
> > >
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > >
> > > Gavin
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
5/20/2005 8:28:28 PM
In my quick test, if there are empty cells I get an error *without* the "&0"

"gavin" wrote:

> Can I ask what the "0&" does? I have tried the formula without it and in my
> case it still works fine.
> 
> 
> 
> Regards,
> 
> 
> 
> Gavin
> 
> 
> 
> "N Harkawat" <nh@nh.com> wrote in message
> news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> > =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> > if its always "f"
> >
> > "gavin" <no-one@home.com> wrote in message
> > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > >I have the following data in a spreadsheet:
> > >
> > >
> > >
> > > A1      B1      C1      D1      E1      F1
> > > 4.5f    6f       3.5f                 3f       7.25f
> > >
> > >
> > > I need to be able to add the numbers together to give me 24.25, i.e.
> strip
> > > the fs away. The numbers will always be less than 10 and the there will
> > > only
> > > ever be .25 or.5 or .75 after the number (I don't know whether that is
> > > significant?).
> > >
> > >
> > > If anyone can show me how to do this I would be very grateful. I have
> been
> > > messing around with MID and FIND to no avail and then started thinking
> > > that
> > > SUMPRODUCT might have to get involved but it all got a bit much for me!
> > > There may be a perfectly simple solution which I have overlooked!
> > >
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > >
> > > Gavin
> > >
> > >
> >
> >
> 
> 
> 
0
DukeCarey (494)
5/20/2005 8:29:01 PM
Gavin,

It is added to cater for empty cells.

To show it clear one of the cells in your range, it will still work okay.

Now take the &0 out of the formula, and see what happens.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"gavin" <no-one@home.com> wrote in message
news:d6le8p$m3b$1@newsg2.svr.pol.co.uk...
> Can I ask what the "0&" does? I have tried the formula without it and in
my
> case it still works fine.
>
>
>
> Regards,
>
>
>
> Gavin
>
>
>
> "N Harkawat" <nh@nh.com> wrote in message
> news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> > =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> > if its always "f"
> >
> > "gavin" <no-one@home.com> wrote in message
> > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > >I have the following data in a spreadsheet:
> > >
> > >
> > >
> > > A1      B1      C1      D1      E1      F1
> > > 4.5f    6f       3.5f                 3f       7.25f
> > >
> > >
> > > I need to be able to add the numbers together to give me 24.25, i.e.
> strip
> > > the fs away. The numbers will always be less than 10 and the there
will
> > > only
> > > ever be .25 or.5 or .75 after the number (I don't know whether that is
> > > significant?).
> > >
> > >
> > > If anyone can show me how to do this I would be very grateful. I have
> been
> > > messing around with MID and FIND to no avail and then started thinking
> > > that
> > > SUMPRODUCT might have to get involved but it all got a bit much for
me!
> > > There may be a perfectly simple solution which I have overlooked!
> > >
> > >
> > >
> > > Regards,
> > >
> > >
> > >
> > >
> > > Gavin
> > >
> > >
> >
> >
>
>


0
bob.phillips1 (6510)
5/20/2005 8:31:33 PM
Thanks for the link, Bob. I have seen SUMPRODUCT used many times (mostly in
answers to questions in this forum) in ways I wouldn't have had a clue
about. I think this web page will teach me a lot about that function!



Best wishes,



Gavin



"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uS4u7pXXFHA.3732@TK2MSFTNGP10.phx.gbl...
> Gavin,
>
> Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html, it's
all
> explained there.
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "gavin" <no-one@home.com> wrote in message
> news:d6ldsf$5ju$1@news6.svr.pol.co.uk...
> > Bob,
> > On reflection can I just ask about HOW this function works? For example,
I
> > have never seen a double hyphen before.
> >
> >
> > Regards,
> >
> >
> >
> > Gavin
> >
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:ON5MP$VXFHA.1152@tk2msftngp13.phx.gbl...
> > > Hi Gavin,
> > >
> > > One way
> > >
> > > =SUMPRODUCT(--(LEFT(A1:E1,LEN(A1:E1)-1)))
> > >
> > > -- 
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "gavin" <no-one@home.com> wrote in message
> > > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > > > I have the following data in a spreadsheet:
> > > >
> > > >
> > > >
> > > > A1      B1      C1      D1      E1      F1
> > > > 4.5f    6f       3.5f                 3f       7.25f
> > > >
> > > >
> > > > I need to be able to add the numbers together to give me 24.25, i.e.
> > strip
> > > > the fs away. The numbers will always be less than 10 and the there
> will
> > > only
> > > > ever be .25 or.5 or .75 after the number (I don't know whether that
is
> > > > significant?).
> > > >
> > > >
> > > > If anyone can show me how to do this I would be very grateful. I
have
> > been
> > > > messing around with MID and FIND to no avail and then started
thinking
> > > that
> > > > SUMPRODUCT might have to get involved but it all got a bit much for
> me!
> > > > There may be a perfectly simple solution which I have overlooked!
> > > >
> > > >
> > > >
> > > > Regards,
> > > >
> > > >
> > > >
> > > >
> > > > Gavin
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
no-one1 (46)
5/20/2005 8:58:49 PM
Oh crikey - I've made a bit of a mistake! On reflection the range in the
formula could possibly contain a small number of other characters which it
needs to ignore. It could contain "s" or "l" - is there a way to make the
formula ignore these?


Thanks for more help!



Best wishes,



Gavin





"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OdqeqrXXFHA.3584@TK2MSFTNGP14.phx.gbl...
> Gavin,
>
> It is added to cater for empty cells.
>
> To show it clear one of the cells in your range, it will still work okay.
>
> Now take the &0 out of the formula, and see what happens.
>
> -- 
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "gavin" <no-one@home.com> wrote in message
> news:d6le8p$m3b$1@newsg2.svr.pol.co.uk...
> > Can I ask what the "0&" does? I have tried the formula without it and in
> my
> > case it still works fine.
> >
> >
> >
> > Regards,
> >
> >
> >
> > Gavin
> >
> >
> >
> > "N Harkawat" <nh@nh.com> wrote in message
> > news:%233pfbCWXFHA.2076@TK2MSFTNGP15.phx.gbl...
> > > =SUMPRODUCT(--SUBSTITUTE(0&A1:F1,"f",""))
> > > if its always "f"
> > >
> > > "gavin" <no-one@home.com> wrote in message
> > > news:d6l5o2$j16$1@newsg1.svr.pol.co.uk...
> > > >I have the following data in a spreadsheet:
> > > >
> > > >
> > > >
> > > > A1      B1      C1      D1      E1      F1
> > > > 4.5f    6f       3.5f                 3f       7.25f
> > > >
> > > >
> > > > I need to be able to add the numbers together to give me 24.25, i.e.
> > strip
> > > > the fs away. The numbers will always be less than 10 and the there
> will
> > > > only
> > > > ever be .25 or.5 or .75 after the number (I don't know whether that
is
> > > > significant?).
> > > >
> > > >
> > > > If anyone can show me how to do this I would be very grateful. I
have
> > been
> > > > messing around with MID and FIND to no avail and then started
thinking
> > > > that
> > > > SUMPRODUCT might have to get involved but it all got a bit much for
> me!
> > > > There may be a perfectly simple solution which I have overlooked!
> > > >
> > > >
> > > >
> > > > Regards,
> > > >
> > > >
> > > >
> > > >
> > > > Gavin
> > > >
> > > >
> > >
> > >
> >
> >
>
>


0
no-one1 (46)
5/23/2005 6:50:57 PM
Reply:

Similar Artilces:

A formula that displays the last letter in a text
I frequently use text in cells. I use MID and LEN often enough, but is there a formula that will display the last letter in a text that will often vary in length. Thanks. Michael =RIGHT(A1,1) -- __________________________________ HTH Bob <MichaelRLanier@gmail.com> wrote in message news:0983e9df-8991-43ff-ab3a-09ee5450fbd6@a70g2000hsh.googlegroups.com... >I frequently use text in cells. I use MID and LEN often enough, but > is there a formula that will display the last letter in a text that > will often vary in length. Thanks. > > Michael Hi Michael =MID(A1,...

Retrieving Data from a Column / Row to populate a cell
I currently have data supplied to me in an Excel spreadsheet that I to transfer manually I would like this to populate a cell reference, the problem I have is that I cannot gather a train of thought to obtain the data from from a specific row/column. There is a sample of the data supplied to me below Name In Out Int Greg 165 108 29 09 July 2003 42 34 9 14 July 2003 44 19 4 16 July 2003 40 40 8 17 July 2003 39 15 8 Andrew 125 43 9 1...

Copy Cell or Column without advancing formula reference?
I have a spreadsheet with formulas referring to another worksheet. I need to copy the columns however, each time I do, the formulas advance to another cell. Which is an incorrect reference, skewing my results. You need to modify the formulas to use absolute references. Edit each of the cell addresses in the formula and put $ (dollar signs) in front of the column letters. "sgluntz" wrote: > I have a spreadsheet with formulas referring to another worksheet. I need to > copy the columns however, each time I do, the formulas advance to another > cell. Which is an...

Wrap Text from one cell to another cell
I want type all my text into C111 (1-3 pages worth). I want this text to automatically wrap to D111 to E111 to F111 to G111, etc. The cell appears to have a maximum limit of characters. Please help. "bras" wrote: > I want type all my text into C111 (1-3 pages worth). I want this text to > automatically wrap to D111 to E111 to F111 to G111, etc. > > The cell appears to have a maximum limit of characters. Copied from the help file:- Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. Have y...

How do I add a data series in a combination chart in PPT 2003?
I have a combination bar/line chart showing gross revenues and passengers by month over a 3-year period. The gross revenues are bars on the primary y-axis and passengers are lines on the secondary y-axis. I am trying to add gross revenues and passengers for CY2010. I am able to add a data series for the passengers, but when I try to add a data series for the gross revenues, the chart treats it as a series on the secondary axis as a line. How do I add the series as a bar on the primary axis, or alternatively, change the series from a line on the secondary axis to a bar on th...

Independently Formatting Columns and Rows
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is there a way to independently format the width of a column on specific cells? In other words, if I wanted to change the width of the cells in the middle of the document, can I do so without affecting the width of the cells above it and below it? On 2010-05-28 12:40:38 -0400, monks617@officeformac.com said: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Is there a way to independently format the width of a column on > specific cells? In other words, if I want...

[2007/ToDo List] Add-in to create sub-tasks?
Hello ince Outlook is basically what everyone uses to sync a BlackBerry with Windows, I'm stuck with using it. However, I need to divide tasks into sub-tasks, and sometimes/often set some start and/or due date to an task. This is what the MS Outlook 2007 ToDo List UI looks like: http://www.oucs.ox.ac.uk/nexus/outlook2007/images/tasks.png This is what the ToDoList from AbstractSpoon looks like: http://www.shafqatahmed.com/WindowsLiveWriter/MainScreen2.png Do you know of an add-in so that Outlook support this poor-man alternative to MS Project? Thank you. "Gilles Ganault"...

Add vendor order number to purchase order
When we send a purchase order to a vendor, that vendor assigns their own internal sales order number to our purchase order. I need to be able to put that number somewhere in GP that will allow me to locate one of our purchase orders based on the supplier's order number. There are some fields on the Vendor detail window by clicking the arrow button by the vendor id. You might be able to use one of those fields. Otherwise, Extender is the way to go. -- Charles Allen, MVP "Mike Davis" wrote: > When we send a purchase order to a vendor, that vendor assigns their own &...

Text in textbox is indented?
Hi, I have typed some text into a textbox. Then I think I messed with some text settings and as a result, my text is indented from the second line of each paragraph onward. Something like this: ---------------------- The text in my textbox ****begins here, ****however as you ****can see it is ****indented from ****the second line ****onward. If I start a new line ****however, the ****first line is not ****indented. ---------------------- {picture attached} I've clicked the decrease indent button many times but that doesn't change anything. Any ideas? ...

add functionality to RMS calendar
the calendar is a good function to have. Expanded functionality would be great. Design it as a watered down version of Outlook - set individual appointments that can be assigned to individual customers. That way you can search the calendar to see what appointments individual customers have scheduled. Being able to set up recurring schedules would be helpful, too. If it doesn't make sense to expand the calendar functionality, then create functionality to synchronize customer list in RMS with Outlook. FRB ---------------- This post is a suggestion for Microsoft, and Microsoft resp...

Formula for changing Text Color??
I don't know if this is even possible. What I would like to do is t input a number into a column in sheet 1 and have a correspondin number (which is already there) change font color in sheet two. So input a number, say 3 in C10 on sheet 1 and the number 3 which is i sheet 2 (c10) changes from black to red. Is this possible? thanks very much -- dan ----------------------------------------------------------------------- danh's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27420 You ...

import fixed-length text file into excel
- 2 2299 Del Pietro M Wintervill NC 1:18:26 2:44:0 2:44:11 6:16 - 1 2045 Robert Marchinko M Salisbury NC 1:15:34 2:31:1 2:31:20 5:47 I have a text file where each field starts at same column and of th same length in each record. I tried cut&Paste, I tried opening it in excel as a tab delimited file I tried as a fixed-length file None of these worked. Data came in scrambled or into scrunched columns If the data were a table then cut&paste works great. What's the secret in loading above 2 records into Excel? I couldn' simulate the look ...

CButton: Change size based upon text extent
I'd like to dynamically change the size of my CButton derived control based upon the text/bitmap size. So if the application calls SetWindowText during program flow the button rect will update. What's the best way to capture this and override the rect size? Thanks Eric You can use SetWindowPos to change its size. You can handle WM_SETTEXT and see what text is being set to, calculate the size of the button, change its size and then call the base class handler. --- Ajay "Ajay Kalra" <ajaykalra@yahoo.com> wrote in message news:1145984489.865667.169280@e56...

visio 2007 columns and relations disappeared
I reverse engineered a database and at some point the wile I was organizing the tables a few of the foreign got left behind. I spent long time organizing the tables and found this very frustrating. I could not find a way "reattach" the tables to each other. I removed the orphaned lines from the page thinking that when I updated the diagram from the database they would reappear. They did not. In fact columns had also been removed from tables. How do I get the tables to reflect the data in the database again? Refreshing the model does not work! ...

How do I format text as a bulleted item in Excel? Is it possible?
It's a manual effort. You can use Insert|Symbol (if you're using xl2002+) or copy the bullet you want from CharMap. (Windows start button|charmap). You'll have to select that character in the formulabar and change the font to what you want. Chip Pearson has an addin that may help (if you're using xl2k or lower). http://www.cpearson.com/excel/download.htm (look for Symbolizer and make sure you get the correct version) DRB wrote: -- Dave Peterson Dave Peterson <petersod@verizonXSPAM.net> wrote in news:43062E62.984DE524@verizonXSPAM.net: > Or with: http://...

Create set based on column items feature in Pivot Tables 2010
I have a pivot table created in 2010 that I would like to use the 'create sets' feature in. However it is not enabled. The three 'sets' features are disabled as are the OLAP tools / What if analysis options on the Pivot Table options ribbon. What am i missing??? Cheers Julie Okay, found the problem - for 2010 the sets are only allowable for OLAP based Pivot Tables. :( On Nov 23, 10:16=A0am, jigsaw <jul...@consultant.com> wrote: > I have a pivot table created in 2010 that I would like to use the > 'create sets' feature in. =A0However it is not enabled....

What are the zig zag lines under text?
I've got tiny black zig zag lines under my text. They're not spelling errors, because spell check is completed. I can't view what the document layout looks like because of these nasty little lines. Word 2007, print layout view. It indicates hidden text. If you toggle off the ShowAll command, the text will disappear. -- Terry Farrell - MSWord MVP "Pamoola55" <Pamoola55@discussions.microsoft.com> wrote in message news:703BB6D0-A3AD-4A8E-B10C-901BD7EAE3E5@microsoft.com... > I've got tiny black zig zag lines under my text. They're not s...

Need Help for Desirability Calculator (moving squares with numbers around)
Hi, I'm new to this forum and don't know much about excel. I want to make desirability calculator wich is related to a game. In the game ther are structures and each structure has a desirability affect on it environment. When a few structures are placed next to each other it' too tedious work to calculate their total affect. I hope the attachment will be clear enough to show what I want to do. There are around 30 structures but as an example I have shown th desirability affect of two of them; an oracle and a garden. When yo open the file you will see these two structures and thei...

Importing Text file....need to manipulate text and spread across multiple columns.
I receive a number of different data files in varying formats. In orde to effectively review the information, however, I need to format it an sort it in a way that makes the data useful. Here is an example o what I have (this infromation has been scrubbed) : <Seam> First Name Neelakan Ready <Seam> Last Name Chin <Seam> Address 31 Bloomfield Lane,Apt #215 <Seam> City Auburn <Seam> State Michigan <Seam> Post Code 48336 <Seam> Country USA Here is what I want: USERID First Name Last Name Address City <Seam>...

extracting numbers from variable text
hi I am trying to extract a numeric value from a column of text that does not always have any relevant data. Typical column entries are like this: Employees: 46 | Employee Growth: -4.17% Employees: 3336 | Employee Growth: -5.66% Employees: 1700 | Employee Growth: % but in this mixed bag of data there are many odd entries of free text e.g. Two areas are using xx. Production studio's . . . . . The data I want to extract is the number of employees, which can be anything between a 1 and 6 digit number. I can do a basic extraction using =MID(cellref, 12,6) to get the raw information, bu...

Add one more EDGE in existing Exchange + EDGE 2007
I need to add new EDGE in my Exchange + EDGE 2007 enviroment (Edge in LAN, I know that isn't a good choice, now I'm changing). I need to deploy new antispam, so I want to Install new EDGE and test it before I will go live, (this will be in DMZ) . Some one could give me some macro information about this topic? or some links? ...

i want to swap text in one Word document?
I want to be able to use one document and insert two types of text, i.e., one manual as Australian version and one manual as NZ version. Want to be able to switch texts in the same document. And your question is what? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Anita." <Anita.@discussions.microsoft.com> wrote in message news:2FE3898A-3DE0-4085-B0BF-9517727EB3EE@microsoft.com... >I want to be able to use one document and insert two types of text, i.e., >one > manual as Australian ...

Why are my excel worksheet columns are Numbered not Alpha
My excel worksheet columns are numbered not alpha. What did I do? How can I change back to numbers Hi Paula, Try: Tools | Options | General Tab | deselect 'R1C1 reference style | ok --- Regards, Norman "Paula" <Paula@discussions.microsoft.com> wrote in message news:BD09BE8B-EDCB-4E91-8EBC-A98049B97A35@microsoft.com... > My excel worksheet columns are numbered not alpha. What did I do? How can > I > change back to numbers You've probably set the R1C1 referencing style. Go to Tools>Options> then selecet the General Tab and uncheck the R1C1...

Prompt for the number of times to duplicate current record
I want to have a button on my form that will ask you how many times to copy the current record and then copy that many times. I cannot find anything on this. Can someone please help me? Why? I'm not asking to be snoopy, but because a well-normalized relational database may not need duplicate records. Can you describe a bit more about the business need you feel this approach (duplicate records) will help you solve? Regards Jeff Boyce Micosoft Office/Access MVP "Krispy" <Krispy@discussions.microsoft.com> wrote in message news:64453E77-E668-486C-8D98-09B98787C762@mi...

Setting column width in printed reports
I print the same reports every week. Every week they are a different width on the page. How do you set the column width to print the same width on the page? How do you know if it is going to fit on the page? ...