+ Reply to Thread
Results 1 to 13 of 13

How do I connect fields from two spreadsheets

  1. #1
    The Good Deeds Team
    Guest

    How do I connect fields from two spreadsheets

    I have two spreadsheets.

    the first has a list of account numbers, for example

    10
    20
    30

    The second has a list of acount numbers and an amount, for example

    10 100
    20 150
    30 175

    How can I in the fisrt spreadsheet, use a formula to look in the second
    spreadsheet for the corresponding account number, example 20, and pull the
    correct value, example 150 withoutout coding each row in the first
    spreadsheet to look exactly at the specific row in the second spreadsheet

    For example, I can do this and it works

    =(second spreadsheet!$F$11)

    however I just want it to know where in the second spreadsheet column F the
    value 20 exists and pull 175 from colum G

    I don't want to have to tell the firts spreadsheet it is on row 11, I want
    it to look through coulmn F and find the value 20, which matches the value in
    the first spreadsheet, and then give me the amount from that row in the
    second spreadsheet, say column g

    this application is really 6000 accounts in the first spreadsheet, that need
    to match 6000 accounts in the second spreadsheet, and I don't want to code it
    row by row, simplely match the accopunt numbers from the two spreadsheets and
    give me a value on the corresponding row.




  2. #2
    RagDyer
    Guest

    Re: How do I connect fields from two spreadsheets

    You can use Vlookup.

    Account numbers on Sheet1, A2 to A6000.

    Account numbers on Sheet2, A2 to A6000
    Amounts on Sheet2, B2 to B6000.

    Enter this in B2 of Sheet1:

    =VLOOKUP(A2,Sheet2!$A$2:$B$6000,2,0)

    You can drag down to copy, or the easy way is to double click on the "fill
    handle" in the lower right corner of B2, which will copy the formula in B2
    down Column B, as far as there is data in Column A.
    --

    HTH,

    RD
    ==============================================
    Please keep all correspondence within the Group, so all may benefit!
    ==============================================


    "The Good Deeds Team" <[email protected]> wrote in
    message news:[email protected]...
    I have two spreadsheets.

    the first has a list of account numbers, for example

    10
    20
    30

    The second has a list of acount numbers and an amount, for example

    10 100
    20 150
    30 175

    How can I in the fisrt spreadsheet, use a formula to look in the second
    spreadsheet for the corresponding account number, example 20, and pull the
    correct value, example 150 withoutout coding each row in the first
    spreadsheet to look exactly at the specific row in the second spreadsheet

    For example, I can do this and it works

    =(second spreadsheet!$F$11)

    however I just want it to know where in the second spreadsheet column F the
    value 20 exists and pull 175 from colum G

    I don't want to have to tell the firts spreadsheet it is on row 11, I want
    it to look through coulmn F and find the value 20, which matches the value
    in
    the first spreadsheet, and then give me the amount from that row in the
    second spreadsheet, say column g

    this application is really 6000 accounts in the first spreadsheet, that need
    to match 6000 accounts in the second spreadsheet, and I don't want to code
    it
    row by row, simplely match the accopunt numbers from the two spreadsheets
    and
    give me a value on the corresponding row.




  3. #3
    Otto Moehrbach
    Guest

    Re: How do I connect fields from two spreadsheets

    You can do this with VLookup formulas but there is a problem with that, that
    you should be aware of. With 6000 rows of data and just 2 columns in
    Sheet2, you will need 6000 VLookup formulas. The problem is that the file
    will grow big in a hurry. If you have more than just those 2 columns, which
    I suspect you do, you will end up with a big file. If that is not a problem
    for you then I would say to go with the formulas. The alternative is to go
    with a VBA solution. Post back if you need more. HTH Otto
    "The Good Deeds Team" <[email protected]> wrote in
    message news:[email protected]...
    >I have two spreadsheets.
    >
    > the first has a list of account numbers, for example
    >
    > 10
    > 20
    > 30
    >
    > The second has a list of acount numbers and an amount, for example
    >
    > 10 100
    > 20 150
    > 30 175
    >
    > How can I in the fisrt spreadsheet, use a formula to look in the second
    > spreadsheet for the corresponding account number, example 20, and pull the
    > correct value, example 150 withoutout coding each row in the first
    > spreadsheet to look exactly at the specific row in the second spreadsheet
    >
    > For example, I can do this and it works
    >
    > =(second spreadsheet!$F$11)
    >
    > however I just want it to know where in the second spreadsheet column F
    > the
    > value 20 exists and pull 175 from colum G
    >
    > I don't want to have to tell the firts spreadsheet it is on row 11, I want
    > it to look through coulmn F and find the value 20, which matches the value
    > in
    > the first spreadsheet, and then give me the amount from that row in the
    > second spreadsheet, say column g
    >
    > this application is really 6000 accounts in the first spreadsheet, that
    > need
    > to match 6000 accounts in the second spreadsheet, and I don't want to code
    > it
    > row by row, simplely match the accopunt numbers from the two spreadsheets
    > and
    > give me a value on the corresponding row.
    >
    >
    >




  4. #4
    RagDyer
    Guest

    Re: How do I connect fields from two spreadsheets

    You bring up a good point Otto.

    The Index and Match combination is supposed to be much more efficient then
    Vlookup, although I have no idea how it compares to VBA.

    This should work faster then the Vlookup formula I first suggested:

    =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    --


    Regards,

    RD
    --------------------------------------------------------------------
    Please keep all correspondence within the Group, so all may benefit!
    -------------------------------------------------------------------


    "Otto Moehrbach" <[email protected]> wrote in message
    news:%[email protected]...
    You can do this with VLookup formulas but there is a problem with that, that
    you should be aware of. With 6000 rows of data and just 2 columns in
    Sheet2, you will need 6000 VLookup formulas. The problem is that the file
    will grow big in a hurry. If you have more than just those 2 columns, which
    I suspect you do, you will end up with a big file. If that is not a problem
    for you then I would say to go with the formulas. The alternative is to go
    with a VBA solution. Post back if you need more. HTH Otto
    "The Good Deeds Team" <[email protected]> wrote in
    message news:[email protected]...
    >I have two spreadsheets.
    >
    > the first has a list of account numbers, for example
    >
    > 10
    > 20
    > 30
    >
    > The second has a list of acount numbers and an amount, for example
    >
    > 10 100
    > 20 150
    > 30 175
    >
    > How can I in the fisrt spreadsheet, use a formula to look in the second
    > spreadsheet for the corresponding account number, example 20, and pull the
    > correct value, example 150 withoutout coding each row in the first
    > spreadsheet to look exactly at the specific row in the second spreadsheet
    >
    > For example, I can do this and it works
    >
    > =(second spreadsheet!$F$11)
    >
    > however I just want it to know where in the second spreadsheet column F
    > the
    > value 20 exists and pull 175 from colum G
    >
    > I don't want to have to tell the firts spreadsheet it is on row 11, I want
    > it to look through coulmn F and find the value 20, which matches the value
    > in
    > the first spreadsheet, and then give me the amount from that row in the
    > second spreadsheet, say column g
    >
    > this application is really 6000 accounts in the first spreadsheet, that
    > need
    > to match 6000 accounts in the second spreadsheet, and I don't want to code
    > it
    > row by row, simplely match the accopunt numbers from the two spreadsheets
    > and
    > give me a value on the corresponding row.
    >
    >
    >




  5. #5
    Otto Moehrbach
    Guest

    Re: How do I connect fields from two spreadsheets

    RD
    The VBA way may well be slower (looping through 6000 cells) but it
    doesn't increase the size of the file like all those formulas do. I usually
    refrain from using formulas when there are many (like 6000) formulas
    involved. Otto
    "RagDyer" <[email protected]> wrote in message
    news:[email protected]...
    > You bring up a good point Otto.
    >
    > The Index and Match combination is supposed to be much more efficient then
    > Vlookup, although I have no idea how it compares to VBA.
    >
    > This should work faster then the Vlookup formula I first suggested:
    >
    > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > --
    >
    >
    > Regards,
    >
    > RD
    > --------------------------------------------------------------------
    > Please keep all correspondence within the Group, so all may benefit!
    > -------------------------------------------------------------------
    >
    >
    > "Otto Moehrbach" <[email protected]> wrote in message
    > news:%[email protected]...
    > You can do this with VLookup formulas but there is a problem with that,
    > that
    > you should be aware of. With 6000 rows of data and just 2 columns in
    > Sheet2, you will need 6000 VLookup formulas. The problem is that the file
    > will grow big in a hurry. If you have more than just those 2 columns,
    > which
    > I suspect you do, you will end up with a big file. If that is not a
    > problem
    > for you then I would say to go with the formulas. The alternative is to
    > go
    > with a VBA solution. Post back if you need more. HTH Otto
    > "The Good Deeds Team" <[email protected]> wrote
    > in
    > message news:[email protected]...
    >>I have two spreadsheets.
    >>
    >> the first has a list of account numbers, for example
    >>
    >> 10
    >> 20
    >> 30
    >>
    >> The second has a list of acount numbers and an amount, for example
    >>
    >> 10 100
    >> 20 150
    >> 30 175
    >>
    >> How can I in the fisrt spreadsheet, use a formula to look in the second
    >> spreadsheet for the corresponding account number, example 20, and pull
    >> the
    >> correct value, example 150 withoutout coding each row in the first
    >> spreadsheet to look exactly at the specific row in the second spreadsheet
    >>
    >> For example, I can do this and it works
    >>
    >> =(second spreadsheet!$F$11)
    >>
    >> however I just want it to know where in the second spreadsheet column F
    >> the
    >> value 20 exists and pull 175 from colum G
    >>
    >> I don't want to have to tell the firts spreadsheet it is on row 11, I
    >> want
    >> it to look through coulmn F and find the value 20, which matches the
    >> value
    >> in
    >> the first spreadsheet, and then give me the amount from that row in the
    >> second spreadsheet, say column g
    >>
    >> this application is really 6000 accounts in the first spreadsheet, that
    >> need
    >> to match 6000 accounts in the second spreadsheet, and I don't want to
    >> code
    >> it
    >> row by row, simplely match the accopunt numbers from the two spreadsheets
    >> and
    >> give me a value on the corresponding row.
    >>
    >>
    >>

    >
    >




  6. #6
    RagDyer
    Guest

    Re: How do I connect fields from two spreadsheets

    FWIW,
    A couple of years ago, after reading about some speed tests Aladin did, I
    switched a big WB database from a double (error checking) Vlookup formula to
    the Index and Match combination,
    It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes !

    Regards,

    RD
    --------------------------------------------------------------------
    Please keep all correspondence within the Group, so all may benefit!
    -------------------------------------------------------------------

    "Otto Moehrbach" <[email protected]> wrote in message
    news:#[email protected]...
    > RD
    > The VBA way may well be slower (looping through 6000 cells) but it
    > doesn't increase the size of the file like all those formulas do. I

    usually
    > refrain from using formulas when there are many (like 6000) formulas
    > involved. Otto
    > "RagDyer" <[email protected]> wrote in message
    > news:[email protected]...
    > > You bring up a good point Otto.
    > >
    > > The Index and Match combination is supposed to be much more efficient

    then
    > > Vlookup, although I have no idea how it compares to VBA.
    > >
    > > This should work faster then the Vlookup formula I first suggested:
    > >
    > > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > > --
    > >
    > >
    > > Regards,
    > >
    > > RD
    > > --------------------------------------------------------------------
    > > Please keep all correspondence within the Group, so all may benefit!
    > > -------------------------------------------------------------------
    > >
    > >
    > > "Otto Moehrbach" <[email protected]> wrote in message
    > > news:%[email protected]...
    > > You can do this with VLookup formulas but there is a problem with that,
    > > that
    > > you should be aware of. With 6000 rows of data and just 2 columns in
    > > Sheet2, you will need 6000 VLookup formulas. The problem is that the

    file
    > > will grow big in a hurry. If you have more than just those 2 columns,
    > > which
    > > I suspect you do, you will end up with a big file. If that is not a
    > > problem
    > > for you then I would say to go with the formulas. The alternative is to
    > > go
    > > with a VBA solution. Post back if you need more. HTH Otto
    > > "The Good Deeds Team" <[email protected]> wrote
    > > in
    > > message news:[email protected]...
    > >>I have two spreadsheets.
    > >>
    > >> the first has a list of account numbers, for example
    > >>
    > >> 10
    > >> 20
    > >> 30
    > >>
    > >> The second has a list of acount numbers and an amount, for example
    > >>
    > >> 10 100
    > >> 20 150
    > >> 30 175
    > >>
    > >> How can I in the fisrt spreadsheet, use a formula to look in the second
    > >> spreadsheet for the corresponding account number, example 20, and pull
    > >> the
    > >> correct value, example 150 withoutout coding each row in the first
    > >> spreadsheet to look exactly at the specific row in the second

    spreadsheet
    > >>
    > >> For example, I can do this and it works
    > >>
    > >> =(second spreadsheet!$F$11)
    > >>
    > >> however I just want it to know where in the second spreadsheet column F
    > >> the
    > >> value 20 exists and pull 175 from colum G
    > >>
    > >> I don't want to have to tell the firts spreadsheet it is on row 11, I
    > >> want
    > >> it to look through coulmn F and find the value 20, which matches the
    > >> value
    > >> in
    > >> the first spreadsheet, and then give me the amount from that row in the
    > >> second spreadsheet, say column g
    > >>
    > >> this application is really 6000 accounts in the first spreadsheet, that
    > >> need
    > >> to match 6000 accounts in the second spreadsheet, and I don't want to
    > >> code
    > >> it
    > >> row by row, simplely match the accopunt numbers from the two

    spreadsheets
    > >> and
    > >> give me a value on the corresponding row.
    > >>
    > >>
    > >>

    > >
    > >

    >
    >




  7. #7
    Otto Moehrbach
    Guest

    Re: How do I connect fields from two spreadsheets

    That is significant. I will remember that. Thanks. Otto
    "RagDyer" <[email protected]> wrote in message
    news:%[email protected]...
    > FWIW,
    > A couple of years ago, after reading about some speed tests Aladin did, I
    > switched a big WB database from a double (error checking) Vlookup formula
    > to
    > the Index and Match combination,
    > It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes !
    >
    > Regards,
    >
    > RD
    > --------------------------------------------------------------------
    > Please keep all correspondence within the Group, so all may benefit!
    > -------------------------------------------------------------------
    >
    > "Otto Moehrbach" <[email protected]> wrote in message
    > news:#[email protected]...
    >> RD
    >> The VBA way may well be slower (looping through 6000 cells) but it
    >> doesn't increase the size of the file like all those formulas do. I

    > usually
    >> refrain from using formulas when there are many (like 6000) formulas
    >> involved. Otto
    >> "RagDyer" <[email protected]> wrote in message
    >> news:[email protected]...
    >> > You bring up a good point Otto.
    >> >
    >> > The Index and Match combination is supposed to be much more efficient

    > then
    >> > Vlookup, although I have no idea how it compares to VBA.
    >> >
    >> > This should work faster then the Vlookup formula I first suggested:
    >> >
    >> > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    >> > --
    >> >
    >> >
    >> > Regards,
    >> >
    >> > RD
    >> > --------------------------------------------------------------------
    >> > Please keep all correspondence within the Group, so all may benefit!
    >> > -------------------------------------------------------------------
    >> >
    >> >
    >> > "Otto Moehrbach" <[email protected]> wrote in message
    >> > news:%[email protected]...
    >> > You can do this with VLookup formulas but there is a problem with that,
    >> > that
    >> > you should be aware of. With 6000 rows of data and just 2 columns in
    >> > Sheet2, you will need 6000 VLookup formulas. The problem is that the

    > file
    >> > will grow big in a hurry. If you have more than just those 2 columns,
    >> > which
    >> > I suspect you do, you will end up with a big file. If that is not a
    >> > problem
    >> > for you then I would say to go with the formulas. The alternative is
    >> > to
    >> > go
    >> > with a VBA solution. Post back if you need more. HTH Otto
    >> > "The Good Deeds Team" <[email protected]>
    >> > wrote
    >> > in
    >> > message news:[email protected]...
    >> >>I have two spreadsheets.
    >> >>
    >> >> the first has a list of account numbers, for example
    >> >>
    >> >> 10
    >> >> 20
    >> >> 30
    >> >>
    >> >> The second has a list of acount numbers and an amount, for example
    >> >>
    >> >> 10 100
    >> >> 20 150
    >> >> 30 175
    >> >>
    >> >> How can I in the fisrt spreadsheet, use a formula to look in the
    >> >> second
    >> >> spreadsheet for the corresponding account number, example 20, and pull
    >> >> the
    >> >> correct value, example 150 withoutout coding each row in the first
    >> >> spreadsheet to look exactly at the specific row in the second

    > spreadsheet
    >> >>
    >> >> For example, I can do this and it works
    >> >>
    >> >> =(second spreadsheet!$F$11)
    >> >>
    >> >> however I just want it to know where in the second spreadsheet column
    >> >> F
    >> >> the
    >> >> value 20 exists and pull 175 from colum G
    >> >>
    >> >> I don't want to have to tell the firts spreadsheet it is on row 11, I
    >> >> want
    >> >> it to look through coulmn F and find the value 20, which matches the
    >> >> value
    >> >> in
    >> >> the first spreadsheet, and then give me the amount from that row in
    >> >> the
    >> >> second spreadsheet, say column g
    >> >>
    >> >> this application is really 6000 accounts in the first spreadsheet,
    >> >> that
    >> >> need
    >> >> to match 6000 accounts in the second spreadsheet, and I don't want to
    >> >> code
    >> >> it
    >> >> row by row, simplely match the accopunt numbers from the two

    > spreadsheets
    >> >> and
    >> >> give me a value on the corresponding row.
    >> >>
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >




  8. #8
    The Good Deeds Team
    Guest

    Re: How do I connect fields from two spreadsheets

    This is really cool and worked great - now I would like to extend the model.

    The application is a budget model

    Budgets are stored in 140 separate worksheets, I thought about, having 140
    tabs, but someone in the office said that would not work for them

    Your suggestion worked for me as long as the other spreahseet was open (as
    in the following example)

    =-ROUND(INDEX('[10010 Budget Ofc of the President.xls]Sheet 1'!$F$1:$F$100,
    MATCH($A503,'[10010 Budget Ofc of the President.xls]Sheet 1'!$M$1:'[10010
    Budget Ofc of the President.xls]Sheet 1'!$M$100,0))/3,2)

    However if I close the other spreadsheet, and the first spreadsheet as well,
    then open the first spreadsheet and say update from other spreadsheets, the
    fields gets the value REF# and the formula changes to this:

    =-ROUND(INDEX('S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget
    Ofc of the President.xls]Sheet 1'!$F$1:$F$100,
    MATCH($A503,'S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget
    Ofc of the President.xls]Sheet 1'!$M$1:'S:\ACCOUNTING\Budgets\Budgets\FY2005
    Budget\OOP\[10010 Budget Ofc of the President.xls]Sheet 1'!$M$100,0))/3,2)

    Even if this worked, I wanted to reduce the string

    S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\

    to

    ..\oop

    that does not seem to work either

    Do you have any suggestions?



    "RagDyer" wrote:

    > FWIW,
    > A couple of years ago, after reading about some speed tests Aladin did, I
    > switched a big WB database from a double (error checking) Vlookup formula to
    > the Index and Match combination,
    > It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes !
    >
    > Regards,
    >
    > RD
    > --------------------------------------------------------------------
    > Please keep all correspondence within the Group, so all may benefit!
    > -------------------------------------------------------------------
    >
    > "Otto Moehrbach" <[email protected]> wrote in message
    > news:#[email protected]...
    > > RD
    > > The VBA way may well be slower (looping through 6000 cells) but it
    > > doesn't increase the size of the file like all those formulas do. I

    > usually
    > > refrain from using formulas when there are many (like 6000) formulas
    > > involved. Otto
    > > "RagDyer" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > You bring up a good point Otto.
    > > >
    > > > The Index and Match combination is supposed to be much more efficient

    > then
    > > > Vlookup, although I have no idea how it compares to VBA.
    > > >
    > > > This should work faster then the Vlookup formula I first suggested:
    > > >
    > > > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > > > --
    > > >
    > > >
    > > > Regards,
    > > >
    > > > RD
    > > > --------------------------------------------------------------------
    > > > Please keep all correspondence within the Group, so all may benefit!
    > > > -------------------------------------------------------------------
    > > >
    > > >
    > > > "Otto Moehrbach" <[email protected]> wrote in message
    > > > news:%[email protected]...
    > > > You can do this with VLookup formulas but there is a problem with that,
    > > > that
    > > > you should be aware of. With 6000 rows of data and just 2 columns in
    > > > Sheet2, you will need 6000 VLookup formulas. The problem is that the

    > file
    > > > will grow big in a hurry. If you have more than just those 2 columns,
    > > > which
    > > > I suspect you do, you will end up with a big file. If that is not a
    > > > problem
    > > > for you then I would say to go with the formulas. The alternative is to
    > > > go
    > > > with a VBA solution. Post back if you need more. HTH Otto
    > > > "The Good Deeds Team" <[email protected]> wrote
    > > > in
    > > > message news:[email protected]...
    > > >>I have two spreadsheets.
    > > >>
    > > >> the first has a list of account numbers, for example
    > > >>
    > > >> 10
    > > >> 20
    > > >> 30
    > > >>
    > > >> The second has a list of acount numbers and an amount, for example
    > > >>
    > > >> 10 100
    > > >> 20 150
    > > >> 30 175
    > > >>
    > > >> How can I in the fisrt spreadsheet, use a formula to look in the second
    > > >> spreadsheet for the corresponding account number, example 20, and pull
    > > >> the
    > > >> correct value, example 150 withoutout coding each row in the first
    > > >> spreadsheet to look exactly at the specific row in the second

    > spreadsheet
    > > >>
    > > >> For example, I can do this and it works
    > > >>
    > > >> =(second spreadsheet!$F$11)
    > > >>
    > > >> however I just want it to know where in the second spreadsheet column F
    > > >> the
    > > >> value 20 exists and pull 175 from colum G
    > > >>
    > > >> I don't want to have to tell the firts spreadsheet it is on row 11, I
    > > >> want
    > > >> it to look through coulmn F and find the value 20, which matches the
    > > >> value
    > > >> in
    > > >> the first spreadsheet, and then give me the amount from that row in the
    > > >> second spreadsheet, say column g
    > > >>
    > > >> this application is really 6000 accounts in the first spreadsheet, that
    > > >> need
    > > >> to match 6000 accounts in the second spreadsheet, and I don't want to
    > > >> code
    > > >> it
    > > >> row by row, simplely match the accopunt numbers from the two

    > spreadsheets
    > > >> and
    > > >> give me a value on the corresponding row.
    > > >>
    > > >>
    > > >>
    > > >
    > > >

    > >
    > >

    >
    >
    >


  9. #9
    The Good Deeds Team
    Guest

    Re: How do I connect fields from two spreadsheets

    if the first table has the account number, but it does not exist in the
    second table, the result is '#N/A', how can I make the result 0 (zero)

    "RagDyer" wrote:

    > You bring up a good point Otto.
    >
    > The Index and Match combination is supposed to be much more efficient then
    > Vlookup, although I have no idea how it compares to VBA.
    >
    > This should work faster then the Vlookup formula I first suggested:
    >
    > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > --
    >
    >
    > Regards,
    >
    > RD
    > --------------------------------------------------------------------
    > Please keep all correspondence within the Group, so all may benefit!
    > -------------------------------------------------------------------
    >
    >
    > "Otto Moehrbach" <[email protected]> wrote in message
    > news:%[email protected]...
    > You can do this with VLookup formulas but there is a problem with that, that
    > you should be aware of. With 6000 rows of data and just 2 columns in
    > Sheet2, you will need 6000 VLookup formulas. The problem is that the file
    > will grow big in a hurry. If you have more than just those 2 columns, which
    > I suspect you do, you will end up with a big file. If that is not a problem
    > for you then I would say to go with the formulas. The alternative is to go
    > with a VBA solution. Post back if you need more. HTH Otto
    > "The Good Deeds Team" <[email protected]> wrote in
    > message news:[email protected]...
    > >I have two spreadsheets.
    > >
    > > the first has a list of account numbers, for example
    > >
    > > 10
    > > 20
    > > 30
    > >
    > > The second has a list of acount numbers and an amount, for example
    > >
    > > 10 100
    > > 20 150
    > > 30 175
    > >
    > > How can I in the fisrt spreadsheet, use a formula to look in the second
    > > spreadsheet for the corresponding account number, example 20, and pull the
    > > correct value, example 150 withoutout coding each row in the first
    > > spreadsheet to look exactly at the specific row in the second spreadsheet
    > >
    > > For example, I can do this and it works
    > >
    > > =(second spreadsheet!$F$11)
    > >
    > > however I just want it to know where in the second spreadsheet column F
    > > the
    > > value 20 exists and pull 175 from colum G
    > >
    > > I don't want to have to tell the firts spreadsheet it is on row 11, I want
    > > it to look through coulmn F and find the value 20, which matches the value
    > > in
    > > the first spreadsheet, and then give me the amount from that row in the
    > > second spreadsheet, say column g
    > >
    > > this application is really 6000 accounts in the first spreadsheet, that
    > > need
    > > to match 6000 accounts in the second spreadsheet, and I don't want to code
    > > it
    > > row by row, simplely match the accopunt numbers from the two spreadsheets
    > > and
    > > give me a value on the corresponding row.
    > >
    > >
    > >

    >
    >
    >


  10. #10
    RagDyer
    Guest

    Re: How do I connect fields from two spreadsheets

    Try this:

    =IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$6000,0)),0,INDEX(Sheet2!$B$2:$B$6000,MATCH(
    A2,Sheet2!$A$2:$A$6000,0)))
    --

    HTH,

    RD
    ==============================================
    Please keep all correspondence within the Group, so all may benefit!
    ==============================================


    "The Good Deeds Team" <[email protected]> wrote in
    message news:[email protected]...
    if the first table has the account number, but it does not exist in the
    second table, the result is '#N/A', how can I make the result 0 (zero)

    "RagDyer" wrote:

    > You bring up a good point Otto.
    >
    > The Index and Match combination is supposed to be much more efficient then
    > Vlookup, although I have no idea how it compares to VBA.
    >
    > This should work faster then the Vlookup formula I first suggested:
    >
    > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > --
    >
    >
    > Regards,
    >
    > RD
    > --------------------------------------------------------------------
    > Please keep all correspondence within the Group, so all may benefit!
    > -------------------------------------------------------------------
    >
    >
    > "Otto Moehrbach" <[email protected]> wrote in message
    > news:%[email protected]...
    > You can do this with VLookup formulas but there is a problem with that,

    that
    > you should be aware of. With 6000 rows of data and just 2 columns in
    > Sheet2, you will need 6000 VLookup formulas. The problem is that the file
    > will grow big in a hurry. If you have more than just those 2 columns,

    which
    > I suspect you do, you will end up with a big file. If that is not a

    problem
    > for you then I would say to go with the formulas. The alternative is to

    go
    > with a VBA solution. Post back if you need more. HTH Otto
    > "The Good Deeds Team" <[email protected]> wrote

    in
    > message news:[email protected]...
    > >I have two spreadsheets.
    > >
    > > the first has a list of account numbers, for example
    > >
    > > 10
    > > 20
    > > 30
    > >
    > > The second has a list of acount numbers and an amount, for example
    > >
    > > 10 100
    > > 20 150
    > > 30 175
    > >
    > > How can I in the fisrt spreadsheet, use a formula to look in the second
    > > spreadsheet for the corresponding account number, example 20, and pull

    the
    > > correct value, example 150 withoutout coding each row in the first
    > > spreadsheet to look exactly at the specific row in the second

    spreadsheet
    > >
    > > For example, I can do this and it works
    > >
    > > =(second spreadsheet!$F$11)
    > >
    > > however I just want it to know where in the second spreadsheet column F
    > > the
    > > value 20 exists and pull 175 from colum G
    > >
    > > I don't want to have to tell the firts spreadsheet it is on row 11, I

    want
    > > it to look through coulmn F and find the value 20, which matches the

    value
    > > in
    > > the first spreadsheet, and then give me the amount from that row in the
    > > second spreadsheet, say column g
    > >
    > > this application is really 6000 accounts in the first spreadsheet, that
    > > need
    > > to match 6000 accounts in the second spreadsheet, and I don't want to

    code
    > > it
    > > row by row, simplely match the accopunt numbers from the two

    spreadsheets
    > > and
    > > give me a value on the corresponding row.
    > >
    > >
    > >

    >
    >
    >



  11. #11
    RagDyer
    Guest

    Re: How do I connect fields from two spreadsheets

    I don't know if this will help or not, and maybe I'm missing something, but
    .... why do you have the WB name *repeated twice* in the Match() portion of
    your formula???

    Doesn't this work just as well for you?

    =-ROUND(INDEX('[10010 Budget Ofc of the President.xls]Sheet
    1'!$F$1:$F$100,MATCH($A503,'[10010 Budget Ofc of the President.xls]Sheet
    1'!$M$1:$M$100,0))/3,2)


    --

    HTH,

    RD
    ==============================================
    Please keep all correspondence within the Group, so all may benefit!
    ==============================================

    "The Good Deeds Team" <[email protected]> wrote in
    message news:[email protected]...
    This is really cool and worked great - now I would like to extend the model.

    The application is a budget model

    Budgets are stored in 140 separate worksheets, I thought about, having 140
    tabs, but someone in the office said that would not work for them

    Your suggestion worked for me as long as the other spreahseet was open (as
    in the following example)

    =-ROUND(INDEX('[10010 Budget Ofc of the President.xls]Sheet 1'!$F$1:$F$100,
    MATCH($A503,'[10010 Budget Ofc of the President.xls]Sheet 1'!$M$1:'[10010
    Budget Ofc of the President.xls]Sheet 1'!$M$100,0))/3,2)

    However if I close the other spreadsheet, and the first spreadsheet as well,
    then open the first spreadsheet and say update from other spreadsheets, the
    fields gets the value REF# and the formula changes to this:

    =-ROUND(INDEX('S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget
    Ofc of the President.xls]Sheet 1'!$F$1:$F$100,
    MATCH($A503,'S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\[10010 Budget
    Ofc of the President.xls]Sheet 1'!$M$1:'S:\ACCOUNTING\Budgets\Budgets\FY2005
    Budget\OOP\[10010 Budget Ofc of the President.xls]Sheet 1'!$M$100,0))/3,2)

    Even if this worked, I wanted to reduce the string

    S:\ACCOUNTING\Budgets\Budgets\FY2005 Budget\OOP\

    to

    ..\oop

    that does not seem to work either

    Do you have any suggestions?



    "RagDyer" wrote:

    > FWIW,
    > A couple of years ago, after reading about some speed tests Aladin did, I
    > switched a big WB database from a double (error checking) Vlookup formula

    to
    > the Index and Match combination,
    > It cut the opening time of the WB, IIRC, from 5, to just about 3 minutes !
    >
    > Regards,
    >
    > RD
    > --------------------------------------------------------------------
    > Please keep all correspondence within the Group, so all may benefit!
    > -------------------------------------------------------------------
    >
    > "Otto Moehrbach" <[email protected]> wrote in message
    > news:#[email protected]...
    > > RD
    > > The VBA way may well be slower (looping through 6000 cells) but it
    > > doesn't increase the size of the file like all those formulas do. I

    > usually
    > > refrain from using formulas when there are many (like 6000) formulas
    > > involved. Otto
    > > "RagDyer" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > You bring up a good point Otto.
    > > >
    > > > The Index and Match combination is supposed to be much more efficient

    > then
    > > > Vlookup, although I have no idea how it compares to VBA.
    > > >
    > > > This should work faster then the Vlookup formula I first suggested:
    > > >
    > > > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > > > --
    > > >
    > > >
    > > > Regards,
    > > >
    > > > RD
    > > > --------------------------------------------------------------------
    > > > Please keep all correspondence within the Group, so all may benefit!
    > > > -------------------------------------------------------------------
    > > >
    > > >
    > > > "Otto Moehrbach" <[email protected]> wrote in message
    > > > news:%[email protected]...
    > > > You can do this with VLookup formulas but there is a problem with

    that,
    > > > that
    > > > you should be aware of. With 6000 rows of data and just 2 columns in
    > > > Sheet2, you will need 6000 VLookup formulas. The problem is that the

    > file
    > > > will grow big in a hurry. If you have more than just those 2 columns,
    > > > which
    > > > I suspect you do, you will end up with a big file. If that is not a
    > > > problem
    > > > for you then I would say to go with the formulas. The alternative is

    to
    > > > go
    > > > with a VBA solution. Post back if you need more. HTH Otto
    > > > "The Good Deeds Team" <[email protected]>

    wrote
    > > > in
    > > > message news:[email protected]...
    > > >>I have two spreadsheets.
    > > >>
    > > >> the first has a list of account numbers, for example
    > > >>
    > > >> 10
    > > >> 20
    > > >> 30
    > > >>
    > > >> The second has a list of acount numbers and an amount, for example
    > > >>
    > > >> 10 100
    > > >> 20 150
    > > >> 30 175
    > > >>
    > > >> How can I in the fisrt spreadsheet, use a formula to look in the

    second
    > > >> spreadsheet for the corresponding account number, example 20, and

    pull
    > > >> the
    > > >> correct value, example 150 withoutout coding each row in the first
    > > >> spreadsheet to look exactly at the specific row in the second

    > spreadsheet
    > > >>
    > > >> For example, I can do this and it works
    > > >>
    > > >> =(second spreadsheet!$F$11)
    > > >>
    > > >> however I just want it to know where in the second spreadsheet column

    F
    > > >> the
    > > >> value 20 exists and pull 175 from colum G
    > > >>
    > > >> I don't want to have to tell the firts spreadsheet it is on row 11, I
    > > >> want
    > > >> it to look through coulmn F and find the value 20, which matches the
    > > >> value
    > > >> in
    > > >> the first spreadsheet, and then give me the amount from that row in

    the
    > > >> second spreadsheet, say column g
    > > >>
    > > >> this application is really 6000 accounts in the first spreadsheet,

    that
    > > >> need
    > > >> to match 6000 accounts in the second spreadsheet, and I don't want to
    > > >> code
    > > >> it
    > > >> row by row, simplely match the accopunt numbers from the two

    > spreadsheets
    > > >> and
    > > >> give me a value on the corresponding row.
    > > >>
    > > >>
    > > >>
    > > >
    > > >

    > >
    > >

    >
    >
    >



  12. #12
    The Good Deeds Team
    Guest

    Re: How do I connect fields from two spreadsheets

    yes, everything you said worked perfectly - you are the one.

    "RagDyer" wrote:

    > Try this:
    >
    > =IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$6000,0)),0,INDEX(Sheet2!$B$2:$B$6000,MATCH(
    > A2,Sheet2!$A$2:$A$6000,0)))
    > --
    >
    > HTH,
    >
    > RD
    > ==============================================
    > Please keep all correspondence within the Group, so all may benefit!
    > ==============================================
    >
    >
    > "The Good Deeds Team" <[email protected]> wrote in
    > message news:[email protected]...
    > if the first table has the account number, but it does not exist in the
    > second table, the result is '#N/A', how can I make the result 0 (zero)
    >
    > "RagDyer" wrote:
    >
    > > You bring up a good point Otto.
    > >
    > > The Index and Match combination is supposed to be much more efficient then
    > > Vlookup, although I have no idea how it compares to VBA.
    > >
    > > This should work faster then the Vlookup formula I first suggested:
    > >
    > > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > > --
    > >
    > >
    > > Regards,
    > >
    > > RD
    > > --------------------------------------------------------------------
    > > Please keep all correspondence within the Group, so all may benefit!
    > > -------------------------------------------------------------------
    > >
    > >
    > > "Otto Moehrbach" <[email protected]> wrote in message
    > > news:%[email protected]...
    > > You can do this with VLookup formulas but there is a problem with that,

    > that
    > > you should be aware of. With 6000 rows of data and just 2 columns in
    > > Sheet2, you will need 6000 VLookup formulas. The problem is that the file
    > > will grow big in a hurry. If you have more than just those 2 columns,

    > which
    > > I suspect you do, you will end up with a big file. If that is not a

    > problem
    > > for you then I would say to go with the formulas. The alternative is to

    > go
    > > with a VBA solution. Post back if you need more. HTH Otto
    > > "The Good Deeds Team" <[email protected]> wrote

    > in
    > > message news:[email protected]...
    > > >I have two spreadsheets.
    > > >
    > > > the first has a list of account numbers, for example
    > > >
    > > > 10
    > > > 20
    > > > 30
    > > >
    > > > The second has a list of acount numbers and an amount, for example
    > > >
    > > > 10 100
    > > > 20 150
    > > > 30 175
    > > >
    > > > How can I in the fisrt spreadsheet, use a formula to look in the second
    > > > spreadsheet for the corresponding account number, example 20, and pull

    > the
    > > > correct value, example 150 withoutout coding each row in the first
    > > > spreadsheet to look exactly at the specific row in the second

    > spreadsheet
    > > >
    > > > For example, I can do this and it works
    > > >
    > > > =(second spreadsheet!$F$11)
    > > >
    > > > however I just want it to know where in the second spreadsheet column F
    > > > the
    > > > value 20 exists and pull 175 from colum G
    > > >
    > > > I don't want to have to tell the firts spreadsheet it is on row 11, I

    > want
    > > > it to look through coulmn F and find the value 20, which matches the

    > value
    > > > in
    > > > the first spreadsheet, and then give me the amount from that row in the
    > > > second spreadsheet, say column g
    > > >
    > > > this application is really 6000 accounts in the first spreadsheet, that
    > > > need
    > > > to match 6000 accounts in the second spreadsheet, and I don't want to

    > code
    > > > it
    > > > row by row, simplely match the accopunt numbers from the two

    > spreadsheets
    > > > and
    > > > give me a value on the corresponding row.
    > > >
    > > >
    > > >

    > >
    > >
    > >

    >
    >


  13. #13
    Ragdyer
    Guest

    Re: How do I connect fields from two spreadsheets

    Appreciate the feed-back.
    --
    Regards,

    RD

    ---------------------------------------------------------------------------
    Please keep all correspondence within the NewsGroup, so all may benefit !
    ---------------------------------------------------------------------------
    "The Good Deeds Team" <[email protected]> wrote in
    message news:[email protected]...
    > yes, everything you said worked perfectly - you are the one.
    >
    > "RagDyer" wrote:
    >
    > > Try this:
    > >
    > >

    =IF(ISNA(MATCH(A2,Sheet2!$A$2:$A$6000,0)),0,INDEX(Sheet2!$B$2:$B$6000,MATCH(
    > > A2,Sheet2!$A$2:$A$6000,0)))
    > > --
    > >
    > > HTH,
    > >
    > > RD
    > > ==============================================
    > > Please keep all correspondence within the Group, so all may benefit!
    > > ==============================================
    > >
    > >
    > > "The Good Deeds Team" <[email protected]> wrote

    in
    > > message news:[email protected]...
    > > if the first table has the account number, but it does not exist in the
    > > second table, the result is '#N/A', how can I make the result 0 (zero)
    > >
    > > "RagDyer" wrote:
    > >
    > > > You bring up a good point Otto.
    > > >
    > > > The Index and Match combination is supposed to be much more efficient

    then
    > > > Vlookup, although I have no idea how it compares to VBA.
    > > >
    > > > This should work faster then the Vlookup formula I first suggested:
    > > >
    > > > =INDEX(Sheet2!$B$2:$B$6000,MATCH(A2,Sheet2!$A$2:$A$6000,0))
    > > > --
    > > >
    > > >
    > > > Regards,
    > > >
    > > > RD
    > > > --------------------------------------------------------------------
    > > > Please keep all correspondence within the Group, so all may benefit!
    > > > -------------------------------------------------------------------
    > > >
    > > >
    > > > "Otto Moehrbach" <[email protected]> wrote in message
    > > > news:%[email protected]...
    > > > You can do this with VLookup formulas but there is a problem with

    that,
    > > that
    > > > you should be aware of. With 6000 rows of data and just 2 columns in
    > > > Sheet2, you will need 6000 VLookup formulas. The problem is that the

    file
    > > > will grow big in a hurry. If you have more than just those 2 columns,

    > > which
    > > > I suspect you do, you will end up with a big file. If that is not a

    > > problem
    > > > for you then I would say to go with the formulas. The alternative is

    to
    > > go
    > > > with a VBA solution. Post back if you need more. HTH Otto
    > > > "The Good Deeds Team" <[email protected]>

    wrote
    > > in
    > > > message news:[email protected]...
    > > > >I have two spreadsheets.
    > > > >
    > > > > the first has a list of account numbers, for example
    > > > >
    > > > > 10
    > > > > 20
    > > > > 30
    > > > >
    > > > > The second has a list of acount numbers and an amount, for example
    > > > >
    > > > > 10 100
    > > > > 20 150
    > > > > 30 175
    > > > >
    > > > > How can I in the fisrt spreadsheet, use a formula to look in the

    second
    > > > > spreadsheet for the corresponding account number, example 20, and

    pull
    > > the
    > > > > correct value, example 150 withoutout coding each row in the first
    > > > > spreadsheet to look exactly at the specific row in the second

    > > spreadsheet
    > > > >
    > > > > For example, I can do this and it works
    > > > >
    > > > > =(second spreadsheet!$F$11)
    > > > >
    > > > > however I just want it to know where in the second spreadsheet

    column F
    > > > > the
    > > > > value 20 exists and pull 175 from colum G
    > > > >
    > > > > I don't want to have to tell the firts spreadsheet it is on row 11,

    I
    > > want
    > > > > it to look through coulmn F and find the value 20, which matches the

    > > value
    > > > > in
    > > > > the first spreadsheet, and then give me the amount from that row in

    the
    > > > > second spreadsheet, say column g
    > > > >
    > > > > this application is really 6000 accounts in the first spreadsheet,

    that
    > > > > need
    > > > > to match 6000 accounts in the second spreadsheet, and I don't want

    to
    > > code
    > > > > it
    > > > > row by row, simplely match the accopunt numbers from the two

    > > spreadsheets
    > > > > and
    > > > > give me a value on the corresponding row.
    > > > >
    > > > >
    > > > >
    > > >
    > > >
    > > >

    > >
    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1