Formula needs to include several worksheet tabs in one workbook

[Guest]

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Guest]

The Index(SourceRange,Row#,Cloumn#) only returns 1 value from 1 Source.
I'm confused (which isn't hard) -- What are you trying to do with the
multiple references suggested?

 

[Guest]

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 actual 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 eliminate 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 values 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.

Example:
(source spreadsheet) d:\globalxray\customer price level4.xls

A1 B1 C2
Customer ID Item ID Price

1000 14 X 17 14.50
1000 1ECNC 10.00
1000 1IHOFST 20.00
1000 1IHONOT 30.00
1000 1IHONST 0.00


2nd spreadsheet (pulls the data from the first): d:\globalxray\customer
price level test.xls


CustomerID ITemID Billing rate (data pulled from 1st
spreadsheet
1000 14 X 17 14.50 in column C)
1000 1ECNC 10.00
1000 1IHOFST 20.00
1000 1IHONOT 30.00
1000 1IHONST 0.00

The Index(SourceRange,Row#,Cloumn#) only returns 1 value from 1 Source.
I'm confused (which isn't hard) -- What are you trying to do with the
multiple references suggested?

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Guest]

typo in sample - should be:

Example:

(source spreadsheet) d:\globalxray\customer price level4.xls

A B C
Customer ID Item ID Price

1000 14 X 17 14.50
1000 1ECNC 10.00
1000 1IHOFST 20.00
1000 1IHONOT 30.00
1000 1IHONST 0.00

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 actual 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 eliminate 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 values 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.

Example:
(source spreadsheet) d:\globalxray\customer price level4.xls

A1 B1 C2
Customer ID Item ID Price

1000 14 X 17 14.50
1000 1ECNC 10.00
1000 1IHOFST 20.00
1000 1IHONOT 30.00
1000 1IHONST 0.00


2nd spreadsheet (pulls the data from the first): d:\globalxray\customer
price level test.xls


CustomerID ITemID Billing rate (data pulled from 1st
spreadsheet
1000 14 X 17 14.50 in column C)
1000 1ECNC 10.00
1000 1IHOFST 20.00
1000 1IHONOT 30.00
1000 1IHONST 0.00

The Index(SourceRange,Row#,Cloumn#) only returns 1 value from 1 Source.
I'm confused (which isn't hard) -- What are you trying to do with the
multiple references suggested?

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Roger Govier]

Hi

I would put
D:\GLOBAL X RAY\[customer price level4.xls]Sheet
into a cell, e.g. E1
then
=INDEX(INDIRECT("'"&E1&ROW(A1)&"'!$A2:$C2"),3)
Note the quotation marks

=INDEX(INDIRECT(" ' " &E1&ROW(A1)& " ' !$A2:$C2"),3)

This will evaluate to sheet 1 in the first instance, but as you copy
down the page, ROW(A1) will become B1, C1 etc and give rise to Sheet2,
Sheet3.

 

[Guest]

If I am understanding right ((**try this on a Backup copy **)
In you level4.xls file Insert a new column between Item ID and Price.
In this new column (Now Col C) in the first data row enter = A1&B1 and copy
down to bottom.
(This concatenates your Customer ID and Item ID - making it unique).

In your test.xls file with Col A and B already filled with your Cust ID and
Item ID
in the first data row Col C enter (example row 2):

=VLOOKUP(A2&B2,[d:\globalxray\customer price level4.xls]Sheet1!C$2$2000,2)

Copy down as far as needed

Hope this Helps

Jim

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 actual 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 eliminate 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 values 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.

Example:
(source spreadsheet) d:\globalxray\customer price level4.xls

A1 B1 C2
Customer ID Item ID Price

1000 14 X 17 14.50
1000 1ECNC 10.00
1000 1IHOFST 20.00
1000 1IHONOT 30.00
1000 1IHONST 0.00


2nd spreadsheet (pulls the data from the first): d:\globalxray\customer
price level test.xls


CustomerID ITemID Billing rate (data pulled from 1st
spreadsheet
1000 14 X 17 14.50 in column C)
1000 1ECNC 10.00
1000 1IHOFST 20.00
1000 1IHONOT 30.00
1000 1IHONST 0.00

The Index(SourceRange,Row#,Cloumn#) only returns 1 value from 1 Source.
I'm confused (which isn't hard) -- What are you trying to do with the
multiple references suggested?

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Guest]

The first row is the heading. The data starts in row 2. Columns are A,B,C.
I copied D:\GLOBAL X RAY\[customer price level4.xls]Sheet to E1, then in D1 I
put =INDEX(INDIRECT(" ' " &E1&ROW(A1)& " ' !$A2:$C2"),3). I then copied them
down but got an #ref! error. I don't supposed I can send you the 2
spreadsheet. I might be easier if you can see. I think you are on the right
foot as I am sure you know. I would loved to get this resolved. My e-mail
address is (e-mail address removed) if you want to send me your e-mail address.

Mary

Hi

