In a workbook, have data sheet and report sheet.
In the report sheet at cell N1=If(data!B3:H3=1,1,0)
In cell P1=2
How can I substitute the value of P1 into the range so that the formula
will become something like N1=If(data!"B" &P1+1:"H" &P1+1=1,1,0). I
need to do this to because when I change the value in P1, N1 will give
me the answer instantly instead of changing the formula range every
time.

Can a worksheet (A.xls) defines reference to a worksheet(B.xls) for
data that is not open/used?

In VBA how do I define to use a closed workbook (A.xls) to extract data
and write to current open workbook (B.xls)?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Have a look in HELP index for INDIRECT

"Michael168" wrote in message
...
In a workbook, have data sheet and report sheet.
In the report sheet at cell N1=If(data!B3:H3=1,1,0)
In cell P1=2
How can I substitute the value of P1 into the range so that the formula
will become something like N1=If(data!"B" &P1+1:"H" &P1+1=1,1,0). I
need to do this to because when I change the value in P1, N1 will give
me the answer instantly instead of changing the formula range every
time.

Can a worksheet (A.xls) defines reference to a worksheet(B.xls) for
data that is not open/used?

In VBA how do I define to use a closed workbook (A.xls) to extract data
and write to current open workbook (B.xls)?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Michael,

Q1.
=IF(INDIRECT("data!B"&P1&":H"&P1)=1,1,0)

Q2.
You can reference the data in a closed book as long as you don't want the
dynamic form aka Q1 using INDIRECT. Each time you open the workbook, it will
ask if you want to update references. The dynamic form can be done, but it
needs a touch of magic as devised by Harlan Grove. See this Google post for
details

http://tinyurl.com/t92m

Q3.
In VBA, just open it, extract the data, and close it. No need to bother with
it being closed.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael168" wrote in message
...
In a workbook, have data sheet and report sheet.
In the report sheet at cell N1=If(data!B3:H3=1,1,0)
In cell P1=2
How can I substitute the value of P1 into the range so that the formula
will become something like N1=If(data!"B" &P1+1:"H" &P1+1=1,1,0). I
need to do this to because when I change the value in P1, N1 will give
me the answer instantly instead of changing the formula range every
time.

Can a worksheet (A.xls) defines reference to a worksheet(B.xls) for
data that is not open/used?

In VBA how do I define to use a closed workbook (A.xls) to extract data
and write to current open workbook (B.xls)?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/