Index function - problem copying to other cells

[klafert]

I have a spreadsheet that I am using the following formula and it works:

=INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)

I have the formula in C2 and trying to copy the formula in the column
C3..c1000, but when I copy it or use fill down, then it doesn't change.
Example:

C2 =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)
C3 =INDEX('[customer price level4.xls]Sheet1'!$A$3:$C$3,3)
C4 =INDEX('[customer price level4.xls]Sheet1'!$A$4:$C$4,3)
C5 =INDEX('[customer price level4.xls]Sheet1'!$A$5:$C$5,3) and so on....

I will be doing this often and the range will change every week. which is
no problem but I need to be able to copy the formula and have it adjust to
the cells w/o me having to go to each row. I thought when you copy it
normally did that? Is it my formula? I am new to using Index. Also, the
source sheet has 16 worksheet. Tabs sheet1, sheet2, and so on until sheet16,
and from a1..c65536. So I need to adjust my range also, but I was doing a
test range. But I am not sure how to reference several worksheet in one
workbook?

Thanks for any help and hopefully today!! Need to have the 1st spreadsheet
done today.

 

[Niek Otten]

Use

INDEX('[customer price level4.xls]Sheet1'!$A2:$C2,3)

Note the omitted $ sings

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a spreadsheet that I am using the following formula and it works:
|
| =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)
|
| I have the formula in C2 and trying to copy the formula in the column
| C3..c1000, but when I copy it or use fill down, then it doesn't change.
| Example:
|
| C2 =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)
| C3 =INDEX('[customer price level4.xls]Sheet1'!$A$3:$C$3,3)
| C4 =INDEX('[customer price level4.xls]Sheet1'!$A$4:$C$4,3)
| C5 =INDEX('[customer price level4.xls]Sheet1'!$A$5:$C$5,3) and so on....
|
| I will be doing this often and the range will change every week. which is
| no problem but I need to be able to copy the formula and have it adjust to
| the cells w/o me having to go to each row. I thought when you copy it
| normally did that? Is it my formula? I am new to using Index. Also, the
| source sheet has 16 worksheet. Tabs sheet1, sheet2, and so on until sheet16,
| and from a1..c65536. So I need to adjust my range also, but I was doing a
| test range. But I am not sure how to reference several worksheet in one
| workbook?
|
| Thanks for any help and hopefully today!! Need to have the 1st spreadsheet
| done today.

 

[Stan Brown]

Sun, 1 Jul 2007 07:02:00 -0700 from klafert

I have a spreadsheet that I am using the following formula and it works:

=INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)

I have the formula in C2 and trying to copy the formula in the column
C3..c1000, but when I copy it or use fill down, then it doesn't change.
Example:

C2 =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)
C3 =INDEX('[customer price level4.xls]Sheet1'!$A$3:$C$3,3)
C4 =INDEX('[customer price level4.xls]Sheet1'!$A$4:$C$4,3)
C5 =INDEX('[customer price level4.xls]Sheet1'!$A$5:$C$5,3) and so on....

Several problems here.

First, all your references are absolute, which prevents them from
changing when you do a copy or similar operations. (The $ characters
make for absolute references. See
"The difference between relative and absolute references"
(with quotes) in Excel help. It may need more than one reading, but
getting a sound understanding of that topic is crucial to your
success in Excel.)

Second, if I recall correctly even relative references to another
workbook don't change when you do a copy or fill.

Third, are you referring to an external workbook, or to another sheet
in the same workbook? The [...] part of the reference is needed only
if you're referrinhg to another workbook.

I will be doing this often and the range will change every week.
which is no problem but I need to be able to copy the formula and
have it adjust to the cells w/o me having to go to each row. ... I
am new to using Index.

This is not related to the use of INDEX; any formula would have the
same issue.

What you need to do, I think, is use the INDIRECT function to
construct your references.

 

[klafert]

I am using 2 workbooks.

The first workbook , d:\globalxray\customer price level4.xls is created by
running a Crystal Report and then exported to Excel. It has the Customer ID,
Inventory item, and the acutal price being charged. This will only be
updated when the price of an inventory is changed, a new inventory item is
added, or any other changes made. When Crystal export to Excel it creates
the workbook customer price leve4.xls and I am assuming to the amount of data
it creates several sheets for one workbook. Is there a limit to the sheets
that can be created in workbook? This would only increase by new customers
and inventory items. I can elimate some by including only active records.

The second workbook will pull the Inventory item rate from the 1st workbook.
So the second workbook should only use one worksheet. This is then saved to
a .csv which I will put the formula in to pull the rate and then I will copy
and pasted special to get the vaules only. This will be imported into an
accounting program. This is all working at this time. We are just added the
inventory rate now. So, if there is a better formula to use I am all for it.
I can send you the spreadsheet if you need for a better understanding.


Thanks

Sun, 1 Jul 2007 07:02:00 -0700 from klafert

I have a spreadsheet that I am using the following formula and it works:

=INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)

I have the formula in C2 and trying to copy the formula in the column
C3..c1000, but when I copy it or use fill down, then it doesn't change.
Example:

C2 =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3)
C3 =INDEX('[customer price level4.xls]Sheet1'!$A$3:$C$3,3)
C4 =INDEX('[customer price level4.xls]Sheet1'!$A$4:$C$4,3)
C5 =INDEX('[customer price level4.xls]Sheet1'!$A$5:$C$5,3) and so on....

Several problems here.

First, all your references are absolute, which prevents them from
changing when you do a copy or similar operations. (The $ characters
make for absolute references. See
"The difference between relative and absolute references"
(with quotes) in Excel help. It may need more than one reading, but
getting a sound understanding of that topic is crucial to your
success in Excel.)

Second, if I recall correctly even relative references to another
workbook don't change when you do a copy or fill.

Third, are you referring to an external workbook, or to another sheet
in the same workbook? The [...] part of the reference is needed only
if you're referrinhg to another workbook.

I will be doing this often and the range will change every week.
which is no problem but I need to be able to copy the formula and
have it adjust to the cells w/o me having to go to each row. ... I
am new to using Index.

This is not related to the use of INDEX; any formula would have the
same issue.

What you need to do, I think, is use the INDIRECT function to
construct your references.

 

[klafert]

This worked fine. I put it in C2 and was able to use fill down and it
adjusted correctly and gave me the correct information. Thank you very much.
Is there a limit how many sheets I can have in a workbook?

 

[klafert]

Actually this work for sheet 1 only I need it to search all 16 worksheet in
the same workbook. But otherwise this worked.