I would put
D:\GLOBAL X RAY\[customer price level4.xls]Sheet
into a cell, e.g. E1
then
=INDEX(INDIRECT("'"&E1&ROW(A1)&"'!$A2:$C2"),3)
Note the quotation marks

=INDEX(INDIRECT(" ' " &E1&ROW(A1)& " ' !$A2:$C2"),3)

This will evaluate to sheet 1 in the first instance, but as you copy
down the page, ROW(A1) will become B1, C1 etc and give rise to Sheet2,
Sheet3.
--
Regards

Roger Govier

The problem is not the formula I use except for I need to specific a
range
that includes using more than one worksheet tab. Here is the formula
it
works for one worksheet because I only specifics one worksheet. I
just need
to know how to encode the range of the other worksheet tabs. first
worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Guest]

This worked for row 2 and sorta for the rest but for some reason put the
price which is pulling from the source sheet and is in 3rd column, but the
rest pulled the inventory id which is the 2nd column. Also, I had to copy
the file name all the way down is this correct. You have resolved the issue
of multiple worksheet. Now to check the formula. Like I said it worked for
the 1st row of data which is a2-c2 the results showed up in C2 like it
should. The rest appears to be working but pulling the wrong data. I went
to change the column number but it appears to be correct.

Hi

I would put
D:\GLOBAL X RAY\[customer price level.XL]Sheet
into a cell, e.g. E
then
=INDEX(INDIRECT("'"&E&ROW(A1)&"'!$A2:$C2"),3)
Note the quotation marks

=INDEX(INDIRECT(" ' " &E1&ROW(A1)& " ' !$A2:$C2"),3)

This will evaluate to sheet 1 in the first instance, but as you copy
down the page, ROW(A1) will become B1, C1 etc and give rise to Sheet2,
Sheet3.
--
Regards

Roger Govier

The problem is not the formula I use except for I need to specific a
range
that includes using more than one worksheet tab. Here is the formula
it
works for one worksheet because I only specifics one worksheet. I
just need
to know how to encode the range of the other worksheet tabs. first
worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Dave Peterson]

You could use 16 formulas and change the sheet name in each formula.

But if you want to create a string that points at that other workbook, then the
function you'd want to use is =indirect().

But =indirect() won't work if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Guest]

Well of course I don't want 16 formula. Also, my formula got changed and I
think I have to use Index and Match. Because I have to have that exact data
from the cell I am requesting the data from. But I am going to check into
this and I didn't know that I had to have both spreadsheet open that is not a
problem. Hope you are around later. I am off to the hospital to see brother
- CANCER!!

Mary

You could use 16 formulas and change the sheet name in each formula.

But if you want to create a string that points at that other workbook, then the
function you'd want to use is =indirect().

But =indirect() won't work if the sending workbook is closed.

Laurent Longer has an adding (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Guest]

Can I get your e-mail - I have a request and don't know if I would be
breaking any of the communites rules. You can also e-mail me at
(e-mail address removed) or call me at 504-888-7380. I wil wait a few minutes
before I have to run off.

You could use 16 formulas and change the sheet name in each formula.

But if you want to create a string that points at that other workbook, then the
function you'd want to use is =indirect().

But =indirect() won't work if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Dave Peterson]

I hope your brother is doing better.

But if you don't want 16 separate folder and you don't want to open the sending
workbook, you should try that addin from Laurent Longre (I mistyped his last
name earlier. Sorry.)

This isn't a problem with the index/match. It's a problem with excel's built in
=indirect() function. It won't recalculate correctly if the sending workbook is
closed--as you've seen.

Well of course I don't want 16 formula. Also, my formula got changed and I
think I have to use Index and Match. Because I have to have that exact data
from the cell I am requesting the data from. But I am going to check into
this and I didn't know that I had to have both spreadsheet open that is not a
problem. Hope you are around later. I am off to the hospital to see brother
- CANCER!!

Mary

You could use 16 formulas and change the sheet name in each formula.

But if you want to create a string that points at that other workbook, then the
function you'd want to use is =indirect().

But =indirect() won't work if the sending workbook is closed.

Laurent Longer has an adding (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.

 

[Dave Peterson]

It's best to keep the conversation in the newsgroup. There'll be lots more
people who can help.

As an aside, you may not want to share your email address or phone number in a
public forum like this.

Can I get your e-mail - I have a request and don't know if I would be
breaking any of the communites rules. You can also e-mail me at
<<snipped>> I wil wait a few minutes
before I have to run off.

You could use 16 formulas and change the sheet name in each formula.

But if you want to create a string that points at that other workbook, then the
function you'd want to use is =indirect().

But =indirect() won't work if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

The problem is not the formula I use except for I need to specific a range
that includes using more than one worksheet tab. Here is the formula it
works for one worksheet because I only specifics one worksheet. I just need
to know how to encode the range of the other worksheet tabs. first worksheet
is sheet1 and goes through sheet16 in the same work book.

=INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A2:$C2,3)
How do I edit this formula to include the other worksheet.