Vlookup based on Active cell

Is there a way to have a vlookup formual based on the data in the
active cell. I have data on one sheet that I what to pull in to a cell
on another worksheet based on the data in the active cell I click on.
I looking for the reference of the vlookup to be the active cell.

thanks in advance for any help on this
0
5/8/2009 6:43:57 PM
excel 39879 articles. 2 followers. Follow

7 Replies
1000 Views

Similar Articles

[PageSpeed] 54

Thomp <williamth...@gmail.com> wrote...
>Is there a way to have a vlookup formual based on the data in the
>active cell. I have data on one sheet that I what to pull in to a cell
>on another worksheet based on the data in the active cell I click on.
>I looking for the reference of the vlookup to be the active cell.

Yes and no. VLOOKUP can use the value in the active cell as its 1st
(3rd or 4th) argument as follows

=VLOOKUP(CELL("Contents"), . , . [, . ])

but Excel won't recalc as you move the cell pointer, so you'd either
need to press [F9] to recalc every time you move the cell pointer.
Note: SelectionChange won't quite work: if you have a multiple cell
range selected, then you can change the active cell within the
selected range using [[Shift]+][Enter] or [[Shift]+][Tab], but that
won't trigger the SelectionChange event.

Also, the formula above will throw a circular recalc warning when you
enter it and when you recalc when the cell containing it is active.

That said, I'm not sure this would be useful for anything interactive,
and there are likely much better ways to do what you want using
macros, but you'd need to provide more details about exactly what
you're trying to do.
0
hrlngrv1 (375)
5/8/2009 7:30:07 PM
On May 8, 2:30=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> Thomp <williamth...@gmail.com> wrote...
> >Is there a way to have a vlookup formual based on the data in the
> >active cell. I have data on one sheet that I what to pull in to a cell
> >on another worksheet based on the data in the active cell I click on.
> >I looking for the reference of the vlookup to be the active cell.
>
> Yes and no. VLOOKUP can use the value in the active cell as its 1st
> (3rd or 4th) argument as follows
>
> =3DVLOOKUP(CELL("Contents"), . , . [, . ])
>
> but Excel won't recalc as you move the cell pointer, so you'd either
> need to press [F9] to recalc every time you move the cell pointer.
> Note: SelectionChange won't quite work: if you have a multiple cell
> range selected, then you can change the active cell within the
> selected range using [[Shift]+][Enter] or [[Shift]+][Tab], but that
> won't trigger the SelectionChange event.
>
> Also, the formula above will throw a circular recalc warning when you
> enter it and when you recalc when the cell containing it is active.
>
> That said, I'm not sure this would be useful for anything interactive,
> and there are likely much better ways to do what you want using
> macros, but you'd need to provide more details about exactly what
> you're trying to do.


I seem to have the formula wrong somehow. See below what I wrote. I
have a macro from John walkenbach's book that I think I can use to
make it recalculate if I can just get the formula right.

=3DVLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)

What I am ultimately trying to do is that I have two sheets one with
all of the data and another one which is kind of like my dashboard. I
want to be able to populate the cells on the dashboard based on a
vlookup that retrieves data from the data sheet in which the data in
the active cell matches the data on the data sheet

thanks,
Thomp
0
5/8/2009 8:20:22 PM
Thomp <williamth...@gmail.com> wrote...
>Harlan Grove <hrln...@gmail.com> wrote:
....
>> =VLOOKUP(CELL("Contents"), . , . [, . ])
....
>I seem to have the formula wrong somehow. See below what I wrote. I
>have a macro from John walkenbach's book that I think I can use to
>make it recalculate if I can just get the formula right.
>
>=VLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)

Yup, that's wrong. CELL is a function, and CELL("Contents") is a
function call that returns the value of the cell that was active at
last recalc.

>What I am ultimately trying to do is that I have two sheets one with
>all of the data and another one which is kind of like my dashboard. I
>want to be able to populate the cells on the dashboard based on a
>vlookup that retrieves data from the data sheet in which the data in
>the active cell matches the data on the data sheet

