Making VLOOKUP Range Static

I have an excel file that has about 17 sheets.  On three of those
sheets I have VLOOKUP's that look at the other sheets.  I load the
other sheets with data and use a macro to format the data how I want
it.  During these macros I do add some Columns to the sheets.  The last
thing I do is recalucate to get the latest data from the VLOOKUP's.
However, when I add the columns to the sheets it changes the ranges on
my VLOOKUP's.  I do not want that to occur.  How do I make the range
static?  I have tried the following, "$D:$Q", that does not do it.
what do I need to do?

0
7/31/2006 8:40:28 PM
excel 39879 articles. 2 followers. Follow

5 Replies
816 Views

Similar Articles

[PageSpeed] 45

Hi!

Try it like this:

=VLOOKUP(A1,INDIRECT("D:Q"),2,0)

Biff

"schmid" <steve.schmid@ge.com> wrote in message 
news:1154378428.193251.152660@m79g2000cwm.googlegroups.com...
>I have an excel file that has about 17 sheets.  On three of those
> sheets I have VLOOKUP's that look at the other sheets.  I load the
> other sheets with data and use a macro to format the data how I want
> it.  During these macros I do add some Columns to the sheets.  The last
> thing I do is recalucate to get the latest data from the VLOOKUP's.
> However, when I add the columns to the sheets it changes the ranges on
> my VLOOKUP's.  I do not want that to occur.  How do I make the range
> static?  I have tried the following, "$D:$Q", that does not do it.
> what do I need to do?
> 


0
biffinpitt (3172)
7/31/2006 9:12:00 PM
Biff wrote:
> Hi!
>
> Try it like this:
>
> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
>
> Biff
>
> "schmid" <steve.schmid@ge.com> wrote in message
> news:1154378428.193251.152660@m79g2000cwm.googlegroups.com...
> >I have an excel file that has about 17 sheets.  On three of those
> > sheets I have VLOOKUP's that look at the other sheets.  I load the
> > other sheets with data and use a macro to format the data how I want
> > it.  During these macros I do add some Columns to the sheets.  The last
> > thing I do is recalucate to get the latest data from the VLOOKUP's.
> > However, when I add the columns to the sheets it changes the ranges on
> > my VLOOKUP's.  I do not want that to occur.  How do I make the range
> > static?  I have tried the following, "$D:$Q", that does not do it.
> > what do I need to do?
> >

The VLOOKUP is looking at a range that is on another sheet in the file.
 What is the proper syntax to look at another sheet?  where in the text
does the sheet name go?

Schmid

0
7/31/2006 11:24:59 PM
"schmid" <steve.schmid@ge.com> wrote in message 
news:1154388299.018843.72810@m79g2000cwm.googlegroups.com...
>
> Biff wrote:
>> Hi!
>>
>> Try it like this:
>>
>> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
>>
>> Biff
>>
>> "schmid" <steve.schmid@ge.com> wrote in message
>> news:1154378428.193251.152660@m79g2000cwm.googlegroups.com...
>> >I have an excel file that has about 17 sheets.  On three of those
>> > sheets I have VLOOKUP's that look at the other sheets.  I load the
>> > other sheets with data and use a macro to format the data how I want
>> > it.  During these macros I do add some Columns to the sheets.  The last
>> > thing I do is recalucate to get the latest data from the VLOOKUP's.
>> > However, when I add the columns to the sheets it changes the ranges on
>> > my VLOOKUP's.  I do not want that to occur.  How do I make the range
>> > static?  I have tried the following, "$D:$Q", that does not do it.
>> > what do I need to do?
>> >
>
> The VLOOKUP is looking at a range that is on another sheet in the file.
> What is the proper syntax to look at another sheet?  where in the text
> does the sheet name go?
>
> Schmid

Like this:

=VLOOKUP(A1,INDIRECT("'Sheet_name'!D:Q"),2,0)

Biff


