How to link to a certain cell based on the value of another cell?
What I would like to do is tell excel to link a certain cell =$A$1 style, but
use the contents of another cell to specify this. For example, if the value
of A1 was the number 3, I would like to link to =$B$3. So far, the only
think I have found to work is using an if statement to say IF(A1=3, =$B$3)
but this isn't practical for a spreadsheet with many many rows. Is there a
way to have excel fundamentally do a =$B$(A1) where it uses the value of A1
to determine which column in B to link to? It seems like there has to be a
way to do this if an if statement can do this for a limite...VBA code to insert pictures in a column of cells
I have a folder of 99 pictures with filenames: image01.jpg, image02.jpg up
to image99.jpg. All of these images are small, and the same size.
I want to insert them (in order) in a column of cells (A1, A2, to A99) using
VBA. The images should be embedded, not linked. Resizing cells to the right
size would be a nice extra, but not necessary - it's easy in this case to
resize manually. Thanks for looking at my question - and I hope to hear some
...array in VBA
A few days ago I saw an answer to a post that showed the VBA equivalent of
using an array worksheet formula with ctrl+shift+enter. I forgot to copy
that answer for later use, now I can't find it anymore. Anybody who knows
what I mean? If so, please tell me where to find the post.
"Jack Sons" <email@example.com> wrote in message
> Hi all,
> A few days ago I saw an answer to a post that showed the VBA equiva...Need help updating 1 cell from other cells in same column
Hi and thanks in advanced for your attention. I am trying to do the following:
Have cell B12 be updated everyday automatically from 1 different cell when
data is found on those cells. Those cells contain a formula that sums 3 cells
in one same row. The total is of course put on those cells (B16 - B35).
To specify more on this problem let me give in that:
B16 represents the money earned on February 1st.
B17 represents the money earned on February 2nd
B18 the same for Feb. 3rd. and so on.
That data is only entered once per day. I tried link B12 to the value on
those cells, but ...VBA Passwords
I passwod protected by VBA modules so that no one could see them. But
have just come back from holidays forgetten my passwords and now
can't see them!
Any ideas how I can recover the
Message posted from http://www.ExcelForum.com
If you are using a newer version of excel that excel 97, then you will
either have to remember it or use a commercial password cracking program. If
you are using Excel 97 there is a free program called vbakey.exe that will
tell you your vba password.
Outgoing mail is certified Virus Free.
Checked ...Disallow Pasting over Validation
Is there a way to disallow a user from pasting over cells
for which I have Data Validation set? I'm trying to
prevent user input errors and I've successfully set
validation, but users keep pasting over the validation and
thus entering incorrect data.
Any help is greatly appreciated.
If you have a dropdown list in your data validation cells you can forma
the cell as locked and protect the sheet. The user can still selec
from the dropdown
Message posted from http://www.ExcelForum.com
This isn't perfect, but check it out
http://j-walk.com/ss/excel/ti...HELP WITH VBA
I am trying to finish writing some simple program.
My problem is that when I try and change the arguement from SELECTION
CHANGE to CHANGE, it doesn't work. Can someone help?
Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Sheet3").Range("B3").Value = 2 Then
My second problem is that I would like my data rather than appearing in
a cell, appear in a TEXT BOX. What do I do?
Copying your exact same post from 3 days ago word...VBA
Ok, all you expert Excel users may think this is dumb.....but what is VBA?
And what do you use it for? I looked around and can't figure a thing out.
Visual Basic for applications. Used to write macros/programs. As a
"Kim" <Kim@discussions.microsoft.com> schrieb im Newsbeitrag
> Ok, all you expert Excel users may think this is dumb.....but what is
> And what do you use it for? ...When pasting the cell content sometimes disappears when scrolling
When I paste information from a different source into Excel and scroll down
the text in certain columns is disappearing. When I scroll back up some of
it reappears and some disappears again. The template was originally created
in Excel 2003 (I think) and we've just upgraded to 2007 so I'm assuming it
has something to do with that as I've never come across this problem before.
Is there a setting I need to change or do we need to start the template all
over again. It's quite a complex template so that would have to be my last
option. Any help appreciated.
Many thanks...VBA function changing name
OK, here's a strange one - well strange to me anyway. I've declared a
function in a module using mixed case:
Function TMDE_Category (FormName As Form)
The function is called by the VBA code behind two different forms. I noticed
the other day that it appeared in the module as
Function tmde_category(FormName As Form)
Obviously, this isn't preventing the database application from running, but
I can't figure out why it changed. I changed it back to the mixed case
declaration, saved the module, exited the app, reopened it and looked. The
function had changed back t...Averaging non consecutive cells excluding zero's
how can I average non consecutive cells in a row excluding zero's
all cells in the range (A1:K1)have values in them but only the sum of cetain
cell need to be averaged
any help is a appreciated.
I didn't condtiional the sum as adding zero wouldn't change anything.
Sorry, that should be:
A little too much copy & paste in the formula bar in the orig...Locking cell color pattern when sorting rows?
I am creating a spreadsheet where I want every alternating row to have
a light grey solid pattern that acts as shading, so it is easier to
follow individual rows from column to column on a printed page.
The problem is that when I choose to sort rows by a specific data
property in any column, the shading pattern becomes messed up, because
there doesnt seem to be a way to lock the shaded pattern of each cell.
Is there a solution to this problem? Thanks
Might this be a solution ?
Seperate lines between groups of data by using conditional formatting.
- In a spreadsheet, build a table....Paste link only
I have worksheet (destination) in which several cells are linked to a
worksheet (source) in another workbook. My destination worksheet has
not been updated with the latest values in the source sheet, so all
the cells are filled with 0s, etc.
Cell D2 in my destination worksheet is currently linked to V2 in the
source worksheet. I'd like to make E2 be linked to V2. When I click on
V2 (source), choose Copy, click on E2 (destination), and choose Paste
Special > Paste Links, two problems occur:
1. The link is copied but it's copied as $V$2 (whereas all of the
other links don't have...Lookup Cell Address
I'm trying to lookup a value in a list, but return the cell address (or row
number) of where the value was found instead of the value itself. So if my
table starts in A1 and looks like this...
When look up the value of 3 I want to return A1 (or 1). I would use a
macro, but that's not an option in this particular case.
On Thu, 11 Feb 2010 17:47:02 -0800, hmmm <firstname.lastname@example.org>
>I'm trying to lookup a value in a list, but return the cell address (or row
>number) of where the value was found instead of the value its...Too many different cell formats.
Is it possible to open an Excel spreadsheet that only
displays the message: "Too many different cell formats" ?
I have never seen it. My guess is NO. But I am not positive.
"Thomas" <email@example.com> wrote in message
> Is it possible to open an Excel spreadsheet that only
> displays the message: "Too many different cell formats" ?
Previous post from Dave McRitchie in response to a similar question:-
Formatting is not going to result in data loss.
Fo...Serious error during VBA event
We've added VBA code to several windows. Now, all of a sudden, we're getting
this error message, "A serious error occured during a VBA event", for
non-admin users. This is a Citrix environment and we do not get the error
when we log onto Citrix as an administrator.
We found knowledgebase article # 929612 which tells us to give full control
to four registry keys for all users. We tried this but we're still getting
...Callout use: Applying callouts to 3,000 cells similar to iPhone sms.
Operating System: Mac OS X 10.6 (Snow Leopard)
Hey, <br><br>I've got an excel sheet with my sms messages between my girlfriend and I. Theres about 3,000 rows of texts in two columns (each column has one person's side of the conversation). <br><br>I'm would like to format each cell with a "callout" (the cartoonish speech bubble) similar to messaging format used on an iPhone so that I could get a small booklet of our conversations printed out. <br><br>Is there any quick way to format all of thes...entering a date into cell and get ## symbols
i am entering a date value into a cell and i that it shows is "##" Even when
i print it shown "##" and no date why?
Make the cell wider
"Steve" <Steve@discussions.microsoft.com> wrote in message
>i am entering a date value into a cell and i that it shows is "##" Even
> i print it shown "##" and no date why?
> i am entering a date value into a cell and i that it shows is "##" Even when ...Paste as text not HTML
In WOrd 2007 one can FINALLY paste info from a web page as text as the
default, not as HTML. Is this available in Excel 2007? I can't find it.
Für Elise - Support the Breast Cancer & Heart Foundations
...Find vba problem
I'm using the following to find text in cells:
Cells.Find(What:=Range("D3").Value, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:= _
xlByRows, SearchDirection:=xlNext, MatchCase:= _
The formula works if it only text in the cell. What if the text is the
same only as a result of the formula in the cell, the above doesn'
I take it there is no match as above is looking at the formula in th
cell and not the text? Is there any way it can to changed to recognis
the text and not the formula
Message posted from http...cut and paste not working in publisher
i am trying to copy text from web sites and paste into a pub 2000 document.
i have been doing this for years but now my pub 2000 documents are not
importing the pasted text but i can paste into word documents. i have then
tried to paste from word to publisher and i get the following error message:
An error occured while attempting to import the text. Publisher is unable to
successfully import the text.
why cant i import text into publisher?
Have you tried "paste special" and selected unformatted text? Have you cleared
your Internet temporary cache recently? In ...VBA
ActiveCell.Formula = "=HighLevelSummary_%!$C$5"
When I run this code, I get Run Time Error - 1004
Application defined or object defined error.
I will appreciate assistance in identifying the problem
with the code.
- are the 2 sheets. In cells - J53-K53 I want to put the
link to cell from sheet HighLevelSummary cell C5.
try this. Modify to suit.
'Set frng = Range("h8:h" & Range("a65536").End(xlUp).Row)
set frng=range("j53:...Re: Excel VBA
i have a problem with Excel (maybe it's the easiest thing in the world
I don't know, I'm new to Excel):
Let's say I have following formula into cell A2: =SUM(A1:A1)
Now, a row is inserted at A1, so the formula is now into cell A3. Th
formula itself is now SUM(A2:A2), the reference has change
So far, so good.
But: I want the formula to add the values of ALL cells above, i.e. th
formula should be SUM(A1:A2) if I insert one row. If I insert anothe
row, the formula should be SUM(A1:A3) - NOT SUM(A3:A3). I think th
best way to solve this is VBA...
I ho...VBA equivalent for Excel "indirect()" function in UDF
I often pick random elements from a list (named range), whose name is typed
However, the attempt to make this a UDF function in VBA fails, since
"INDIRECT()" is not part of the "worksheetfunction"-collection in VBA.
Is there any equivalent that may do the trick ?
Thank you in advance.
I don't believe there is a direct equivalent in VBA for INDIRECT. However,
you can use Offset to refer other cells.
myValue = Range("B2...VBA
I have a column in my spreadsheet that contains the column header one or
more items underneath. Is there a generic method to select all the items
I have tried...
Set myRange = myWorksheet.Range("A2")
Set myRange = myWorksheet.Range(myRange , myRange.End(xlDown))
....but if the column only contains one item, the range is from that item to
the bottom of the spreadsheet.
Set myRange = .Range("A2:A" & _
.Range("A" & .Rows.Count).End(xlUp).Row)
In article <403a40b2...