Excel Question

A-Vic
A-Vic Registered Posts: 6,970
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

Comments

  • CJC
    CJC Registered Posts: 1,657
    You just need to choose "Object" from the Insert menu, it should be pretty obvious what to do from there.
  • A-Vic
    A-Vic Registered Posts: 6,970
    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
    CJC Registered Posts: 1,657
    You're welcome.
  • mark130273
    mark130273 Registered Posts: 4,234
    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
    CJC Registered Posts: 1,657
    You just need to bring up the drawing toolbar (view->toolbars) and you can draw rectangles to your heart's content.
  • mark130273
    mark130273 Registered Posts: 4,234
    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
    Buff Registered Posts: 275
    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
    Buff Registered Posts: 275
    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
    mark130273 Registered Posts: 4,234
    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
    mark130273 Registered Posts: 4,234
    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
    CJC Registered Posts: 1,657
    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
    mark130273 Registered Posts: 4,234
    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
    Buff Registered Posts: 275
    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

    [email protected]
  • mark130273
    mark130273 Registered Posts: 4,234
    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
    Buff Registered Posts: 275
    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.
Privacy Policy