Ok, it's a bit clearer now. Presumably you have so much data that you
cannot join any of the sheets together? Is it possible for you to add
an extra column C in each of the sheets 1 -16 ? If so, then you can
add this formula:
=A2&B2
and copy it down each sheet (double-click the fill handle - the small
black square in the bottom right corner of the cursor).
Then in your other workbook you can have a formula in D2 along the
lines of:
=IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price
level4.xls]Sheet1'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\
[customer price level4.xls]Sheet1'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&
$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C:$D,
2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price
level4.xls]Sheet2'!$C:$D,2,0)) & IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X
RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)),"",VLOOKUP($A2&
$B2,'D:\GLOBAL X RAY\[customer price level4.xls]Sheet3'!$C:$D,2,0)) &
IF(ISNA(VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\[customer price
level4.xls]Sheet4'!$C:$D,2,0)),"",VLOOKUP($A2&$B2,'D:\GLOBAL X RAY\
[customer price level4.xls]Sheet4'!$C:$D,2,0))
Note this is all one formula - you will need to be wary of spurious
line breaks. This will only look at the first 4 sheets, as the formula
will be too long for all 16, so copy the formula into E2:G2 and for E2
change the sheet references from Sheet1 to Sheet5, Sheet2 to Sheet6,
Sheet3 to Sheet7 and Sheet4 to Sheet8 (you can use Edit|Replace to do
this). Do the same with the formula in F2, but it should refer to
Sheets 9 to 12, and G2 should refer to Sheets 13 to 16.
In C2 you can then add a formula to join these together:
=SUM(D2:G2)
which is the value that you want - if it is zero then there is no
match in any of the 16 sheets.
Copy the formulae in C2:G2 down the sheet as required - you can hide
columns D to G if you don't want to see them.
Hope this helps.
Pete
I guess I am getting frustrated and need this today. I am trying to make
myself clear. I am going to start from scratch.
Source spreadsheet - has all the data
customer Id Item ID Price
joe blow 2mxr 50.00
carol windy 2mxr 60.00
2nd spreadsheet - this spreadsheet will pull the data from the source
spreadsheet
Customer ID Item ID Billing Rate
Joe blow 2mxr ????
carol windy 2mxr ???
The Billing rate is pulled from the source sheet from the price column. So
my second sheet will have the 1st two column filled in with the customer id
Item id, but not the Bill rate (Price from source sheet). The source sheet
is created in Crystal reports and exported to Excel. So, there are no
formulas in this sheet. But due to the amount of data, when Crystal export
the data it creates 16 worksheets in one workbook. The second sheets needs a
formula that will check Column A (customer id) + B (Item Id) when they match
then pull the price into column c of the second spreadsheet for the billing
rate. In other words, each customer mite have a different price for the same
item. The spreadsheet that came from Crystal has the correct price for each
customers. So, the formula needs to search all 16 tabs and they are labeled
sheet1, sheet2, etc up to sheet 16 and return the price from the source sheet
into the second spreadsheet. I done this before and usually use the VLOOKup
but since I have to meet two criteria and due to the many tabs, I need a new
formula. I know that I have to adjust my formula.
I more than appreciate any help I can get and would even pay a fee to get
this done. I have a lot going on not to mention Brother in hospital.
Pete_UK said:
Look, people are trying to help you here, but it is not exactly clear
what you are trying to do, so we have pointed out that your formula is
not searching for anything - basically, you are returning the 3rd cell
in the range A2:C2 in Sheet1 of your other workbook. So, this is
basically a straightforward link to cell C2, and you could make your
formula:
='D:\GLOBAL X RAY\[customer price level4.xls]Sheet1'!$C2
If you wanted to return data from another sheet, all you need to do is
change the sheet reference, i.e.:
='D:\GLOBAL X RAY\[customer price level4.xls]Sheet2'!$C2
assuming you still want to get data from C2 of that sheet. If you
don't want to manually change the sheet name, then this could be
"calculated" or derived from a cell on your sheet by means of the
INDIRECT function, but this only works if both workbooks are open at
the same time.
You originally asked how you could get the data from one of up to 16
sheets in the other workbook - the way you would normally do this with
a VLOOKUP on 3 sheets, for example, is along the lines of:
=IF(ISNA(VLOOKUP(on_sheet1),
IF(ISNA(VLOOKUP(on_sheet2),
IF(ISNA(VLOOKUP(on_sheet3),"not present",
VLOOKUP(on_sheet3)),
VLOOKUP(on_sheet2)),
VLOOKUP(on_sheet1))
but with a limit of 7 nested IFs in Excel you would have to use a
different approach. Hence my questions to you - can you combine the 16
sheets into one composite sheet, and what ranges of data do you have
on each sheet?
Please keep all responses in the same thread - I, for one, can't keep
up with all your multi-postings.
Pete
I have 2 posting here, but everybody gets bogged down on the wrong part of
the question. My original ques ion is very similar to what I have seen
posted here, yet it seems to be a problem. Right now the problem is no 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.- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
