again here's my another problem

[Guest]

Hi All,

i have two workbooks, both are open,

first book : "Book1_ERRangers2.xls"
with a sheettab named as : "Result_count"

2nd book : "CUADRO.xls"
with a sheettab named as : "Sheet1"

On the Result_tab of 1st book,
B1 = "place the filename of the 2nd book"
B2 = "place the sheet tab of 2nd Book"
B3 =B1&B2&"a1:iv65536" <<<<<not sure here!!!
C4 = HERE IS THE TWIST=COUNTIF(INDIRECT($B$3),B4) <<<<not sure here!!!!

C4 result is error...
Please advice/guide how to set up the proper concatenation+indirect+countif...

I am not allowed to change/copy/paste anything from the 2nd book...

Please advice...

regards,
driller

 

[Pete_UK]

The filename will need to have square brackets around it (and should
include the .xls extension). The sheetname will need to have an
exclamation after it. So in B3 you will need something like:

="["&B1&"]"&B2&"!A1:A1000"

(keep it to a smaller range to test it out). Put something in B4 that
you know exists in column A of the second sheet and see if you get the
correct count in C4.

Hope this helps.

Pete

 

[Guest]

Hi Pete_UK,

i've tested the countif(indirect(b3),b4)....with B3 formula you shared to
me...

it works (2003) when the filename and Tabname has no blank space, yet when
i try to test the formula on existing workbooks where the names have spaces
in between words, the result is #REF, although both books are open....

(e.g) "CUADRO.xls" can be read while "CUADR O.xls cannot...with
--------------->> countif(indirect(b3),b4) <<---???

Do we have some more text-tricks to update the concatenation formula on B3...?
I am not allowed to change the filenames of existing source books/sheets
since these are embedded on headers/footers of printed sheets.

The countif (book1) is an external auditing file, which relaxes when this
and one source book is open...

Kindly advice, with or without formula, so i can re-post this question, if
deem necessary...


thanks_and_regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

The filename will need to have square brackets around it (and should
include the .xls extension). The sheetname will need to have an
exclamation after it. So in B3 you will need something like:

="["&B1&"]"&B2&"!A1:A1000"

(keep it to a smaller range to test it out). Put something in B4 that
you know exists in column A of the second sheet and see if you get the
correct count in C4.

Hope this helps.

Pete

Hi All,

i have two workbooks, both are open,

first book : "Book1_ERRangers2.xls"
with a sheettab named as : "Result_count"

2nd book : "CUADRO.xls"
with a sheettab named as : "Sheet1"

On the Result_tab of 1st book,
B1 = "place the filename of the 2nd book"
B2 = "place the sheet tab of 2nd Book"
B3 =B1&B2&"a1:iv65536" <<<<<not sure here!!!
C4 = HERE IS THE TWIST=COUNTIF(INDIRECT($B$3),B4) <<<<not sure here!!!!

C4 result is error...
Please advice/guide how to set up the proper concatenation+indirect+countif...

I am not allowed to change/copy/paste anything from the 2nd book...

Please advice...

regards,
driller

--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

 

[Pete_UK]

If you reference another sheet which has spaces in the name, you have
to surround the name with apostrophes, so you will need to have an
apostrophe before the open square bracket of the filename and before
the exclamation mark, but inside the quotes, like so:

="'["&B1&"]"&B2&"'!A1:A1000"

Not very easy to see, which is why I've explained things above.

Hope this helps.

Pete

Hi Pete_UK,

i've tested the countif(indirect(b3),b4)....with B3 formula you shared to
me...

it works (2003) when the filename and Tabname has no blank space, yet when
i try to test the formula on existing workbooks where the names have spaces
in between words, the result is #REF, although both books are open....

(e.g) "CUADRO.xls" can be read while "CUADR O.xls cannot...with
--------------->> countif(indirect(b3),b4) <<---???

Do we have some more text-tricks to update the concatenation formula on B3...?
I am not allowed to change the filenames of existing source books/sheets
since these are embedded on headers/footers of printed sheets.

The countif (book1) is an external auditing file, which relaxes when this
and one source book is open...

Kindly advice, with or without formula, so i can re-post this question, if
deem necessary...

thanks_and_regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

The filename will need to have square brackets around it (and should
include the .xls extension). The sheetname will need to have an
exclamation after it. So in B3 you will need something like:

="["&B1&"]"&B2&"!A1:A1000"

(keep it to a smaller range to test it out). Put something in B4 that
you know exists in column A of the second sheet and see if you get the
correct count in C4.

Hope this helps.

- Show quoted text -

 

[Guest]

Pete_UK,

Perfect ! thats a relief...

Indeed, the 2 'apostrophes' makes the BIG difference or advantage specially
when calling the linked ext. filename references under a formula....

thanks_and_regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

If you reference another sheet which has spaces in the name, you have
to surround the name with apostrophes, so you will need to have an
apostrophe before the open square bracket of the filename and before
the exclamation mark, but inside the quotes, like so:

