CellColorIndex function for a different workbook?

[Sayre]

I have used Chip Pearson's CellColorIndex function and it is very useful but only seems to work if the cells you reference in your formula (=cellcolorindex(A1,False) are in the same workbook as the function itself.

I have a situation where I need the function to reside in one workbook, but I need to write the formula that references it to be written in another workbook and reference cells in the other workbook.

Hope that makes sense? Is this possible?

The other workbook where I will write the formula will always be called "HDS_1" if that helps. Let me know if there is a link that specifically addresses it and I'll take it from there, thanks!

 

[HalfAce]

Hello sayre,
This will be backwards because you've only included the name of the workbook that
the formula will be written in.
What you need to change is 'HDS_1' to the name of the workbook that
contains the cell you're referencing.

(In other words, if you were to write this in the other workbook, referencing a cell in sheet 1
of the HDS_1 workbook, this is what it would look like.)

=cellcolorindex('[HDS_1.xls]Sheet1'!$A$1,FALSE)

Note:
If the background color of the cell will be getting changed, then you'll have to calculate
(press F9) the workbook/sheet containing the formula to get it to update.

 

[Sayre]

The other workbook is called HDS_PricebookAnalyzer_9OCT2006. However I tried to reverse this and it did not work. Not sure I am being clear, apologies for that.

I typed the following into the HDS_1 pricebook:

"=cellcolorindex([HDS_pricebook_analyzer_9OCT2006.xls]Sheet1!A5,FALSE)" and still got a result of "#NAME?". I think it is not working because the CellColor function does not reside in the HDS_1 workbook. It resides in the other workbook.

What I am looking for is a way to have the CellColorIndex function work in the HDS_1 workbook, even though the function resides in the other workbook. Is there a way to extend the "reach" of functions beyond the workbook they are housed in (as long as that workbook containing that function is open)?

 

[HalfAce]

Near as I can tell, the function code needs to be in the workbook containing the formula.
I know we can call a macro that doesn't reside in the workbook calling it from the Personal.xls workbook but I don't think we can call a function for a formula from there.
Hopefully someone can prove me wrong.

If the formula is going into the HDS_1 workbook (and that name remains static) then it should work just fine with the code in that book. The only drawback is it looks like the HDS_pricebook_analyzer_9OCT2006 workbook's name changes daily so your formula would need to as well.

 

[Richard Schollar]

Hey HalfAce

I'm pretty sure you can call functions in other workbooks (like the Personal Macro Workbook) like:

=Personal.xls!CellColorIndex(A1,False)

Richard

 

[HalfAce]

Well I'll be danged. Thanks Richard.
In that case (assuming the file with the colored cell is 'HDS_pricebook_analyzer_9OCT2006') I believe the formula would look something like:

=PERSONAL.XLS!CellColorIndex([HDS_pricebook_analyzer_9OCT2006.xls]Sheet1!$A$1,FALSE)

But I believe the same drawback would be true in that the formula will need to be amended to reflect the name of the workbook with the colored cell.

 

[Sayre]

Thanks for the efforts but I am still having trouble. I think this is getting me closer. When I write this formula in the HDS_1 workbook (referencing either workbook in the formula) it pops open a window asking me to choose a saved file.

I think I am still not being clear, let me try again.

The 'HDS_pricebook_analyzer_9OCT2006' workbook contains the function.

The 'HDS_1' workbook contains the highlighted cells AND is also where I need to write the formula. I need the formula to reference the function from the 'analyzer' workbook and function properly.

Does that change anything? Thanks a ton for attempting to help!

 

[HalfAce]

You've lost me on this.
If the formula and the cell it's referencing are both in the same workbook,
why would you want the function for the formula in different workbook?

 

[Sayre]

I know it seems odd, but the HDS_pricebookanalyzer.xls workbook has some long macros that essentially create the HDS_1 workbook from scratch (using DATA from a file made by someone else). It then compares the newly created file (HDS_1.xls) to last month's old version of the same file (which is renamed as HDS_2.xls). So no code can be in either HDS_1.xls or HDS_2.xls.

I could of course have the macro move the appropriate tab into the pricebook analyzer to do the CellColorIndex function and then move it back to HDS_1, but then the name of the pricebook analyzer file-name would have to remain constant or would have to be updated in VB every month and I am trying to avoid that if possible.

If I can't come up with either a formula or some VB to avoid the above then I suppose that's what I'll do. Let me know if you think there is any way to do this. Thanks again! And sorry for not making this clear before...

 

[HalfAce]

Well, unless you need to invoke the function at some other time than when the HDS_1 workbook
is created (or perhaps multiple times or something) I think I'd simply include the recording of the
cell color index as part of the code that creates the workbook. (But that's just me.)

If the formula and colored cell are both to be in HDS_1 and the function will reside in
HDS_pricebook_analyzer_9OCT2006, then you should be able to use this to call the function.

=HDS_pricebook_analyzer_9OCT2006.xls!CellColorIndex(Sheet1!$A$1,FALSE)

This will work the same as if the function were kept in your Personal.xls workbook, which I
think I'd still recommend, but you know your setup & needs better than I do.
(You see, I still suspect that with a name that ends with '9OCT2006' that this file name will
get changed - perhaps somewhat regularly? - and would then require your formula to be edited,
whereas if you use the personal.xls wb to hold & call the function from that would never be
necessary.)

Does that formula work for you?