Excel Question
A-Vic
Registered Posts: 6,970 Beyond epic contributor ๐งโโ๏ธ
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
Thanks
Vic
0
Comments
-
You just need to choose "Object" from the Insert menu, it should be pretty obvious what to do from there.0
-
You're welcome.0
-
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 ?0 -
You just need to bring up the drawing toolbar (view->toolbars) and you can draw rectangles to your heart's content.0
-
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 ?0
-
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.0 -
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.0 -
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
cheers0 -
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 ....?0 -
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.0
-
so if i change the level to medium or low . it shouldnt make much difference unless i get files send via email of cause ?0
-
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.com0 -
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 ?0
-
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.0
Categories
- All Categories
- 1.2K Books to buy and sell
- 2.3K General discussion
- 12.5K For AAT students
- 319 NEW! Qualifications 2022
- 162 General Qualifications 2022 discussion
- 11 AAT Level 2 Certificate in Accounting
- 56 AAT Level 3 Diploma in Accounting
- 87 AAT Level 4 Diploma in Professional Accounting
- 8.8K For accounting professionals
- 23 coronavirus (Covid-19)
- 272 VAT
- 92 Software
- 273 Tax
- 135 Bookkeeping
- 7.2K General accounting discussion
- 200 AAT member discussion
- 3.8K For everyone
- 38 AAT news and announcements
- 345 Feedback for AAT
- 2.8K Chat and off-topic discussion
- 582 Job postings
- 16 Who can benefit from AAT?
- 36 Where can AAT take me?
- 42 Getting started with AAT
- 26 Finding an AAT training provider
- 48 Distance learning and other ways to study AAT
- 25 Apprenticeships
- 66 AAT membership