| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index & multiple sheets in one workbook
I am using a formula and it works, but only for sheet 1. The source workbook
has 16 worksheets. I need it to search all 16 worksheet to find a match. I need to modify my formula so that will search the whole workbook. FORMULA: =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A6:$C6,3) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & multiple sheets in one workbook
Do you only have data on row 6 of each sheet? Can you not combine the
sheets into one composite sheet, instead of having 16 of them? Pete On Jul 1, 8:30 pm, klafert wrote: I am using a formula and it works, but only for sheet 1. The source workbook has 16 worksheets. I need it to search all 16 worksheet to find a match. I need to modify my formula so that will search the whole workbook. FORMULA: =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A6:$C6,3) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & multiple sheets in one workbook
Your terminology is very confusing.
You're stating that you're *searching* to find a *match*. The formula you posted is a simple, straight, *link* formula, that returns the contents of a *specific* cell in a specific WS in a specific WB! There's *no* searching or matching being performed by the formula that you posted. Care to re-phrase your question, or post a different formula as an example? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "klafert" wrote in message ... I am using a formula and it works, but only for sheet 1. The source workbook has 16 worksheets. I need it to search all 16 worksheet to find a match. I need to modify my formula so that will search the whole workbook. FORMULA: =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A6:$C6,3) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & multiple sheets in one workbook
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 Thanks "Ragdyer" wrote: Your terminology is very confusing. You're stating that you're *searching* to find a *match*. The formula you posted is a simple, straight, *link* formula, that returns the contents of a *specific* cell in a specific WS in a specific WB! There's *no* searching or matching being performed by the formula that you posted. Care to re-phrase your question, or post a different formula as an example? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "klafert" wrote in message ... I am using a formula and it works, but only for sheet 1. The source workbook has 16 worksheets. I need it to search all 16 worksheet to find a match. I need to modify my formula so that will search the whole workbook. FORMULA: =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A6:$C6,3) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & multiple sheets in one workbook
I am open to changing my formula if necesssary. This was my orignal formula:
=INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3) But when I used copy or fill down then it didnt adjust the cell like it normally does for me. 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.... "klafert" wrote: 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 Thanks "Ragdyer" wrote: Your terminology is very confusing. You're stating that you're *searching* to find a *match*. The formula you posted is a simple, straight, *link* formula, that returns the contents of a *specific* cell in a specific WS in a specific WB! There's *no* searching or matching being performed by the formula that you posted. Care to re-phrase your question, or post a different formula as an example? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "klafert" wrote in message ... I am using a formula and it works, but only for sheet 1. The source workbook has 16 worksheets. I need it to search all 16 worksheet to find a match. I need to modify my formula so that will search the whole workbook. FORMULA: =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A6:$C6,3) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & multiple sheets in one workbook
On the face of it ..
if you have this in C2: =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3) just amend it to: =INDEX('[customer price level4.xls]Sheet1'!$A2:$C2,3) then when you copy C2 down, it'll propagate in the desired manner indicated in your posting -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "klafert" wrote: I am open to changing my formula if necesssary. This was my orignal formula: =INDEX('[customer price level4.xls]Sheet1'!$A$2:$C$2,3) But when I used copy or fill down then it didnt adjust the cell like it normally does for me. 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.... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index & multiple sheets in one workbook
Check your other post, too.
klafert wrote: I am using a formula and it works, but only for sheet 1. The source workbook has 16 worksheets. I need it to search all 16 worksheet to find a match. I need to modify my formula so that will search the whole workbook. FORMULA: =INDEX('D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$A6:$C6,3) -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automated multiple text files into multiple sheets in one workbook | Excel Discussion (Misc queries) | |||
| Working with multiple sheets in a workbook | Excel Discussion (Misc queries) | |||
| Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
| Use the same name on multiple sheets in same workbook | Excel Discussion (Misc queries) | |||
| There is no way to view multiple sheets from one workbook | Excel Discussion (Misc queries) | |||
Linear Mode