0
biffinpitt (3172)
8/1/2006 1:16:31 AM
Biff wrote:
> "schmid" <steve.schmid@ge.com> wrote in message
> news:1154388299.018843.72810@m79g2000cwm.googlegroups.com...
> >
> > Biff wrote:
> >> Hi!
> >>
> >> Try it like this:
> >>
> >> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
> >>
> >> Biff
> >>
> >> "schmid" <steve.schmid@ge.com> wrote in message
> >> news:1154378428.193251.152660@m79g2000cwm.googlegroups.com...
> >> >I have an excel file that has about 17 sheets.  On three of those
> >> > sheets I have VLOOKUP's that look at the other sheets.  I load the
> >> > other sheets with data and use a macro to format the data how I want
> >> > it.  During these macros I do add some Columns to the sheets.  The last
> >> > thing I do is recalucate to get the latest data from the VLOOKUP's.
> >> > However, when I add the columns to the sheets it changes the ranges on
> >> > my VLOOKUP's.  I do not want that to occur.  How do I make the range
> >> > static?  I have tried the following, "$D:$Q", that does not do it.
> >> > what do I need to do?
> >> >
> >
> > The VLOOKUP is looking at a range that is on another sheet in the file.
> > What is the proper syntax to look at another sheet?  where in the text
> > does the sheet name go?
> >
> > Schmid
>
> Like this:
>
> =VLOOKUP(A1,INDIRECT("'Sheet_name'!D:Q"),2,0)
> 
> Biff


Biff

Worked like a charm...  Thanks so much bud!

Steve

0
8/1/2006 3:34:33 AM
You're welcome. Thanks for the feedback!

Biff

"schmid" <steve.schmid@ge.com> wrote in message 
news:1154403273.408203.181590@s13g2000cwa.googlegroups.com...
>
> Biff wrote:
>> "schmid" <steve.schmid@ge.com> wrote in message
>> news:1154388299.018843.72810@m79g2000cwm.googlegroups.com...
>> >
>> > Biff wrote:
>> >> Hi!
>> >>
>> >> Try it like this:
>> >>
>> >> =VLOOKUP(A1,INDIRECT("D:Q"),2,0)
>> >>
>> >> Biff
>> >>
>> >> "schmid" <steve.schmid@ge.com> wrote in message
>> >> news:1154378428.193251.152660@m79g2000cwm.googlegroups.com...
>> >> >I have an excel file that has about 17 sheets.  On three of those
>> >> > sheets I have VLOOKUP's that look at the other sheets.  I load the
>> >> > other sheets with data and use a macro to format the data how I want
>> >> > it.  During these macros I do add some Columns to the sheets.  The 
>> >> > last
>> >> > thing I do is recalucate to get the latest data from the VLOOKUP's.
>> >> > However, when I add the columns to the sheets it changes the ranges 
>> >> > on
>> >> > my VLOOKUP's.  I do not want that to occur.  How do I make the range
>> >> > static?  I have tried the following, "$D:$Q", that does not do it.
>> >> > what do I need to do?
>> >> >
>> >
>> > The VLOOKUP is looking at a range that is on another sheet in the file.
>> > What is the proper syntax to look at another sheet?  where in the text
>> > does the sheet name go?
>> >
>> > Schmid
>>
>> Like this:
>>
>> =VLOOKUP(A1,INDIRECT("'Sheet_name'!D:Q"),2,0)
>>
>> Biff
>
>
> Biff
>
> Worked like a charm...  Thanks so much bud!
>
> Steve
> 


0
biffinpitt (3172)
8/1/2006 6:59:08 AM
Reply:

Similar Artilces:

My text seems stuck in the text box! I can't make changes!HELP!
I am trying to alter text just typed into a text box using Publisher 2003. For some reason, the system will not allow me to go back to edit. Any ideas? Thanks Is your application activated? Is it just this one text box in this publication? Are you working with a template? Select the text box, copy, paste, can you edit the pasted text box? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Learner" <Learner@discussions.microsoft.com> wrote in message news:68E3A7B3-BB72-4D73-9250-BF4F3FAE2C08@microsoft.com... >I a...

Static and query based dist groups
Hello, I'm implementing a QBDG (query based distribution group) in exchange 2003 to contain all full time employees and another to include all part time employees. I've got the LDAP search down and it works perfectly. The problem is that my bosses want to be able to opt in to the particular lists. I need to be able to create a static DG that contains the opt ins as well as the QBDG. How do you add a QBDG into a static DG? "adam432823" <adam432823@discussions.microsoft.com> wrote: >Hello, I'm implementing a QBDG (query based distribution group) in excha...

Advanced Search Criteria
Just an FYI, since there doesn't seem to be a solution posted anywhere on the web, at least that I could find. If you want to search using dynamic dates, at least in the case of finding items older than a certain relative date, such as more than 30 days old, I used the following criteria successfully in Outlook 2007: Advanced Find > Advanced (tab) > Field: Sent Condition: On or after Value: 30 days ago ...

global hooks in a static lib?
I developed an extensive MFC Extension DLL for GUI developement. Suddenly, my boss wanted a static library version as well so we can ship a single EXE to customers for the "free version". Unfortunately, this broke some of my stuff since I was using global hooks in one place (SetWindowsHookEx). Does anyone know of any tricks to use a global hook in a static lib/EXE? There is a single EXE file requirement... The only idea I have so far is to move the hook function into a DLL and then encode that DLL into the .LIB and write out a temporary DLL file at runtime and delete it afterward...

vlookup #53
Is there a way to use vlookup depending on what is in a cell. example I have several ranges on separate tabs each tab is numbered lets say they are 324, 345, 567,321 Each range is the same a1..j50 In the input sheet if I put in cell c4 the number 324 I want it to lookup in the range that is in tab 324 The lookup starts in cell f 10 of the input sheet Thanks in advance Ed Davis Modify to suit =VLOOKUP(2,INDIRECT(E21&"!$A$2:$C$44"),2,0) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Ed Davis" <ed.davis1@verizon.net> wrote in ...

Sheet range reference
I have a workbook with a number of sheets, let's say named sheet01, sheet02 ......sheet10. These sheets are of the same structure and are representing some development in time. So now I would like to make a chart (or separate table) the where the X coordinate is the sheet number and the Y some cell on each of the sheets. Is this possible with a simple reference like the ranges within the same chart or do I really have to create a macro or do it by hand? Jens. Each series must reside on a single sheet. You can create a summary sheet which has something like: A1: =Sheet1!A1 A2: ...

Using possibly If and Vlookup
I have a spreadsheet where I want to first of all look up hours worked in cell N2 (within this cell is the number 35.00 or the number 37.00). I then want to use this cell to do a lookup bringing back one salary if N2 is 35 or another salary if N2 is 37. In my main spreadsheet I have a spinal column point in cell J2. I have created two lookup tables on another spreadsheet named salary35 and salary 37. These tables show the spinal point in one column and the appropriate salary in anoter column. eg salary35 1 11187 3 11534 5 11899 7 12246 9 12629 salary 37 1 11827 3 12193 5 12579 7 12...

Problem with Static splitter window...
Hi, Iam trying to create splitter windows. I need my window to be split into two rows. And then, each of the two rows must be further split into 2 columns. I need it precisely like this because I need the splitter bar in both rows to be separate and independent. Can somebody help me find what's the problem in the following code? It does not create the lower splitter. Any help will be greatly appreciated. Thanks, Shirley BOOL CMainFrame::OnCreateClient(LPCREATESTRUCT lpcs, CCreateContext* pContext) { int nWidth, nHeight; nWidth = ::GetSystemMetrics(SM_CXMAXIMIZED); nHei...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

How to make Outlook my eMail choiceinstead of Thunderbird?
Set it as default in Control panel, Internet options, programs tab. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup by visiting http://www.microsoft.com/office/community/en-us/default.mspx or point your newsreader to msnews.microsoft.com. "johnmcinerney" <...

