Substitute Range Question?

[Michael168]

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)?

 

[Don Guillett]

Have a look in HELP index for INDIRECT

 

[Bob Phillips]

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)