Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 15th 04, 02:23 AM
Markshnier
 
Posts: n/a
Default Indirect references in a linked formula


I have a formula in a workbook called my_spreadheet1.xls

=VLOOKUP(A1,'[my_spreadsheet2.xls]Main'!$1:$65536,27,FALSE)

i.e. In my_spreadsheet1.xls I have a formula that links to anoher
Workbook called my_spreadsheet2.xls.

My problem is that I want to have the name of the referenced Workbook
and the Worksheet within that Workbook be variable based on the values
in a couple of other cells.

For example if cell a2 = xxxxxxxxxx.xls, and cell a3 = yyyyyyy,
if would like to be able to create formula like

VLOOKUP(A4,'[xxxxxxxx.xls]yyyy'!$1:$65536,27,FALSE), where the xxxxx
and yyyyy are some function that will insert the contents of those
cells into the formula as text.

I have tried to use ADDRESS and INDIRECT where I have the xxxx and
yyyyy , but I can't figure ouit the syntax to make it work.

Any help would be appreciated.

Mark


--
Markshnier
------------------------------------------------------------------------
Markshnier's Profile: http://www.excelforum.com/member.php...o&userid=16465
View this thread: http://www.excelforum.com/showthread...hreadid=278194


  #2   Report Post  
Old November 15th 04, 02:49 AM
nh
 
Posts: n/a
Default

Assuming the location of the file is on cell D5 use the following formula
=INDIRECT("'"&D5&"Main'!$1:$65536") where D5 contains
'C:\Documents and Settings\xxx\My Documents\[my_spreadsheet2.xls]


"Markshnier" wrote in message
...

I have a formula in a workbook called my_spreadheet1.xls

=VLOOKUP(A1,'[my_spreadsheet2.xls]Main'!$1:$65536,27,FALSE)

i.e. In my_spreadsheet1.xls I have a formula that links to anoher
Workbook called my_spreadsheet2.xls.

My problem is that I want to have the name of the referenced Workbook
and the Worksheet within that Workbook be variable based on the values
in a couple of other cells.

For example if cell a2 = xxxxxxxxxx.xls, and cell a3 = yyyyyyy,
if would like to be able to create formula like

VLOOKUP(A4,'[xxxxxxxx.xls]yyyy'!$1:$65536,27,FALSE), where the xxxxx
and yyyyy are some function that will insert the contents of those
cells into the formula as text.

I have tried to use ADDRESS and INDIRECT where I have the xxxx and
yyyyy , but I can't figure ouit the syntax to make it work.

Any help would be appreciated.

Mark


--
Markshnier
------------------------------------------------------------------------
Markshnier's Profile:
http://www.excelforum.com/member.php...o&userid=16465
View this thread: http://www.excelforum.com/showthread...hreadid=278194





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 01:49 PM
Simple formula doesn't quite add up circeo Excel Discussion (Misc queries) 3 January 17th 05 09:04 PM
Using Indirect en direct cell references Bart Schouw Excel Discussion (Misc queries) 2 January 13th 05 01:05 PM
Approach Linked documents via INDIRECT Johannes Links and Linking in Excel 2 December 10th 04 02:36 PM
Conditional Formatting for dates spacerocket Excel Worksheet Functions 2 November 4th 04 10:13 AM


All times are GMT +1. The time now is 12:50 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017