="'["&B1&"]"&B2&"'!A1:A1000"

Not very easy to see, which is why I've explained things above.

Hope this helps.

Pete

Hi Pete_UK,

i've tested the countif(indirect(b3),b4)....with B3 formula you shared to
me...

it works (2003) when the filename and Tabname has no blank space, yet when
i try to test the formula on existing workbooks where the names have spaces
in between words, the result is #REF, although both books are open....

(e.g) "CUADRO.xls" can be read while "CUADR O.xls cannot...with
--------------->> countif(indirect(b3),b4) <<---???

Do we have some more text-tricks to update the concatenation formula on B3...?
I am not allowed to change the filenames of existing source books/sheets
since these are embedded on headers/footers of printed sheets.

The countif (book1) is an external auditing file, which relaxes when this
and one source book is open...

Kindly advice, with or without formula, so i can re-post this question, if
deem necessary...

thanks_and_regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

The filename will need to have square brackets around it (and should
include the .xls extension). The sheetname will need to have an
exclamation after it. So in B3 you will need something like:

="["&B1&"]"&B2&"!A1:A1000"

(keep it to a smaller range to test it out). Put something in B4 that
you know exists in column A of the second sheet and see if you get the
correct count in C4.

Hope this helps.

On Jul 12, 11:20 pm, driller <[email protected]>
wrote:
Hi All,

i have two workbooks, both are open,

first book : "Book1_ERRangers2.xls"
with a sheettab named as : "Result_count"

2nd book : "CUADRO.xls"
with a sheettab named as : "Sheet1"

On the Result_tab of 1st book,
B1 = "place the filename of the 2nd book"
B2 = "place the sheet tab of 2nd Book"
B3 =B1&B2&"a1:iv65536" <<<<<not sure here!!!
C4 = HERE IS THE TWIST=COUNTIF(INDIRECT($B$3),B4) <<<<not sure here!!!!

C4 result is error...
Please advice/guide how to set up the proper concatenation+indirect+countif...

I am not allowed to change/copy/paste anything from the 2nd book...

Please advice...

--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

Glad it worked for you - thanks for feeding back.

Pete

Pete_UK,

Perfect ! thats a relief...

Indeed, the 2 'apostrophes' makes the BIG difference or advantage specially
when calling the linked ext. filename references under a formula....

thanks_and_regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull

If you reference another sheet which has spaces in the name, you have
to surround the name with apostrophes, so you will need to have an
apostrophe before the open square bracket of the filename and before
the exclamation mark, but inside the quotes, like so:

="'["&B1&"]"&B2&"'!A1:A1000"

Not very easy to see, which is why I've explained things above.

Hope this helps.

Pete

Hi Pete_UK,
i've tested the countif(indirect(b3),b4)....with B3 formula you shared to
me...
it works (2003) when the filename and Tabname has no blank space, yet when
i try to test the formula on existing workbooks where the names have spaces
in between words, the result is #REF, although both books are open....
(e.g) "CUADRO.xls" can be read while "CUADR O.xls cannot...with
--------------->> countif(indirect(b3),b4) <<---???
Do we have some more text-tricks to update the concatenation formula on B3...?
I am not allowed to change the filenames of existing source books/sheets
since these are embedded on headers/footers of printed sheets.
The countif (book1) is an external auditing file, which relaxes when this
and one source book is open...
Kindly advice, with or without formula, so i can re-post this question, if
deem necessary...
thanks_and_regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull
:
The filename will need to have square brackets around it (and should
include the .xls extension). The sheetname will need to have an
exclamation after it. So in B3 you will need something like:
="["&B1&"]"&B2&"!A1:A1000"
(keep it to a smaller range to test it out). Put something in B4 that
you know exists in column A of the second sheet and see if you get the
correct count in C4.
Hope this helps.
Pete
On Jul 12, 11:20 pm, driller <[email protected]>
wrote:
Hi All,
i have two workbooks, both are open,
first book : "Book1_ERRangers2.xls"
with a sheettab named as : "Result_count"
2nd book : "CUADRO.xls"
with a sheettab named as : "Sheet1"
On the Result_tab of 1st book,
B1 = "place the filename of the 2nd book"
B2 = "place the sheet tab of 2nd Book"
B3 =B1&B2&"a1:iv65536" <<<<<not sure here!!!
C4 = HERE IS THE TWIST=COUNTIF(INDIRECT($B$3),B4) <<<<not sure here!!!!
C4 result is error...
Please advice/guide how to set up the proper concatenation+indirect+countif...
I am not allowed to change/copy/paste anything from the 2nd book...
Please advice...
regards,
driller
--
*****
birds of the same feather flock together..birdwise, it''s more worth
knowing the edges rather than focusing in one line! Know the limits and
remember the extents - dive with Jonathan Seagull- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -