I have 3 columns of info to move to next worksheet. On new sheet I set one
column to bring me the numbers in order--large1, etc thru 4. I would like a
formula or function to bring me the info in the next column, same row. And
also the info in the third column. For example-in cell a3, the formula
is=LARGE(wkly!w34:w37,1).It gave me the value 323 which is wkly!w35. In cell
b3 I want the info in wkly!x35, in cell c3 I want the value from wkly!y35, so
that all 3 columns match based on the
large1, large2, large3, and large4. If I bring all the info to the next page
with copy and then sort it messes up all my calculation formulas in columns
d,e, f, etc. I am working in groups of 4. Thank you

In B3:
=INDEX(wkly!X$34:X$37,MATCH(LARGE(wkly!$W$34:$W$37 ,ROWS($1:1)),wkly!$W$34:$W$37,0))
Copy B3 to C3, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jeel" wrote in message
...
I have 3 columns of info to move to next worksheet. On new sheet I set one
column to bring me the numbers in order--large1, etc thru 4. I would like
a
formula or function to bring me the info in the next column, same row. And
also the info in the third column. For example-in cell a3, the formula
is=LARGE(wkly!w34:w37,1).It gave me the value 323 which is wkly!w35. In
cell
b3 I want the info in wkly!x35, in cell c3 I want the value from wkly!y35,
so
that all 3 columns match based on the
large1, large2, large3, and large4. If I bring all the info to the next
page
with copy and then sort it messes up all my calculation formulas in
columns
d,e, f, etc. I am working in groups of 4. Thank you

Thank you for your help. I tested it a little and it looks like it is going
to work.
Perhaps you could help me with another problem I posted.
I want to remove the zeroes from my worksheet. I click the microsoft button,
excel options, advanced and uncheck the box for show zeroes. I save when I
close but when I open the workbook again the zeroes are there again. Is there
a way to set the default so the zeroes won't show and I would have to check
the box to show them? Thank you.

"Max" wrote:

In B3:
=INDEX(wkly!X$34:X$37,MATCH(LARGE(wkly!$W$34:$W$37 ,ROWS($1:1)),wkly!$W$34:$W$37,0))
Copy B3 to C3, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jeel" wrote in message
...
I have 3 columns of info to move to next worksheet. On new sheet I set one
column to bring me the numbers in order--large1, etc thru 4. I would like
a
formula or function to bring me the info in the next column, same row. And
also the info in the third column. For example-in cell a3, the formula
is=LARGE(wkly!w34:w37,1).It gave me the value 323 which is wkly!w35. In
cell
b3 I want the info in wkly!x35, in cell c3 I want the value from wkly!y35,
so
that all 3 columns match based on the
large1, large2, large3, and large4. If I bring all the info to the next
page
with copy and then sort it messes up all my calculation formulas in
columns
d,e, f, etc. I am working in groups of 4. Thank you

I tested your formula and it looks like it will work. If you have time, I
would like some written explaination of the formula so next time I can work
it out for myself.
Thank you
"Max" wrote:

In B3:
=INDEX(wkly!X$34:X$37,MATCH(LARGE(wkly!$W$34:$W$37 ,ROWS($1:1)),wkly!$W$34:$W$37,0))
Copy B3 to C3, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jeel" wrote in message
...
I have 3 columns of info to move to next worksheet. On new sheet I set one
column to bring me the numbers in order--large1, etc thru 4. I would like
a
formula or function to bring me the info in the next column, same row. And
also the info in the third column. For example-in cell a3, the formula
is=LARGE(wkly!w34:w37,1).It gave me the value 323 which is wkly!w35. In
cell
b3 I want the info in wkly!x35, in cell c3 I want the value from wkly!y35,
so
that all 3 columns match based on the
large1, large2, large3, and large4. If I bring all the info to the next
page
with copy and then sort it messes up all my calculation formulas in
columns
d,e, f, etc. I am working in groups of 4. Thank you

Think the switch off zeros setting should stick. Just tested it here. But
what probably happens is that when we open another window, and then we click
on the same sheet in this new window, the zeros are there again (but in this
new window). If we were to click on the same sheet in the 1st window, you'll
see that the switch off zeros setting is still there. Hence if we operate the
file using 2 windows, we'll need to apply the setting to both windows for the
same sheet. If we close one of the 2 windows, then re-create it, we'll have
to apply the setting again.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jeel" wrote:
Thank you for your help. I tested it a little and it looks like it is going
to work.
Perhaps you could help me with another problem I posted.
I want to remove the zeroes from my worksheet. I click the microsoft button,
excel options, advanced and uncheck the box for show zeroes. I save when I
close but when I open the workbook again the zeroes are there again. Is there
a way to set the default so the zeroes won't show and I would have to check
the box to show them? Thank you.

Suggest that you browse the index/match examples at Debra's page:
http://www.contextures.com/xlFunctions03.html

That should give you a good handle on it
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jeel" wrote:
I tested your formula and it looks like it will work. If you have time, I
would like some written explanation of the formula so next time I can work
it out for myself.
Thank you

Thank you. I took a look at Debra's page. I am trying to figure it out.
Your formula worked really well. However I ran accross one problem.
The first step I created was the Large function. Your formula was indexed
against that. The problem is that there are 2 numbers in the first column
that are identical.
I searched and can't seem to come up with a solution. Maybe I should have
started differently using the Match or Index function? Thanks.

"Max" wrote:

Suggest that you browse the index/match examples at Debra's page:
http://www.contextures.com/xlFunctions03.html

That should give you a good handle on it
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jeel" wrote:
I tested your formula and it looks like it will work. If you have time, I
would like some written explanation of the formula so next time I can work
it out for myself.
Thank you

.. there are 2 numbers in the first column that are identical.

You need a solution with tiebreaks incorporated in this kind of case.
(Suggest you put in as a fresh new posting)

You could take a look at my recent response to another posting:
http://tinyurl.com/2qzo94

The link to the sample mentioned in the response is still working:
http://www.freefilehosting.net/download/3a519
AutoList Debtors for amt more than zero n sort desc by Age.xls

The criteria col would look something like this:
=IF(AND(ISNUMBER(C2),C20),E2-ROW()/10^10,"")
which includes tiebreaking

and the extract formula would look like this:
=IF(ISERROR(LARGE($G:$G,ROWS($1:1))),"",INDEX(A:A, MATCH(LARGE($G:$G,ROWS($1:1)),$G:$G,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I started a new post asking for help to make this formula work. I also posted
an example. I hope this makes sense. I couldn't seem to make the formula for
duplicates work. Thank you for your help. jeel

"Max" wrote:

.. there are 2 numbers in the first column that are identical.

You need a solution with tiebreaks incorporated in this kind of case.
(Suggest you put in as a fresh new posting)

You could take a look at my recent response to another posting:
http://tinyurl.com/2qzo94

The link to the sample mentioned in the response is still working:
http://www.freefilehosting.net/download/3a519
AutoList Debtors for amt more than zero n sort desc by Age.xls

The criteria col would look something like this:
=IF(AND(ISNUMBER(C2),C20),E2-ROW()/10^10,"")
which includes tiebreaking

and the extract formula would look like this:
=IF(ISERROR(LARGE($G:$G,ROWS($1:1))),"",INDEX(A:A, MATCH(LARGE($G:$G,ROWS($1:1)),$G:$G,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

OP's new posting in:
http://tinyurl.com/3x9wx7
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---