hyperlink with vlookup
=HYPERLINK[=vlookup(a2;CARATULAS!$A$4:$D$146;4;0)] I´ve got to create an hyperlink that (according to a value in the same row) takes me to the same value in another worksheet. Please help me!- Thanks One play which could do this .. Link to a sample file at: http://www.savefile.com/files/6398176 File: AutoHyperlink_Mariano922_wksht.xls Assume you have this set-up In Sheet1, cols A & B, data from row2 down ------------ Prod# UnitPrice 1111 10 1112 20 1113 30 1114 40 1115 50 etc In CARATULAS -------- Prod# are listed in col A & BulkPrice in col D (col D is the lookup col), wi...

?-Change data series range as data is entered?
I have a chart which shows a prediction curve and an actual curve. The prediction curve runs out until the end of a project. The actual curve is populated as data is entered. I will be generating a number of these charts from a datatable. I would like to plot the prediction curve for the duration of the project (easy). I would then like to plot the actual curve with only the existing data and not have the line drop to the x-axis at the end. Can I put a formula in the data series "x values" field? I hope that my description makes sense..... Thanks, Carl -- isofuncurves ---...

total of a range of times
This should be simple for some of you, but not for me. I am a runner, I have 4 ind. cells w/my split times. How do I get a total for the tallied times. I'm far from being experienced but am trying to learn..any and all help will be much appreciated. Thanks, Roger Roger To help you in your quest to "try to learn" I will direct you to Chip Pearson's site so's you can learn just about all you'll ever need for Time Calculations. http://www.cpearson.com/excel/datetime.htm#AddingTimes Gord Dibben Excel MVP On Sat, 13 Nov 2004 15:19:02 -0800, "Roger WG" &...

How do I automatically plot different ranges of data in different.
I need to chart parameters that are a function of three variables in the 2002 version of Excel. I have used the x and y axis to represent 2 variables on an 'xy chart', but require an automatic method to represent the value of the third variable. I have tried the 'bubble-plot', but cannot get sufficient resolution between the data. Is their any way that I may plot the third variable in different colours/shades, that automatically changes for different ranges of the the third variable? THis might help you: http://www.peltiertech.com/Excel/Charts/format.html#CondChart &q...

how do i make a chart follow the active cell
I have a worksheet that has frozen panes. I need the chart that is on the worksheet to be visible wherever the cursor is. is this possible? Hi, That would require VBA code. This example places the chart to the right of the active cell. Add this to the sheet code module which contains the chart object. Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Target.Parent.ChartObjects(1) .Top = Target.Cells(1).Top .Left = Target.Cells(1).Left + Target.Cells(1).Width End With End Sub Cheers Andy mepetey wrote: > I have a worksheet that has froze...

Getting the longest lengh in range of cells
I am trying to get the length of the longest cell in a range and use the array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. Is there a way in a formula to determine the longest cell length in a range, or through VBA. Thanks in advance Hi Try this formula array (please amend with last row number): {=MAX(LEN(A1:A7))} HTH Cordially Pascal "Geoff" <gh@bob.com> a �crit dans le message de news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl... > I am trying to get the length of the longest cell in a range and use the > array {=len(a1:a1)}. What happen...

how do I make an excel template
I am trying to figure out a way to make a spreadsheet to track fees that are paid each week in our soccer team. I have no idea where to start or what to do. I need something than can accomodate dates, names and an amount paid and that will also give me a balance per person each week. Are there any templates like this that anyone knows of, or is there a tutorial somewhere that I can attempt to make my own? Thanks I could help you with this, but need to know what the weekly amount would be to give you anything "nice". Meanwhile, you could take my extreme tutorial: http://www.off...

Select Range
Having trouble selecting several non-contiguous ranges. The spreadsheet I am working with is apprx. 300 rows by 110 columns. The sheet is subtotaled. I need to copy data and move to a summary worksheet. There is a subtotaled section entitled 'Risk'. Within this section, the only data required is Country, Customer, 3Q (orders, revenue, gross margin), and 4Q (orders, revenue, gross margin); columns B, E, AQ, AR, AS, BK, BL, and BM - respectively. The section title is in cell A162 and the data is in A163:CY200. I would prefer to select all of the data from the section at once, but ...

report date range
I have a report that will display data from an inventory transaction history form. There I would enter in the date and several other criteria. After doing so I filter the form to my selection and then print my report. I wanted the date range that is on the form to print on the report. I hvae done this in several other reports and never had a problem until now. I'm getting #NAME. The form is still open upon viewing the report. On the report I have a text box with the logic of: ="Report Date Between: " & [Forms]![frmInvTransactionHistory]![BegtDate] & " and &...

How does one make VS compile unsafe code
Hi, This is not the first time I've used unsafe code in C# projects. Usually, I simply check the box in the project properties telling it to allow unsafe code. I've done that for this project, yet VS is telling that, "Unsafe code may only appear in compiling with / unsafe." So, here's the command line being used to compile: C:\WINDOWS\Microsoft.NET\Framework\v3.5\Csc.exe /noconfig /unsafe- / nowarn:618,1701,1702 /nostdlib+ /platform:x86 /errorreport:prompt / warn:4 I'm betting that this is complaining because the switch that appears above is "...

Iterate named range & clear contents
Excel 03 Hi all I have a bunch of named ranges (Insert > Name > Define) which I wan to iterate through, & then delete their contents. I'm trying this : For Each nm in ThisWorkbook.Names nm.ClearContents Next nm can someone please show me the correct way to do this. thanks paul For Each nm In ThisWorkbook.Names On Error Resume Next Range(nm.Name).ClearContents On Error GoTo 0 Next nm -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Paul Wagstaff" <paulwagstaff@blueyonder.co.uk> wrote in mes...

HTREEITEM variables need to be defined as STATIC?
Does HTREEITEM variables need to be defined as STATIC, otherwise after calling the CTREECTRL member function, the variable's value will be changed? Thanks for your answer. >Does HTREEITEM variables need to be defined as STATIC, otherwise after >calling the CTREECTRL member function, the variable's value will be >changed? Generally the answer is no, but it'd be clearer if you showed some example code that you're worrying about. Dave ...

Make a calandar chart
I want to use excel to make a calendar chart. across the top i want the twelve months. Within each month i would like 4 or 5 columns to represent the weeks. the vertical axis will represent data to be plotted by pencil. the cells will remain blank in excel. In essence i am making a one page piece of graph paper. Can I put the vertical week columns within the month columns? how? Is there another alternative? Thanks, Helixal There may be something in here that will help you:- http://office.microsoft.com/en-gb/templates/CT103589901033.aspx Please hit yes if my comme...

Calculating greatest number in a range
This is a little tricky to explain, but hopefully will make sense. A series of data is produced that monitors concurrent users on a system. This is returned every five minutes of every day. This is on a work book which has a month for each tab. Data typically comes in the following, simple form: Wed Nov 26 10:50:01 GMT 2008 0 Wed Nov 26 10:55:00 GMT 2008 0 Wed Nov 26 11:00:00 GMT 2008 1 Wed Nov 26 11:05:00 GMT 2008 1 Wed Nov 26 11:10:00 GMT 2008 1 Wed Nov 26 11:15:00 GMT 2008 1 Wed Nov 26 11:20:00 GMT 2008 1 Essentially, I need a way of differentiating between indivi...

Vlookups and formats
I am using the following formula to pull in the worksheet name into cell B5 =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) The worksheet name is an account number. I then use vlookups to look at B5 as the lookup value to retreive various points of data linked to that specific account number. The problem I have is the formula shown adove brings the sheet name in as text and the vlookup sees it as text. I know this because when I edit the function the returned value for the lookup value is in quotes "1234". If I type i...