I suspect what you want to do is use the first column of your table of
data as a LIST data validation source for one cell in your dashboard
worksheet. That is, in the dashboard worksheet, name a cell ID, format
it as unlocked, apply data validation to it (a list source with the
list being a reference to the leftmost column of your data table),
then make the formulas in your dashboard workbook similar to

=VLOOKUP(ID,ReferenceToYourDataHere,column_index,0)

Data validation would add a drop-down list to the cell named ID, so
users could click on the down arrow button on the right side of the
cell and scroll through the drop-down list to select the records from
the data table. Changing the ID cell *DOES* trigger recalculation, so
Excel would update all VLOOKUP calls referring to ID when ID changes.
0
hrlngrv1 (375)
5/8/2009 9:25:17 PM
=VLOOKUP(CELL("contents"),'SAN Account List'!A2:S75,2,FALSE)

I guess I don't understand why a simple =vlookup() in a separate cell couldn't
be used.




Thomp wrote:
> 
> On May 8, 2:30 pm, Harlan Grove <hrln...@gmail.com> wrote:
> > Thomp <williamth...@gmail.com> wrote...
> > >Is there a way to have a vlookup formual based on the data in the
> > >active cell. I have data on one sheet that I what to pull in to a cell
> > >on another worksheet based on the data in the active cell I click on.
> > >I looking for the reference of the vlookup to be the active cell.
> >
> > Yes and no. VLOOKUP can use the value in the active cell as its 1st
> > (3rd or 4th) argument as follows
> >
> > =VLOOKUP(CELL("Contents"), . , . [, . ])
> >
> > but Excel won't recalc as you move the cell pointer, so you'd either
> > need to press [F9] to recalc every time you move the cell pointer.
> > Note: SelectionChange won't quite work: if you have a multiple cell
> > range selected, then you can change the active cell within the
> > selected range using [[Shift]+][Enter] or [[Shift]+][Tab], but that
> > won't trigger the SelectionChange event.
> >
> > Also, the formula above will throw a circular recalc warning when you
> > enter it and when you recalc when the cell containing it is active.
> >
> > That said, I'm not sure this would be useful for anything interactive,
> > and there are likely much better ways to do what you want using
> > macros, but you'd need to provide more details about exactly what
> > you're trying to do.
> 
> I seem to have the formula wrong somehow. See below what I wrote. I
> have a macro from John walkenbach's book that I think I can use to
> make it recalculate if I can just get the formula right.
> 
> =VLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)
> 
> What I am ultimately trying to do is that I have two sheets one with
> all of the data and another one which is kind of like my dashboard. I
> want to be able to populate the cells on the dashboard based on a
> vlookup that retrieves data from the data sheet in which the data in
> the active cell matches the data on the data sheet
> 
> thanks,
> Thomp

-- 

Dave Peterson
0
petersod (12004)
5/8/2009 9:30:28 PM
On May 8, 4:25=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> Thomp <williamth...@gmail.com> wrote...
> >Harlan Grove <hrln...@gmail.com> wrote:
> ...
> >> =3DVLOOKUP(CELL("Contents"), . , . [, . ])
> ...
> >I seem to have the formula wrong somehow. See below what I wrote. I
> >have a macro from John walkenbach's book that I think I can use to
> >make it recalculate if I can just get the formula right.
>
> >=3DVLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)
>
> Yup, that's wrong. CELL is a function, and CELL("Contents") is a
> function call that returns the value of the cell that was active at
> last recalc.
>
> >What I am ultimately trying to do is that I have two sheets one with
> >all of the data and another one which is kind of like my dashboard. I
> >want to be able to populate the cells on the dashboard based on a
> >vlookup that retrieves data from the data sheet in which the data in
> >the active cell matches the data on the data sheet
>
> I suspect what you want to do is use the first column of your table of
> data as a LIST data validation source for one cell in your dashboard
> worksheet. That is, in the dashboard worksheet, name a cell ID, format
> it as unlocked, apply data validation to it (a list source with the
> list being a reference to the leftmost column of your data table),
> then make the formulas in your dashboard workbook similar to
>
> =3DVLOOKUP(ID,ReferenceToYourDataHere,column_index,0)
>
> Data validation would add a drop-down list to the cell named ID, so
> users could click on the down arrow button on the right side of the
> cell and scroll through the drop-down list to select the records from
> the data table. Changing the ID cell *DOES* trigger recalculation, so
> Excel would update all VLOOKUP calls referring to ID when ID changes.

