Excel Question

A-Vic

Can anyone tell me how to import an excel sheet on to a word document i have done this before but many many years ago

Thanks

Vic

CJC

You just need to choose "Object" from the Insert menu, it should be pretty obvious what to do from there.

A-Vic

CJC wrote: »

You just need to choose "Object" from the Insert menu, it should be pretty obvious what to do from there.

Thank you :001_smile:

CJC

You're welcome.

mark130273

just another quick question ?

if I have imported a picture into Word !

is it possible to draw a completely white square to cover part of this picture.??

or would it be easier to import a blank picture ?

CJC

You just need to bring up the drawing toolbar (view->toolbars) and you can draw rectangles to your heart's content.

mark130273

speaking about excel programme, is there a formula that you can put that will totally the sqaures that are coloured a certain colour , as i have done a cash flow for a continuing business but is on different items, so i have a single cash flow but 4 different colours on this sheet ?

Buff

that would be a pretty complex VBA solution required. You'd also have to make sure you also didn't colour other cells throughout the workbook in the same colour or youcould end up including those in the calc too.

Are the coloured cells not absolute (IE always the same ones?) if so, just keep it simple and effective and add the cells together with the =SUM() function.

Buff

Always save your work before messing round with VBA code.

Open your worksheet and press Alt+F11
Insert > Module

Copy and paste following code into the white area

Function SumColor(rColor As Range, rSumRange As Range)
Dim rCell As Range

Dim iCol As Integer

Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell
SumColor = vResult

End Function

Press Alt + Q

Now go to the cell where you want the total to be and press Shift + F3

Select category as "User Defined"

Highlight the "SumColour" and press OK (or double click SumColour - your choice)

It will bring up another box, the first asking you to choose the cell which has the colour you wish to total - either type it in or click the coloured cell.

The second box asks you to tell excel the data range within which to carry out the action - IE, Your first cell would be A1 the lets say your last cell in your sheet is cell O200 - you can either type A1:O200 or you could click and drag. Press OK.

Hey presto, the code adds together all cells which are coloured the same as the one defined in the first Box.

Credit for this to Ozgrid.

mark130273

thanks for that buff...........complicated but think i will have a go at that........will this formula change if i start chaging sqaures to different colours as i have 4 different colours on the same spreadsheet and if i change lets say a pink square to a blue one .who the formula reckonise the change and work it out from then ?

if so ....woooooooooo hooooooooooooooo

cheers

mark130273

thanks for that buff. its works really well ,but the only points are that when the formula is there. if you start changing squares to different colours and you have to do the whole formula again....(not worried about that)

but the other point when i tried to reload the file it comes up with macros problem and i have to turn down the security level to medium to open it?> do you know why this is happening ....?

CJC

That'll probably just be because Excel won't open anything with macros in at higher security levels. Macros were once a common way to spread viruses.

mark130273

so if i change the level to medium or low . it shouldnt make much difference unless i get files send via email of cause ?

Buff

Stupid excel - I hate that! I have partners phoning me day in day out witht he same thing. Chris is right - Macros can be dangerous, but not if you know who they came from and you trust them.

Go to tools > Options > Security > Macro Security > put it on low and press OK.

You'll need to closer and re open for changes to take effect.

One other thing i didn't mention was that if you added together 5 red cells say, and then you added another red cell, the formula wouldn't calc it automatiaclly, instead, you would have to go into the cell where the total is and press "enter key" inside it so it refreshes the formlua. (you'll be able to write a seperate macro to do that but I don't have time at the moment to discuss).

On the multo colour cells thing, you can do the same thing over again - my suggestion would be to have the following (change colours as necessary):

Cell A1 - Red
Cell A2 - Yellow
Cell A3 - Blue
Cell A4 - Green

Cell B1 - Do what i said before and choose to count red cells so the total is couting reds only
Cell B2 - Do what I said before and choose to count yellow cells so the total is counting yellow cells only

Or - send me your worksheet and i'll put it in for you

jamieleeuk2@googlemail.com

mark130273

hiya buff. just can you do a simple spreadsheet for me using the second lot of formula your using . asi have got the first lot working but it doesnt carry running the totals if you start changing squares ?

Buff

I have done a worksheet containing the relevant code which totals any values in certain colours (i have done you 4 colour groups, if you want more just click the colour in the relevant cell below the others and drag the formula down or ask me). I have assumed you won't go outside of the following range (A1:O150) if you do, then adjust the formula as necessary.

Where do you want me to send it.