I have an Add-in that requires the first sheet to be visible to the user in
use. It uses columns of data on additional worksheets in the same workbook. I
the .xls file (prior to .xla conversion) I was succesfully using a compound
INDIRECT function to reference the data on these additional sheets. Now that
I have made an .xla file of it, first off, no sheets are visible. That's OK
as I've coded the first worksheet to be visible to the user but my INDIRECT
function won't work, it just gives a #REF error in the cells I use it.

My question is this; can I change the INDIRECT formula in the .xls file such
that when I make it an Add-in the formula still works and references the
other worksheets that are in the workbook but just not visible because the
file is now an Add-in? I get the feeling it's just a referencing issue but I
can't get the syntax right.

Thanks in advance,

The formulas should still work just the same as a regular worksheet. How do
you know that you are getting a REF error? And what do you mean by you are
making the sheet visible for them? Are you talking about the add-in sheet?
Please describe the process/steps you are taking.

--
Regards,
Zack Barresse, aka firefytr


"Ian" wrote in message
...
I have an Add-in that requires the first sheet to be visible to the user in
use. It uses columns of data on additional worksheets in the same
workbook. I
the .xls file (prior to .xla conversion) I was succesfully using a
compound
INDIRECT function to reference the data on these additional sheets. Now
that
I have made an .xla file of it, first off, no sheets are visible. That's
OK
as I've coded the first worksheet to be visible to the user but my
INDIRECT
function won't work, it just gives a #REF error in the cells I use it.

My question is this; can I change the INDIRECT formula in the .xls file
such
that when I make it an Add-in the formula still works and references the
other worksheets that are in the workbook but just not visible because the
file is now an Add-in? I get the feeling it's just a referencing issue but
I
can't get the syntax right.

Thanks in advance,

Hi Zack,

The add-in has 10 sheets, the first contains the user interface plotting a
chart, and the remaining nine sheets provide the raw data which is selected
using a combo box (9 different options) on the first sheet. The data from
sheets 2 - 10 is brought onto the first sheet and displayed to the user via
the INDIRECT function which is written in the target cells for that data. The
chart is then plotted with that data.

This worked fine when the file was still .xls but when it is saved to a .xla
(addin) file all sheets are hidden and I understand this, hence my comment
about coding the first sheet visible so the user can interact with the addin.
At this stage now, the cells that have the INDIRECT formula inserted, on the
first sheet, give the #REF error instead of pulling the data from sheets 2 -
10 (dependant on the combox selection).

My current work-around is to make all the addin sheets visible when the
addin is selected from the Tools menu, and then using a loop make their
..Visible property = False. This works but takes time when the addin loads.

I thought that even though an addin sheet was not visible it would still be
"referencable".

Hope this makes sense and hope you can help,
Ian.

"zackb" wrote:

The formulas should still work just the same as a regular worksheet. How do
you know that you are getting a REF error? And what do you mean by you are
making the sheet visible for them? Are you talking about the add-in sheet?
Please describe the process/steps you are taking.

--
Regards,
Zack Barresse, aka firefytr


"Ian" wrote in message
...
I have an Add-in that requires the first sheet to be visible to the user in
use. It uses columns of data on additional worksheets in the same
workbook. I
the .xls file (prior to .xla conversion) I was succesfully using a
compound
INDIRECT function to reference the data on these additional sheets. Now
that
I have made an .xla file of it, first off, no sheets are visible. That's
OK
as I've coded the first worksheet to be visible to the user but my
INDIRECT
function won't work, it just gives a #REF error in the cells I use it.

My question is this; can I change the INDIRECT formula in the .xls file
such
that when I make it an Add-in the formula still works and references the
other worksheets that are in the workbook but just not visible because the
file is now an Add-in? I get the feeling it's just a referencing issue but
I
can't get the syntax right.

Thanks in advance,

Hi Ian,

Well, you can't do what you are asking. At least not directly. You can't
make an add-ins sheets visible. What you can do is copy the worksheets to
another workbook for the user to view. I would not recommend this though.
I would first create a new workbook, or just open an existing file using the
add-in.

I'm not sure why you are getting the #REF error, unless you are pointing to
a closed workbook with your INDIRECT function. These functions will work
fine with the workbook open, but not with it closed.

Fwiw, to get the value from a closed workbook there are many ways, but one
way is to put your formula in a cell like this ...

="='[Book11.xls]Sheet1'!A1"
Confirm with Enter
Select the cell, press Ctrl + C
Press Alt + E, S, V (PasteSpecial | Values)
Press Ctrl + H (Replace)
Type =, Tab, =
Click Replace

This is the easiest way (in my opinion) to quickly get values from closed
books. So from the sounds of it, you'd be better off keeping this as an XLS
file and creating anothe XLA file.

--
Regards,
Zack Barresse, aka firefytr


"Ian" wrote in message
...
Hi Zack,

The add-in has 10 sheets, the first contains the user interface plotting a
chart, and the remaining nine sheets provide the raw data which is
selected
using a combo box (9 different options) on the first sheet. The data from
sheets 2 - 10 is brought onto the first sheet and displayed to the user
via
the INDIRECT function which is written in the target cells for that data.
The
chart is then plotted with that data.

This worked fine when the file was still .xls but when it is saved to a
.xla
(addin) file all sheets are hidden and I understand this, hence my comment
about coding the first sheet visible so the user can interact with the
addin.
At this stage now, the cells that have the INDIRECT formula inserted, on
the
first sheet, give the #REF error instead of pulling the data from sheets
2 -
10 (dependant on the combox selection).

My current work-around is to make all the addin sheets visible when the
addin is selected from the Tools menu, and then using a loop make their
.Visible property = False. This works but takes time when the addin loads.

I thought that even though an addin sheet was not visible it would still
be
"referencable".

Hope this makes sense and hope you can help,
Ian.

"zackb" wrote:

The formulas should still work just the same as a regular worksheet. How
do
you know that you are getting a REF error? And what do you mean by you
are
making the sheet visible for them? Are you talking about the add-in
sheet?
Please describe the process/steps you are taking.

--
Regards,
Zack Barresse, aka firefytr


"Ian" wrote in message
...
I have an Add-in that requires the first sheet to be visible to the user
in
use. It uses columns of data on additional worksheets in the same
workbook. I
the .xls file (prior to .xla conversion) I was succesfully using a
compound
INDIRECT function to reference the data on these additional sheets. Now
that
I have made an .xla file of it, first off, no sheets are visible.
That's
OK
as I've coded the first worksheet to be visible to the user but my
INDIRECT
function won't work, it just gives a #REF error in the cells I use it.

My question is this; can I change the INDIRECT formula in the .xls file
such
that when I make it an Add-in the formula still works and references
the
other worksheets that are in the workbook but just not visible because
the
file is now an Add-in? I get the feeling it's just a referencing issue
but
I
can't get the syntax right.

Thanks in advance,

Zack,

Thanks for your suggestions, I'll give your ideas a go.

Ian.

"zackb" wrote:

Hi Ian,

Well, you can't do what you are asking. At least not directly. You can't
make an add-ins sheets visible. What you can do is copy the worksheets to
another workbook for the user to view. I would not recommend this though.
I would first create a new workbook, or just open an existing file using the
add-in.

I'm not sure why you are getting the #REF error, unless you are pointing to
a closed workbook with your INDIRECT function. These functions will work
fine with the workbook open, but not with it closed.

Fwiw, to get the value from a closed workbook there are many ways, but one
way is to put your formula in a cell like this ...

="='[Book11.xls]Sheet1'!A1"
Confirm with Enter
Select the cell, press Ctrl + C
Press Alt + E, S, V (PasteSpecial | Values)
Press Ctrl + H (Replace)
Type =, Tab, =
Click Replace

This is the easiest way (in my opinion) to quickly get values from closed
books. So from the sounds of it, you'd be better off keeping this as an XLS
file and creating anothe XLA file.

--
Regards,
Zack Barresse, aka firefytr


"Ian" wrote in message
...
Hi Zack,

The add-in has 10 sheets, the first contains the user interface plotting a
chart, and the remaining nine sheets provide the raw data which is
selected
using a combo box (9 different options) on the first sheet. The data from
sheets 2 - 10 is brought onto the first sheet and displayed to the user
via
the INDIRECT function which is written in the target cells for that data.
The
chart is then plotted with that data.

This worked fine when the file was still .xls but when it is saved to a
.xla
(addin) file all sheets are hidden and I understand this, hence my comment
about coding the first sheet visible so the user can interact with the
addin.
At this stage now, the cells that have the INDIRECT formula inserted, on
the
first sheet, give the #REF error instead of pulling the data from sheets
2 -
10 (dependant on the combox selection).

My current work-around is to make all the addin sheets visible when the
addin is selected from the Tools menu, and then using a loop make their
.Visible property = False. This works but takes time when the addin loads.

I thought that even though an addin sheet was not visible it would still
be
"referencable".

Hope this makes sense and hope you can help,
Ian.

"zackb" wrote:

The formulas should still work just the same as a regular worksheet. How
do
you know that you are getting a REF error? And what do you mean by you
are
making the sheet visible for them? Are you talking about the add-in
sheet?
Please describe the process/steps you are taking.

--
Regards,
Zack Barresse, aka firefytr


"Ian" wrote in message
...
I have an Add-in that requires the first sheet to be visible to the user
in
use. It uses columns of data on additional worksheets in the same
workbook. I
the .xls file (prior to .xla conversion) I was succesfully using a
compound
INDIRECT function to reference the data on these additional sheets. Now
that
I have made an .xla file of it, first off, no sheets are visible.
That's
OK
as I've coded the first worksheet to be visible to the user but my
INDIRECT
function won't work, it just gives a #REF error in the cells I use it.

My question is this; can I change the INDIRECT formula in the .xls file
such
that when I make it an Add-in the formula still works and references
the
other worksheets that are in the workbook but just not visible because
the
file is now an Add-in? I get the feeling it's just a referencing issue
but
I
can't get the syntax right.

Thanks in advance,