Thank Harlan..Both of these work great...Kudos!!!!
0
5/9/2009 7:26:21 PM
On May 9, 2:26=A0pm, Thomp <williamth...@gmail.com> wrote:
> On May 8, 4:25=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
>
>
>
>
>
> > Thomp <williamth...@gmail.com> wrote...
> > >Harlan Grove <hrln...@gmail.com> wrote:
> > ...
> > >> =3DVLOOKUP(CELL("Contents"), . , . [, . ])
> > ...
> > >I seem to have the formula wrong somehow. See below what I wrote. I
> > >have a macro from John walkenbach's book that I think I can use to
> > >make it recalculate if I can just get the formula right.
>
> > >=3DVLOOKUP(CELL,('SAN Account List'!A2:S75,2,FALSE)
>
> > Yup, that's wrong. CELL is a function, and CELL("Contents") is a
> > function call that returns the value of the cell that was active at
> > last recalc.
>
> > >What I am ultimately trying to do is that I have two sheets one with
> > >all of the data and another one which is kind of like my dashboard. I
> > >want to be able to populate the cells on the dashboard based on a
> > >vlookup that retrieves data from the data sheet in which the data in
> > >the active cell matches the data on the data sheet
>
> > I suspect what you want to do is use the first column of your table of
> > data as a LIST data validation source for one cell in your dashboard
> > worksheet. That is, in the dashboard worksheet, name a cell ID, format
> > it as unlocked, apply data validation to it (a list source with the
> > list being a reference to the leftmost column of your data table),
> > then make the formulas in your dashboard workbook similar to
>
> > =3DVLOOKUP(ID,ReferenceToYourDataHere,column_index,0)
>
> > Data validation would add a drop-down list to the cell named ID, so
> > users could click on the down arrow button on the right side of the
> > cell and scroll through the drop-down list to select the records from
> > the data table. Changing the ID cell *DOES* trigger recalculation, so
> > Excel would update all VLOOKUP calls referring to ID when ID changes.
>
> Thank Harlan..Both of these work great...Kudos!!!!- Hide quoted text -
>
> - Show quoted text -

Just wanted to add this last piece to my question. I used this macro
from John Wakenbach's book and it will make the active cell recalute
without having to hit F9..
Here is the macro..
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveCell.Row > 1 And ActiveCell.Row < 75 Then _
        ActiveSheet.Calculate

End Sub
0
5/11/2009 2:39:51 PM
Thomp <williamth...@gmail.com> wrote...
....
>from John Wakenbach's book and it will make the active cell recalute
>without having to hit F9..
>Here is the macro..
>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>=A0 =A0 If ActiveCell.Row > 1 And ActiveCell.Row < 75 Then _
>=A0 =A0 =A0 =A0 ActiveSheet.Calculate
>End Sub

Try selecting C3:H20 and move around within the selection using
[Enter], [Shift]+[Enter], [Tab], [Shift]+[Tab] and watch Excel *not*
recalc.

0
hrlngrv1 (375)
5/11/2009 6:49:19 PM
Reply:

Similar Artilces:

Sheet protection causes cells to be linked
I'm using Excel 2000 and I've got a sheet which I want to protect, so can lock the non-input cells. I've also got a button on the shee which runs a macro. When I protect the sheet, all cells look as they are hyperlinked, o macro-linked (I get the hand pointer). If I click on the cell, it the goes off to do something (looks like a call to a macro - which ISN'T th one on the sheet!). If I use freeze pane, it's only the cells above the pane marker whic operate this way - the ones below the pane operate normally! Finally - if I open the sheet in Excel 2003 - all the cells op...

CRM3.0 Question on Activity
Hello, I am using CRM3.0. I have the following case. 1. Create Campaign 2. Add contact to campaign list 3. Create Campaign activity - Phone call 4. Distribute activity 5. CRM User will make phone call 6. When contact is interest, CRM user will make a "Free Trail" for contact. I am having troble in step 6. So far as I know, I have two choices for "Free Trail". A) Service Activity If I choose to create a service activity, then I can not link up the service activity with the campaign or phone call. B) Appointment If I choose appointment, I can set "regarding"...

