Replies

Yeah, that’s what I look at too. But the request was to have a time embedded in a cell … ( shrug ).

When you are in Toolbar Customize mode then right-click the toolbar button for options on changing the button-face ( you can manually edit the button face too ).

As for changing the tool tip, you’ll need to do that in VBA … see here:
http://support.microsoft.com/kb/171595

You could stick a web query to a clock website in your workbook to show the up-to-the-minute time, and set the connection properties to refresh every 1 minute. But, obviously, that’s overkill … but it would work.

One such website:
http://www.worldtimeserver.com/current_time_in_GB.aspx

( right-click over the time and choose Export to Excel … in Excel change Data Connection property to set refresh rate )

That’s why you have to use CTRL-V to paste the formula in.

This is just simple clipboard pasting, and not using any Excel menu edit options.

Glenn.

Try activating the formula bar, so the formula is in Edit mode, select the entire formula, press CTRL-C, press Esc ( to exit Edit mode ), do menu command Insert/Name/Define, type the name you want, activate the Refers To box, and press CTRL-V.

And hopefully, you have what you want.

Glenn.

Hi Jeff,

select A3:AD3 and do menu command Format/Conditional Formatting.

In Condition 1 box select Formula Is, and in the next box type …
=MOD(WEEKDAY(A1),6)=1
and click the Format… button to choose a pattern for shading Sats and Suns. Press OK, press OK.

That should do it.

Glenn.

Use

=COUNTIF(A2:AD2,”=Yes”)

instead.

Glenn.

Sorry to hear that you can’t use this method.

One way to speed up the process is to link a single cell, then edit the formula so that is a relative formula, ( i.e. no $ signs ), and then copy the formula rightwards for as many cells as necessary, and afterwards drag each cell so that it goes where you want it to.

Hope that helps a bit, I’ve done similar kinds of linking hundreds of times, and using relative formulas, dragging after copying, and using Edit/Replace to change formulas in bulk all save time.

Glenn.

Yes, you can link and transpose at the same time.

Select cells B8-B12 in sheet2, and type this formula in the active cell ….
=TRANSPOSE(Sheet1!F5:J5)
and then press CTRL-SHIFT-ENTER instead of ENTER.

This creates an array link, using the TRANSPOSE function to change the shape of the link.

Hope this helps.
Glenn.

If what you’re looking for is the connection between the workbooks and the Excel 4 XLM macros that are called, have you looked in defined names in the workbooks.

One way to call macros automatically in the old days ( Excel 2.1, Excel 3.0 ) was to insert a name in the workbook called AutoOpen and have the reference be the string of the path and name of the macro sheet with the required macro name. E.g. “C:ProgsMyProc.XLM!DoProcess”

Glenn.

Hi Legare,

accessing the PAGE.SETUP Excel4 macro command is quite easy for a quick fix, as you embed something like this in your VBA …

ExecuteExcel4Macro (“PAGE.SETUP(“”Nice header””,””Nice footer””)”)

and this executes really fast compared to the VBA equivalent.

You can make it more complex, but you might need to refer to the Excel4 macro function help ( can be downloaded from MS site, I believe )

Here is an example of a more complex invocation of the call …

header = Chr(34) & “My own header” & Chr(34) ‘ text of header ( in quotes )
footer = Chr(34) & “My footer” & Chr(34) ‘ text of footer ( in quotes )
leftm = 1.9 / 2.5 ‘ left margin ( converted to centimeter from inch )
rightm = 1.9 / 2.5 ‘ right margin
topm = 2.5 / 2.5 ‘ top margin
botm = 2.5 / 2.5 ‘ bottom margin
hdng = True ‘ row and column headings to be shown ( or FALSE if not )
gridl = True ‘ sheet gridlines to be shown ( or FALSE if not )
hcent = True ‘ horizontal centering to be done ( or FALSD if not )
vcent = True ‘ vertical centering to be done ( or FALSE if not )
orient = 2 ‘ print orientation, 1 = Portrait, 2 = Landscape
paper_size = 9 ‘ paper size ( see Excel4 macro function help for full list )
scaling = 100 ‘ scaling as a percent ( see help for other options )
Pg_Num = 1 ‘ specifies the number of the first page ( see help also )
Pg_order = 1 ‘ pagination, 1 = Top-to-bottom then right, 2 = Left-to-right then down
bw_cells = False ‘ print as pure black and white ( or not if FALSE )
quality = “” ‘ see help
head_margin = 1 / 2.5 ‘ header margin from edge of page ( converted from inches )
foot_margin = 1 / 2.5 ‘ footer maergin from edge of page ( converted from inches )
notes = False ‘ print cell notes/comments with sheet ( or FALSE if not )
Draft = False ‘ print in daft mode ( or FALSE if not )

xl4command = “PAGE.SETUP(” & header & “,” & footer & “,” & _
leftm & “,” & rightm & “,” & topm & “,” & botm & “,” & _
hdng & “,” & gridl & “,” & hcent & “,” & vcent & “,” & _
orient & “,” & paper_size & “,” & scaling & “,” & Pg_Num & _
Pg_order & “,” & bw_cells & “,” & quality & “,” & _
head_margin & “,” & foot_margin & “,” & notes & “,” & Draft & “)”

ExecuteExcel4Macro (xl4command)

Glenn.

Hi Bruce,
you need an extra column to do a little bit of calculation before you can do what you want. Put this in column C, and copy it down …

=1/SUM(IF(B2=$B$2:$B$7,IF(A2=$A$2:$A$7,1,0),0))
entering with CTRL-SHIFT-ENTER instead of ENTER, and putting in whatever ranges you need to cover all houses and pets.

After doing this, another formula, like this …
=SUM(IF($B$2:$B$7=”dog”,$C$2:$C$7,0))
again entered with CTRL-SHIFT-ENTER instead of ENTER, entered into any empty cell will do the trick.

Cheers, Glenn.

It sounds as if you’ve got Transition Formula entry switched on, which puts apostrophes in the cell formulae automatically.

Use menu option Tools/Options and select Tab Transition, and switch off any Transition check boxes.

Hope that helps.
Glenn.

Jeff,
Dreamboat is right, you just refer to the defined name for your list in the Data Validation.

Are you sure you’re doing it exactly right? You should define the range A2:A25 on Sheet1 as a name, say Mylist, and then click on cell A2 on Sheet2, use menu option Data/Validation, click List ( for Allow ), and then type
=Mylist
as the source.

Hope that clears things up.
Cheers, Glenn.

Jeff,
if you want entry to B6 restricted depending on the contents of A6, then make a list named the same as the corresponding entry in A6, with the allowable contents, for each of the possible entries. For example make a list named Red containing 1, 2 & 3, and another list named Blue containing 4, 5 & 6.

Then to get the processing you want, make the Data Validation for B6 be List, with a source of
=INDIRECT(A6)

See if that is what you want.
Cheers, Glenn.