excel questions.

Soldato
Joined
13 Jan 2004
Posts
12,579
Location
Leicestershire
hey, im learning quite a lot regarding formulas recently in my work but im struggling on a couple of things id like to do and its annoying me.

for example if i had a smiley face and a sad face i'd want to conditionally relate these to the value in another cell. how would i go about this? i've used conditional formatting a bit and even strayed into formulated conditional formatting but not with loose pictures. i figure if i could lock the picture to a cell then i could use that but i cant lock the images to cells in order to make the formulas point to them.

the other thing thats bugging me...

again its conditional formatting... how can i get an entire row to change colour if on of the cells is a certain value/range? and is it possible to change text type etc?

i can get the cells to display various text and include cell figures using multiple IF values e.g =if(A1=0,"yes","no",if(ifA1=1,"wtf?","haha")). i've even started using countif and roundup commands for stuff but its this conditional stuff thats doing me.

considering i started the job knowing SUM as about the only excel command i think i've done ok considering i've been self teaching myself but this is really annoying me.
 
Soldato
Joined
13 Jan 2004
Posts
12,579
Location
Leicestershire
oh thanks!! i had all of that right all apart from the 'true' bit!!!!

i'm gonna use variations of this now...

=IF($F$9<=10,TRUE)

then add in a lower value one.

how would i go about adding in a lower value to get a range?
 
Soldato
Joined
13 Jan 2004
Posts
12,579
Location
Leicestershire
cheers marc, gives me something to work with.

any ideas on how to assign an image to a cell so i can then link to that cell and have the pic appear in the cell i'm putting the formula in?
 
Soldato
Joined
7 Mar 2005
Posts
19,031
Location
LU7
I've been thinking about this, with no luck. :(

Are you able to import a graphic into the Excel image library? Either that or perhaps somehow trigger a macro to import a graphic for you?
 
Soldato
Joined
13 Jan 2004
Posts
12,579
Location
Leicestershire
i have the images... they sit on the spreadsheet.

however i know nothing about macros. i will be having a play though.

if i was to formulate the whole sheet so it all interlocked and did its thing THEN ran a macro against those figures would i be able to do that?

or if i made a macro that copied the image on the spreadsheet and then moved it to the relevant cell?

how do you create macro buttons so i could just press the button at the end to do it all?
 
Soldato
Joined
7 Mar 2005
Posts
19,031
Location
LU7
i have the images... they sit on the spreadsheet.
Yeah I knew you did. Was just thinking that you might have more luck if the images were in Excel rather than external images.

if i was to formulate the whole sheet so it all interlocked and did its thing THEN ran a macro against those figures would i be able to do that?
Not sure what you mean by "interlocked" but from "THEN ran...." sounds OK. :) :p

or if i made a macro that copied the image on the spreadsheet and then moved it to the relevant cell?
That is what I was thinking yes.

how do you create macro buttons so i could just press the button at the end to do it all?
Depends on what version of Office you have. If 2007 it seems you need to activate the Developers tool menu bar ribbon thingy.

Code:
Click the Microsoft Office Button , and then click Excel Options.
Click Customize, and then in the Choose commands from list, select Macros.
In the list, click the macro that you created, and then click Add.
To change the button image of the macro, select the macro in the box where it was added, and then click Modify.
Under Symbol, click the button image that you want to use.
To change the name of the macro that is displayed when you rest the pointer on the button, in the Display name box, type the name that you want to use.
Click OK to add the macro button to the Quick Access Toolbar.

If an earlier version of Office then there are two "sub menus" that you can turn on that offer buttons. One of them works with macros, the other doesn't I think. For the life of me I cannot remember the names of the menus and therefore which one it is you want. Sorry. :(
 
Soldato
Joined
13 Jan 2004
Posts
12,579
Location
Leicestershire
cool, thanks marc. you've given me ideas and proof that i'm not a complete nob-end with excel. :)

by interlocked i meant, pulls in the info from other sources and then displays it. :) i was kinda thinking out loud then. :)

version of office is XP and 2003.
 
Soldato
Joined
13 Jan 2004
Posts
12,579
Location
Leicestershire
If an earlier version of Office then there are two "sub menus" that you can turn on that offer buttons. One of them works with macros, the other doesn't I think. For the life of me I cannot remember the names of the menus and therefore which one it is you want. Sorry. :(

forms toolbar - then click on button - make the button and it automatically comes up with assign macro. sorted. :D
 
Soldato
Joined
7 Mar 2005
Posts
19,031
Location
LU7
That's it! Forms. There is another one that looks similar and has another kind of button. This button doesn't really work well with macros though.
 
Soldato
Joined
30 Apr 2007
Posts
3,094
Location
Kent
For my GCSE BTEC Course I had to create a button and attach a macro to it.

I thought it was a command box or something but it turned out you create a text box or a button, right click and there is an options somewhere to attach.

Hope this is of some use ;)
 
Top