Matching records based on multiple columns
I need to compare records from two different sets of data. Set 1 is a master list and Set 2 is a sublist of the master. The comparison involves three columns from each set; Tag#, Location, S/N. I want to be able to match records from Set 2 to Set 1, ending with an additional column that shows if there is a match (true/false). What is the most efficient way of completing this task? Thanks concatenate a column that contains the 3 required fields on both tabs, then do a vlookup. -- -John http://www.jmbundy.blogspot.com/ Please rate when your question is answered to help us and ot...

Active Directory Connector
Last summer, I upgraded our Exchange 5.5 box and used W2K server instead of NT4 server. This summer, I am going to the PDC's and BDC's to W2K server, but we will remain in mixed mode for a while. I have a test network set up and I have the old Exchange 5.5 server on it. I have already upgraded the PDC to W2K, but will not be able to upgrade the Exchange server. I followed the instructions in MS KB article 301036 about installing the Active Directory Connector for use in managing the Exchange 5.5 server through the Active Directory Users and Computers MMC, however it does not...

Grouping based on minimums
So, I have a series of loans (eg, mortgages) in one table - the loan data include credit scores for the holders of the loan). In another table, I have segments based on credit score. I need to create a query that groups the loans by segment. So.. Loan Table: ID FICO_1 FICO_2 Amount 1 750 699 100,000 2 650 700 200,000 3 750 710 222,000 4 676 699 300,000 5 720 729 150,000 Segment Table: FICO_1 FICO_2 Segment 720 700 Good 700 680 OK 680 ...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Conditional cell color.
I have used conditional formatting before in order to change the shading of a cell based on the cell value. However, I would like to do the same except have the color change based only on the alpha part of an alphanumeric value. Example: change shading to blue if the alpha = F, and change to yellow if = P (as in: F2409 & P1982). Thank you. Geza, use this formula: =SEARCH("f",A1). If you want it to be case-sensitive, use =FIND("F",A1). You know how to do the rest... -- DDM "DDM's Microsoft Office Tips and Tricks" Visit us at www.ddmcomputing.com &...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Reference another workbook (variable name) inside a formula cell
I use a formula to reference a another workbook cell. The other workbook name can changes after sent to another party and returned ammended. Rather than change the name of the spreadsheet, I would prefer to change the reference in a cell of the first spreadsheet. The formula below activates the lookup if cell c75 is Y. I would like to replace the text [Midwest G1 5000.xls] with a cell containing the name. =IF($C75="Y",SUMIF('[Midwest G1 5000.xls]Summary'!$B:$B,F$4,'[Midwest G1 5000.xls]Summary'!$J:$J),0) I have tried using "&" and direct cell references...

Mouse Move response based on combo box value
Hello, all. I have been asked to add a feature to an exiting form where a "note" will appear on mouse over only if a combo box of a continuous form is a particular value (i.e. Annual Safety Training). The main form is frmEmployees, the subform is subformClassesAttended. The combo box is Combo12, but it is displaying the text description (colmun 2) associated with a bound field of a number type, classID. I have a hidden label, lblInfo, with the info to be displayed but am not sure of the coding & whether I should be referencing the description in Combo12 or Class...

Internet Based Database
Hi All, I Was Woundering , Whats The Best Application to use, Where I Can Create a Database Which is in Access, But Instead of using forms the Front End,it is Webased, Regards Danny Don't use Access if you can possibly avoid it. It's not suitable for anything more than very basic web applications. You will have to do all communication with the database via OLEDB connections and SQL directly to the Jet engine. Your database will have to sit on the web server. If for any reason the database locks up, which can happen easily with an Access DB, your website will grind to a comple...

Merging cells #4
I am using an existing worksheet in Excel. Some cells have already been merged. I need to merge 2 more cells and i am unable to accomplish that. The "merge/center button" on the toolbar is not highlighted, so i am unable to merge the needed cells. Any suggestions? Any chance your worksheet is protected? brownk wrote: > > I am using an existing worksheet in Excel. Some cells have already been > merged. I need to merge 2 more cells and i am unable to accomplish that. > The "merge/center button" on the toolbar is not highlighted, so i am unable > to m...

