INDIRECT - only partial variation to formula

B

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
 
J

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
 
D

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

Domenic said:
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

BimboUK said:
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
 
B

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?



Jacob Skaria said:
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


BimboUK said:
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
 
B

BimboUK

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


Jacob Skaria said:
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


BimboUK said:
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?



Jacob Skaria said:
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
 
H

Harlan Grove

Dave Peterson said:
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)
 
D

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.



Harlan said:
Dave Peterson said:
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)
 
D

Dave Peterson

Ps. It was an excellent tip from Lori Miller.

Dave said:
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.

Harlan said:
Dave Peterson said:
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)
 
H

Harlan Grove

Dave Peterson said:
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!
 
L

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 ]
 
B

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.



Lori Miller said:
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 ]
 
D

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.

Lori Miller said:
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 ]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top