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
JMay said:
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?
klafert said:
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.