how do I change lookup sheet within formula?
I have a series of identical sheets mapping out employee holidays on an
individual basis and I want to map them on to a summary sheet.
I have posted a simple if formula into one row to extratc information from
one sheet. Is there a way to copy this formula down the page to change the
sheet name, but keep the rest of the formula identical?
Yes using INDIRECT(). The below if applied and copied down will refer
Sheet1!A1, Sheet2!A1 ,Sheet3!A1 and so on...Change to suit your tab names
Jacob (MVP ...Associate name with email
I have a column of names and a column of email addresses. Is there any way
to merger them into a single hyperlink that shows the name and sends to the
Try this : With the names in column A and the E-mail addresses in column B try this
For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If myCell.Value Like "?*@?*.?*" Then
ActiveSheet.Hyperlinks.Add Anchor:=myCell.Offset(0, -1), _
Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Offset(0, -1).Value
...I cant see emails coming in anymore.
I used to push send/receive and a box would come up telling me 'Receiving 8
of 40 emails' with a green bar going across the centre. Once the emails are
received the box goes. If I push send/receive nothing happens. If I push it
say, 10 times, again nothing happens. Once all the emails are thru the box
that used to come up, than flashes up 10 times, but by than its a little
late. Does anyone know how to get the box back? I would really appreciate
it... KR Mick.
I send this message using the forward procedure to somebody of the group
My problem is likely the same as a lot ...Not able to see all data but can see on another computer
I am using SAP application and if i retrive the excel file from SAP i can
see only 48 lines where as i should see 110 lines but if i opened that same
excel file in another computer then i can see all lines (110 Lines) i don't
know why its happening.
Can anyone help on this? Thanks in advance.
...Rename files based on file name?
Hello, How would I write vb script to do this?...seems simple but I
have no idea.
I have files in the directory C:\MyFiles
they are named MYFiles101509.awq MyFiles111709.awq
I want to rename them to OctFiles0001.xls and NovFiles0001.xls
is there a way to look through files like look for MYFiles01* and
rename that way?
If you don't have the WSH help file you should
download it and read up about the VBS methods
so that you'll at least "have some idea". :)
String operations you might need include
Mid, Instr, Left, Right.
> Hello, How wo...Word 2004: Style List / Style Dialogue Box Bug
Hello all -- I'm in Word 2004. Upon opening a new document, it defaults
to showing only a couple of styles in the style list. I want to be able
to see all of them.
Word Help suggests going to the Formatting Palette and selecting all
styles. This works, but I don't use the Formatting Palette that often.
The second option: View All Styles in the Style Dialogue box doesn't
work. This is the one I want to use. It tells users to go the the
Format menu and select Style command, then select All Styles on the
List popup menu. I did this and clicked apply, but when I go back to
the documen...Need formula for :
I have formula wich generate a random number from a list ..
-How can i let this formula runs 60 Times , and then put each rando
number in seperated boxes...
need 10 boxes with each 6 different numbers..non of 6 numbers in a bo
may be the same..
It would be a great help thank you..
All info about usefull formulas are welcome ?
Message posted from http://www.ExcelForum.com
I'm not sure of what you exactly want, but if you want the same formula
applied 60 times, put it in 60 cells.
To make that a bit easier : Select 60 cells ( for instance 10 rows and 6
columns t...Sheet Names #3
Is there a way yo can put a formula in a cell to have it equal what the
sheet name is? Example( I have a spreadsheet with about 30 sheets in it. Each
sheet has a different name. Instead of haveing to name every sheet, and tab
twice is there a way I can rename the tab and have a formula i the sheet that
will name the sheet the same as the tab name?)
Just don't put it in A2 or you'll get a circular reference.
"Marcus" <Mar...Query to Access : does'nt see tables
Office 2000 SP3 FR
From Excel / MS Query, I want to read data in Access tables.
I choose the mdb file and so MS Query displays this error message
"This data source does'nt have any visible table".
Original in French : "Cette source de donn�es ne contient aucune table
I transfert my data to a new mdb file : it work's few times (!), then the
error message appears again.
How to resolve this problem ?
MS Query does'nt accept a file name with 2 dot inside !
MyFile.mdb --> Ok
My.File.mdb --> "...How do I incorporate a "check-box" in Microsoft Excel?
I am building a spreadsheet that will have multiple options for the intended
user. I know that through Microsoft Excel you can build a "check-box" where
the user would just simply click on a box and it automatically is checked. I
just want to know how to do this.
If you want options it's better to use the option button, regardless they
are in either the forms toolbar or the control toolbox toolbar under
"app2121" <firstname.lastname@example.org> wrote in message
news:EE9440C0-F6E0-42B9-8A9D-D4B8ACD...Change the Range of a Name
Is There a possibility, to change the Range of a name-dependent of the value
of a Variable(perhaps Startrow rsp. Endrow)-with with a VBA-statement?
Thank you for a good hint!
dim Startrow as long
dim endrow as long
.range(.cells(startrow,"B"),.cells(endrow,"c")).name = "myName"
if myName already exists, this'll reassign it. If it didn't exist, it'll get
(Did I misunderstand the question?)
"H�lzl Otto" wrote:
> Is There a possibility, to change the Range of a name-dependent of the value
&g...Excel Formula for Column
Hello, I am new to excel and i have three columns
(A,B,C,ect) of info and about 5000 rows (1,2,3,ect). Not
sure if those are the correct names. I am trying to
figure out how i can get every number in the B column to
be multiplied by 7.25. All I have found on the net so
far is how to multiply it by another cell, but thats not
what I need. Can someone please help me or I am looking
at alot of extra hours. Thanks in advance.
Type 7.25 in an unused cell. Highlight the cell, select Edit, Copy, the
select all cells in column B you want multiplied, then select Edit, Paste
Special, Multipl...how to change width of label box in pie chart
In EXCEL 2003 (but all versions have given this problem)
When making a pie chart with long label names & especially when including
other data, (%, & values) the names wrap part to next line.
Is there a fix to make the label box wider?
The label width is controlled only by Excel, and you can't do much about it.
You could use a textbox instead, but it will not move with the pie wedge.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
"me@rrc" <me@email@example.com> wrote in message
news:A5CB...How to see my mail queue?
I have a Exchange 2000 running on Windows 2000 server.
Could you please tell me how to monitor my emails in my mail queue? I can't
find a location on the Windows to see the mail queue status.
Thanks in advance,
To view the mail queues follow these steps:
1. Open system manager, Navigate down to the following location
Your organization --> Administrative groups --> Your Admin group -->
Servers --> Your Server --> Protocols --> SMTP --> SMTP virtual server -->
You should then see all the smtp queues and monitor mail.
...I see you
Well, not really, but I used Google Earth to zoom in on the Flatiron
Building. You can get so close you can see the utilities on the roof,
the hydrants on the street, the little parkish area across 5th Avenue,
etc etc. Cool! Now tell me where your office is (like where is Peter's
window, what floor and facing what street). Google Earth lets you zoom
in all directions so I'll be able to turn the map to see that window.
Silly I know but still a connection on some level.
Pats looked good but not great last night. I didn't watch much more of
the game. I'm not sold on ...Extract First Name From String
How can I extract the First Name only from the String Below? I only want to
pick up 'Veronica'
I can get the Last Name by using CHARINDEX(',',[Patient Name])-1
[Patient Name] = LUNA,VERONICA E
Message posted via SQLMonster.com
SELECT LEFT(@Value, CHARINDEX(',', @Value)-1),
"Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message
> How can I extract the First Name only from the String Be...Duplicate Record Name (when attempting to add a Team)
I've got a new installation of CRM that I'm trying to learn to
At some point I had a team or queue that was called Field Support, and
deleted it, but now I can't use the name any more and I don't know
where to look to remove said name.
Thanks in Advance,
...The recipient name is not recongnized
Trying to send out via internet mail. Exchange for local
mail. When sending attachments I get error saying
recipient name not recognized MTS-IS Exchange....When
sending regulare mail it works fine. When sending
attachments via Outlook 2000 it works fine. Any ideas?
Not sure if it relates to your problem, but note that using Exchange &
Internet mail in the same profile in Outlook 2000 and prior versions is not
supported and can lead to all kinds of annoyances. Your Outlook is trying to
send Internet out via Exchange, it sounds like....what's your default mail
service?You can up...Sorting names from a list
I want to highlight all names in a particular column, say column A
starting with an alphabet value, say t, placed in a cell in anothe
column, say B2. So, names are listed in column A, B2 contains t. I wil
like to color all cells in column A that contain names strating with t
I would actually prefer to create a macro. Any help will be mos
Message posted from http://www.ExcelForum.com
Can't help you with a macro but this is very easily done
with conditional formatting.
Select the range to apply this to.
Select Conditional Formatting
Formula Is =LEFT(A1,1)...Formula for computing work time in Excel
For instance, A1 = 8:20, B1 = 16:30.
I need to compute in C1 the following: B1 - A1 - 0.5 hour.
What is the formula?
If I use formula B1 - A1 - 0:30 I get invalid value.
email: alex DOT vinokur AT gmail DOT com
"Alex Vinokur" <firstname.lastname@example.org> wrote in message
> For instance, A1 = 8:20, B1 = 16:30.
> I need to compute in C1 the fo...Entering Absolute Cell Location In A Formula
When I enter cell addresses into a formula, I can simply click on a cell, to
have that cell's relative location added into a formula ( = C1 + D2). I
want to click on the cells and have the absolute location added into a
formula (=$C1 + $D1). Is there a combination of <CTRL> or <SHIFT> etc. that
will do this?
Thanks a lot in advance.
= click C1, press F4, + click D2, press F4
"Alton B. Wilson" <email@example.com> wrote in message
> Hello All,
...Copying Named Ranges in Chart Series
I have a workbook with a tab that has charts and data. Because the
number of data points can vary month to month, I have set up the chart
to reference named ranges. The named ranges are columns that vary in
length, defined by language like this:
The X and Y series use the named ranges. If I add or remove data the
chart range automatically changes accordingly. These na...To See or Not To See
I have copied a few tables of text from a web page into Excel. Some of the
information contain word that are hyperlinks or email addresses. Is there a
way to change those columns of information to reveal the web address or email
So is it just displaying plaintext currently?
Have you seen this information from the Help Menu?
When you make changes to the Hyperlink and Followed Hyperlink styles, your
changes apply to all hyperlinks (hyperlink: Colored and underlined text or a
graphic that you click to go to a file, a location in a file, a Web page on
the World Wide Web...How do I set up and use a group box form control?
All I want to know is how to add a group box to a spreadsheet and add
checkboxes so that they're "embedded" in the group box. (Now when I drag the
group box around, the checkboxes don't move with it...)
Show the drawing toolbar.
There's an arrow icon that you can lasso the objects that you want grouped.
Then right click on the "grouped" selection and select Grouping. Then Group.
Click the arrow icon to get out of that mode.
> All I want to know is how to add a group box to a spreadsheet and add
> checkboxes so that they're ...E12 unable to see storage groups through EX MMC
We've been running E12 in a test environment for about 1 month and just today
ran into a situation where the storage groups for a server are no longer
visible from the console on that machine. If we view the server from a
console on a different EX server we see all storage groups and stores.
Any help is greatly appreciated
can you list the storage groups and stores from the shell (EMS) on that
did you try restarting EMC?
MVP - Exchange
NEW blog location: