INDIRECT - only partial variation to formula

[BimboUK]

I wish to use one or two cells to vary part of a worksheet location in a
SUMIF formula.

example =SUMIF('c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$A$28:$A$847,$A5,'c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$C$28:$C$847)

Basically I am wanting to analyse the same range from a costing summary for
each person but also future proof it as A1 = the year and C2 being the name
of the person. The worksheet name is "09-10 A Person".

I have tried INDIRECT but don't understand the SYNTAX correctly yet.

At present I have the info in TEXT format.

If i can get this right I can really use the data I have!!

PS I have tried MoreFunc by ? Laurent using the INDIRECT.ext func and
couldn't get that to work either.

All help greatly appreciated

 

[Jacob Skaria]

To understand INDIRECT try this

1. Open new workbook.
2. Enter the text 'Hello' in A1
2. Enter Workbook name in A2 eg: Book1.xls
3. Enter Sheet name in A3 eg: Sheet1
4. In A4 enter the formula which should return the value of current sheet A1
(Hello)

=INDIRECT("[" & A2 & "]" & A3 & "!" & "A1")

If this post helps click Yes

 

[Domenic]

That should have been...

=SUMIF(INDIRECT("'c:\My Documents\["&$A$1&" "&C$2&".xls]Costing
summary'!A28:A847"),$A5,INDIRECT("'c:\My Documents\["&$A$1&"
"&C$2&".xls]Costing summary'!C28:C847"))

Hope this helps!

http://www.xl-central.com

Try...

=SUMIF(INDIRECT("'c:\My Documents\["&$A$1&" "&C$2&".xls]Costing
summary'!A28:A847"),$A5,INDIRECT("'c:\My Documents\["&$A$1&"
"&C$2&".xls]Costing summary'!A28:A847"))

Hope this helps!

http://www.xl-central.com

I wish to use one or two cells to vary part of a worksheet location in a
SUMIF formula.

example =SUMIF('c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$A$28:$A$847,$A5,'c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$C$28:$C$847)

Basically I am wanting to analyse the same range from a costing summary for
each person but also future proof it as A1 = the year and C2 being the name
of the person. The worksheet name is "09-10 A Person".

I have tried INDIRECT but don't understand the SYNTAX correctly yet.

At present I have the info in TEXT format.

If i can get this right I can really use the data I have!!

PS I have tried MoreFunc by ? Laurent using the INDIRECT.ext func and
couldn't get that to work either.

All help greatly appreciated

 

[BimboUK]

I opened a new workbook and input as you said but I am getting a #REF error.
When evaluating the formula it gets to INDIRECT("[Book2.xls]Sheets!A1") then
next step is the #REF error (I did it in Book2 so input Book2 instead of
Book1 in A2).

Any ideas whats going wrong?

To understand INDIRECT try this

1. Open new workbook.
2. Enter the text 'Hello' in A1
2. Enter Workbook name in A2 eg: Book1.xls
3. Enter Sheet name in A3 eg: Sheet1
4. In A4 enter the formula which should return the value of current sheet A1
(Hello)

=INDIRECT("[" & A2 & "]" & A3 & "!" & "A1")

If this post helps click Yes
---------------
Jacob Skaria

I wish to use one or two cells to vary part of a worksheet location in a
SUMIF formula.

example =SUMIF('c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$A$28:$A$847,$A5,'c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$C$28:$C$847)

Basically I am wanting to analyse the same range from a costing summary for
each person but also future proof it as A1 = the year and C2 being the name
of the person. The worksheet name is "09-10 A Person".

I have tried INDIRECT but don't understand the SYNTAX correctly yet.

At present I have the info in TEXT format.

If i can get this right I can really use the data I have!!

PS I have tried MoreFunc by ? Laurent using the INDIRECT.ext func and
couldn't get that to work either.

All help greatly appreciated

 

[BimboUK]

Sorry the sheet name was correct ie Sheet1 but I omitted it on my reply - any
ideas

INDIRECT("[Book2.xls]Sheets!A1") ::: Here you have not mentioned the sheet
name.

Please try the formula as such =INDIRECT("[" & A2 & "]" & A3 & "!" & "A1")
with
A1 = Hello
A2 = Book2
A3 = Sheet1


--
If this post helps click Yes
---------------
Jacob Skaria

I opened a new workbook and input as you said but I am getting a #REF error.
When evaluating the formula it gets to INDIRECT("[Book2.xls]Sheets!A1") then
next step is the #REF error (I did it in Book2 so input Book2 instead of
Book1 in A2).

Any ideas whats going wrong?

To understand INDIRECT try this

1. Open new workbook.
2. Enter the text 'Hello' in A1
2. Enter Workbook name in A2 eg: Book1.xls
3. Enter Sheet name in A3 eg: Sheet1
4. In A4 enter the formula which should return the value of current sheet A1
(Hello)

=INDIRECT("[" & A2 & "]" & A3 & "!" & "A1")

If this post helps click Yes
---------------
Jacob Skaria


:

I wish to use one or two cells to vary part of a worksheet location in a
SUMIF formula.

example =SUMIF('c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$A$28:$A$847,$A5,'c:\My Documents\[$A$1 C$2.xls]Costing
summary'!$C$28:$C$847)

Basically I am wanting to analyse the same range from a costing summary for
each person but also future proof it as A1 = the year and C2 being the name
of the person. The worksheet name is "09-10 A Person".

I have tried INDIRECT but don't understand the SYNTAX correctly yet.

At present I have the info in TEXT format.

If i can get this right I can really use the data I have!!

PS I have tried MoreFunc by ? Laurent using the INDIRECT.ext func and
couldn't get that to work either.

All help greatly appreciated

 

[Harlan Grove]

Both =sumif() and =indirect() are non-starters.  They will each
fail if the sending workbook is closed.
Yup.

You'll have to try to get the indirect.ext() function to work for
you and you'll have to use a different function than =sumif()--
maybe =sumproduct() or the array formula sum(if()).

There's another alternative that may be better if the values in the
OP's source workbook don't change (presumably the case since the OP is
accessing a closed workbook).

Starting with the OP's pseudoformula

=SUMIF(
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$A$28:$A$847,
$A5,
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$C$28:$C$847
)

Looks like this formula would be filled right and down from cell C5.
If so, enter the following formula in cell C5.

C5:
="=SUMPRODUCT(--('C:\My documents\[" & $A$1 & C$2 &
".xls]Costing summary'!$A$28:$A$847="& $A5 & "),
'C:\My Documents\[" & & $A$1 & C$2 &
".xls]Costing summary'!$C$28:$C$847)"

This is a string-valued formula that will look like a formula. Copy it
and paste into the cells that should have similar formulas, e.g.,
C5:J24. Select the entire range of these formulas, copy, and paste
special as values. This will convert the string-valued formulas into
constant text strings - not formulas. With the range still selected,
run the Edit > Replace command, replacing = with =. This may appear to
be a do-nothing operation, but it effectively enters each of these
cells as formulas. If A1 contained foo, C2 contained bar and A5
contained "xyz" (INCLUDING the double quotes), the resulting formula
in cell C5 would be

C5:
=SUMPRODUCT(--('C:\My documents\[foobar.xls]Costing summary'!$A$28:$A
$847="xyz"),'C:\My Documents\[foobar.xls]Costing summary'!$C$28:$C$847)

 

[Dave Peterson]

Just a note about changing those strings to formulas.

If the sending workbook/worksheet doesn't exist, then after the edit|replace,
you'll see a prompt for every non-existent file. The only way out of this is to
dismiss each of those dialogs or to kill excel (through the task manager).

Instead of using that edit|replace, you (or the OP or me!) can use data|text to
columns. This seems to plop all the formulas back into the range (single column
at a time, though) and instead of the prompt that needs to be dismissed, you'll
see a #ref error.

I learned this within the last week from another poster and it worked in my
simple tests in xl2003.

It may come in handy for you, too, Harlan.

Both =sumif() and =indirect() are non-starters. They will each
fail if the sending workbook is closed.
Yup.

You'll have to try to get the indirect.ext() function to work for
you and you'll have to use a different function than =sumif()--
maybe =sumproduct() or the array formula sum(if()).

There's another alternative that may be better if the values in the
OP's source workbook don't change (presumably the case since the OP is
accessing a closed workbook).

Starting with the OP's pseudoformula

=SUMIF(
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$A$28:$A$847,
$A5,
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$C$28:$C$847
)

Looks like this formula would be filled right and down from cell C5.
If so, enter the following formula in cell C5.

C5:
="=SUMPRODUCT(--('C:\My documents\[" & $A$1 & C$2 &
".xls]Costing summary'!$A$28:$A$847="& $A5 & "),
'C:\My Documents\[" & & $A$1 & C$2 &
".xls]Costing summary'!$C$28:$C$847)"

This is a string-valued formula that will look like a formula. Copy it
and paste into the cells that should have similar formulas, e.g.,
C5:J24. Select the entire range of these formulas, copy, and paste
special as values. This will convert the string-valued formulas into
constant text strings - not formulas. With the range still selected,
run the Edit > Replace command, replacing = with =. This may appear to
be a do-nothing operation, but it effectively enters each of these
cells as formulas. If A1 contained foo, C2 contained bar and A5
contained "xyz" (INCLUDING the double quotes), the resulting formula
in cell C5 would be

C5:
=SUMPRODUCT(--('C:\My documents\[foobar.xls]Costing summary'!$A$28:$A
$847="xyz"),'C:\My Documents\[foobar.xls]Costing summary'!$C$28:$C$847)

 

[Dave Peterson]

Ps. It was an excellent tip from Lori Miller.

Just a note about changing those strings to formulas.

If the sending workbook/worksheet doesn't exist, then after the edit|replace,
you'll see a prompt for every non-existent file. The only way out of this is to
dismiss each of those dialogs or to kill excel (through the task manager).

Instead of using that edit|replace, you (or the OP or me!) can use data|text to
columns. This seems to plop all the formulas back into the range (single column
at a time, though) and instead of the prompt that needs to be dismissed, you'll
see a #ref error.

I learned this within the last week from another poster and it worked in my
simple tests in xl2003.

It may come in handy for you, too, Harlan.

Both =sumif() and =indirect() are non-starters. They will each
fail if the sending workbook is closed.
Yup.

You'll have to try to get the indirect.ext() function to work for
you and you'll have to use a different function than =sumif()--
maybe =sumproduct() or the array formula sum(if()).

There's another alternative that may be better if the values in the
OP's source workbook don't change (presumably the case since the OP is
accessing a closed workbook).

Starting with the OP's pseudoformula

=SUMIF(
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$A$28:$A$847,
$A5,
'c:\My Documents\[$A$1 C$2.xls]Costing summary'!$C$28:$C$847
)

Looks like this formula would be filled right and down from cell C5.
If so, enter the following formula in cell C5.

C5:
="=SUMPRODUCT(--('C:\My documents\[" & $A$1 & C$2 &
".xls]Costing summary'!$A$28:$A$847="& $A5 & "),
'C:\My Documents\[" & & $A$1 & C$2 &
".xls]Costing summary'!$C$28:$C$847)"

This is a string-valued formula that will look like a formula. Copy it
and paste into the cells that should have similar formulas, e.g.,
C5:J24. Select the entire range of these formulas, copy, and paste
special as values. This will convert the string-valued formulas into
constant text strings - not formulas. With the range still selected,
run the Edit > Replace command, replacing = with =. This may appear to
be a do-nothing operation, but it effectively enters each of these
cells as formulas. If A1 contained foo, C2 contained bar and A5
contained "xyz" (INCLUDING the double quotes), the resulting formula
in cell C5 would be

C5:
=SUMPRODUCT(--('C:\My documents\[foobar.xls]Costing summary'!$A$28:$A
$847="xyz"),'C:\My Documents\[foobar.xls]Costing summary'!$C$28:$C$847)

 

[Harlan Grove]

If the sending workbook/worksheet doesn't exist, then after the edit|replace,
you'll see a prompt for every non-existent file.  The only way out of this is to
dismiss each of those dialogs or to kill excel (through the task manager).

....

Easy though time consuming to dismiss all the dialogs - just press and
don't release the [Esc] key.

OTOH, if the file does exist and there are multiple columns, multiple
Data > Text to Columns commands will take longer than a single Edit >
Replace.

Trade offs!

 

[Lori Miller]

OTOH, if the file does exist and there are multiple columns, multiple

Data > Text to Columns commands will take longer than a single Edit >
Replace.

Not necessarily - depends how many cells and columns... see test below.
Text to Columns is substantially faster but you may need to update links
after. The difference can be large when linking to files across a network.

There is another option too which works on multiple columns - use the
office clipboard: Ctrl+C twice, click the paste icon and then paste special
as TEXT. (With this, you don't need to pastespecial values beforehand).

A test, linking to a column in a closed workbook on a local drive, gave the
following results in seconds. The column consisted of 10,000 random nos.

16.1 cells.Replace "=","="
1.1 columns(1).TextToColumns ,xlDelimitedt=timer:?timer-t
0.2 activeworkbook.UpdateLink activeworkbook.LinkSources()(1)

With office clipboard active then pasting as text (from notepad):
2.0 columns(1).copy
1.4 activesheet.paste

[ All commands executed from the immediate window using
t=timer:Command:?timer-t ]

 

[BimboUK]

Thanks to everybody for the very thoughful and comprehensive response.

Now I know I am doing it right it seems that I have a basic problem that may
be part of the background set-up.

Even when I try the most basic test supplied by Jacob Skania it comes back
with a #REF error.

If I can't do this basic thing then no wonder I am having probs doing the
more complex stuff.

When evaluating it gets to INDIRECT("[Book2.xls]Sheets1!A1")

the next step of the evaluation comes up with the #REF error.

Any ideas whats going wrong - I have tried Hello and 'Hello' in A1

I think this is were my real problem is!!!
I greatly appreciate your comments about how best to deal with my original
problem and when i get the basics working will try the others!!!

You thoughts greatly appreciated.

OTOH, if the file does exist and there are multiple columns, multiple
Data > Text to Columns commands will take longer than a single Edit >
Replace.

Not necessarily - depends how many cells and columns... see test below.
Text to Columns is substantially faster but you may need to update links
after. The difference can be large when linking to files across a network.

There is another option too which works on multiple columns - use the
office clipboard: Ctrl+C twice, click the paste icon and then paste special
as TEXT. (With this, you don't need to pastespecial values beforehand).

A test, linking to a column in a closed workbook on a local drive, gave the
following results in seconds. The column consisted of 10,000 random nos.

16.1 cells.Replace "=","="
1.1 columns(1).TextToColumns ,xlDelimitedt=timer:?timer-t
0.2 activeworkbook.UpdateLink activeworkbook.LinkSources()(1)

With office clipboard active then pasting as text (from notepad):
2.0 columns(1).copy
1.4 activesheet.paste

[ All commands executed from the immediate window using
t=timer:Command:?timer-t ]

 

[Dave Peterson]

Is the "sending" workbook really named book2.xls?

Is it open in the same instance of excel?

Is the name of the worksheet inside book2.xls really named sheets1 (with that
extra S)?

All 3 of those have to be true for =indirect() to work ok.

Thanks to everybody for the very thoughful and comprehensive response.

Now I know I am doing it right it seems that I have a basic problem that may
be part of the background set-up.

Even when I try the most basic test supplied by Jacob Skania it comes back
with a #REF error.

If I can't do this basic thing then no wonder I am having probs doing the
more complex stuff.

When evaluating it gets to INDIRECT("[Book2.xls]Sheets1!A1")

the next step of the evaluation comes up with the #REF error.

Any ideas whats going wrong - I have tried Hello and 'Hello' in A1

I think this is were my real problem is!!!
I greatly appreciate your comments about how best to deal with my original
problem and when i get the basics working will try the others!!!

You thoughts greatly appreciated.

OTOH, if the file does exist and there are multiple columns, multiple
Data > Text to Columns commands will take longer than a single Edit >
Replace.

Not necessarily - depends how many cells and columns... see test below.
Text to Columns is substantially faster but you may need to update links
after. The difference can be large when linking to files across a network.

There is another option too which works on multiple columns - use the
office clipboard: Ctrl+C twice, click the paste icon and then paste special
as TEXT. (With this, you don't need to pastespecial values beforehand).

A test, linking to a column in a closed workbook on a local drive, gave the
following results in seconds. The column consisted of 10,000 random nos.

16.1 cells.Replace "=","="
1.1 columns(1).TextToColumns ,xlDelimitedt=timer:?timer-t
0.2 activeworkbook.UpdateLink activeworkbook.LinkSources()(1)

With office clipboard active then pasting as text (from notepad):
2.0 columns(1).copy
1.4 activesheet.paste

[ All commands executed from the immediate window using
t=timer:Command:?timer-t ]