Formatting Cell Fractions... HELP
Using Excel I formatted cells using fractions as quarters. how can I convert the 2/4 to 1/2 now? all of the numbers of my cells now show either 1/4, 2/4 or 3/4. I wanted to be able to change the 2/4 for 1/2 without having to format each one. Thank you Before I formatted the number showed 101.5, 102.8 - after formatting 101 2/4, 102 3/4. I was trying to find a way to change all the 2/4 for 1/2. Please don't multi-post You have an answer in your other thread -- Kind regards, Niek Otten Microsoft MVP - Excel "Twicebest" <Twicebest@discussions.microsoft.com> wrote...

Turning off Active Directory Sync for Groups
Project Server 2007 was installed and set up for my company a couple of months ago by an outside vendor. This vendor set up the system to use Actice Directory as a way to assign Groups to PWA users. Because our IT is outsourced, using Active Directory to manage Groups has become a very cumbersome, very slow process, it has now been decided that we would like to turn off this sync. Going forward, we would like to manually add/remove Groups for users. Has anyone done this before? If yes, is there anything I should know before proceeding? I do not foresee it, but will do...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

colorize celle from a range of date
www.gssitaly.com/calendar.zip This is a simple planninig of vacation i would want a macro assigned in to a botton in the sheet FERIESA_FATTE that make: click on the botton and automaticly from every line of the sheet FERIESA_FATTE it colorized the 2 other sheet in base of the month example: 36755 6500 D'AGOSTINO NICOLA have arange 02/01 05/01 colorozie in red with a little border the sheet PRIMO_SEMESTRE range E6:H6 have arange 15/03 colorozie in red with a little border the sheet PRIMO_SEMESTRE range BZ6 have arange 14/07 16/07 colorozie in red with a little border the sheet SECONDO...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Referencing relative cell
I'm am trying to reference a cell one row up and three to the right to check if it is zero and I get a formula error. Can someone help with this? Thanks. Michael =IF(R[-1]C[+3]>0,AG5*E4,0) Michael You can't mix A1 and R1C1 reference styles. You have to use whichever one is active in the Tools>Options>General tab If you are using A1 style you will need to enter the actual cell reference. If you're not familiar with Absolute and Relative reference styles you can read up on them in the Help files Good Luck Mark Graesse mark_graesser@yahoo.co ----- Michael wr...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

enter same text into many different cells
How can I enter an "x" into many different cells on a worksheet? For example, I may want one in A7, A23, A44, B3, B8, B12, C1, . . . Is there a way to just enter those cell names and have an "x" go there instead of scrolling all around the worksheet? Thanks! One way, put "x" in an empty cell, copy the cell, press F5 and type in the cell addresses delimited by a comma, press enter or click OK. Press Ctrl + V to paste in the "x" -- Regards, Peo Sjoblom "Christmas Ape" <anonymous@discussions.microsoft.com> wrote in message new...

How to add amount to a cell based on category and month
I have a list of expenxe categories and by month along the top. How can I write the sumif function based on two criteria from drop-down list(one list is Expenses and the second list is the Months) and the third (Amount) cell would be where I input an amount to be added into the table: Expenses month amount Fuel JAN _______ Expenses JAN FEB MAR Trucks/Auto Fuel 348.23 Oils 35.99 Maintenance 298.33 Parts 59.21 DMV 745.87 -- so many functions...!?!?!?!?!?!? If you are trying to fill a matr...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

hyperlinks & cell reference
Hi folks, Everytime I insert a new column into my Excel table the hyperlinks which are linked to the cells don't change automaticlly. Thus I've to change every following hyperlink per hand. Is there an option to relate hyperlinks to an other reference point than the cells? Thank you for your time in advance Joerg If I gave the range a nice name, I could use that in my hyperlink: Using the =hyperlink() worksheet formula: =HYPERLINK("#testname","your friendly name here") TestName referred to c9. Inserting a new column and now it referred to D9. Joerg Ce...