| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
to link information between two lists
Hi,
I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
In your first list you could add a formula like this in E2:
=A2&B2&C2 and copy this down as far as your data extends. Assume this is on Sheet1. Then in your second list (Sheet2) you could have this formula in D2: =IF(ISNA(MATCH(A2&B2&C2,Sheet1!E:E,0)),"",INDEX(Sh eet1!D:D,MATCH (A2&B2&C2,Sheet1!E:E,0))) and then copy this down as far as you need to. Hope this helps. Pete On Oct 15, 6:56*pm, Bklynhyc wrote: Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
did as following but get a error.
"The formula you typed contains an error." actually I would like the ref. the other way around abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 100.00 ? 1233 06/02/09 100.00 ? 1233 06/02/09 100.00 ? 1233 06/02/09 100.00 ? second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 many thanks "Pete_UK" wrote: In your first list you could add a formula like this in E2: =A2&B2&C2 and copy this down as far as your data extends. Assume this is on Sheet1. Then in your second list (Sheet2) you could have this formula in D2: =IF(ISNA(MATCH(A2&B2&C2,Sheet1!E:E,0)),"",INDEX(Sh eet1!D:D,MATCH (A2&B2&C2,Sheet1!E:E,0))) and then copy this down as far as you need to. Hope this helps. Pete On Oct 15, 6:56 pm, Bklynhyc wrote: Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Great so it does work But one problem I it only fill in the first and last
line any suggestion? the result looks like this, I would like the other "<<?" to be filled in as well merchant date amt ref. 1234 6/1/2009 500 1 1233 6/2/2009 100 <<? 1233 6/2/2009 100 <<? 1233 6/2/2009 100 <<? 1233 6/2/2009 100 2 Many Thanks "Bklynhyc" wrote: did as following but get a error. "The formula you typed contains an error." actually I would like the ref. the other way around abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 100.00 ? 1233 06/02/09 100.00 ? 1233 06/02/09 100.00 ? 1233 06/02/09 100.00 ? second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 many thanks "Pete_UK" wrote: In your first list you could add a formula like this in E2: =A2&B2&C2 and copy this down as far as your data extends. Assume this is on Sheet1. Then in your second list (Sheet2) you could have this formula in D2: =IF(ISNA(MATCH(A2&B2&C2,Sheet1!E:E,0)),"",INDEX(Sh eet1!D:D,MATCH (A2&B2&C2,Sheet1!E:E,0))) and then copy this down as far as you need to. Hope this helps. Pete On Oct 15, 6:56 pm, Bklynhyc wrote: Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Hi,
What should the reference number be for the second entry on the second list I.e. should it be 2,3,4 or 5. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
I'm sorry its the other way around,
abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 "Ashish Mathur" wrote: Hi, What should the reference number be for the second entry on the second list I.e. should it be 2,3,4 or 5. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Hi,
One among many approaches. Use the formula in cell E15. B5:E9 carries the first three columns of data on the first list. B15, C15 and D15 carry the merchant, data and amount ref on the second list. =INDEX($E$5:$E$9,MATCH(1,INDEX(($B$5:$B$9=B15)*($C $5:$C$9=C15)*($D$5:$D$9=D15),,),0),1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I'm sorry its the other way around, abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 "Ashish Mathur" wrote: Hi, What should the reference number be for the second entry on the second list I.e. should it be 2,3,4 or 5. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
So this is what i got , any suggestion?
=INDEX($J$2:$J$9,MATCH(1,INDEX(($G$2:$G$9=A2)*($H$ 2:$H$9=B2)*($I$2:$I$9=C2),,),0),1) first list with reference. GHIJ merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 #N/A 1233 06/02/09 100.00 #N/A 1233 06/02/09 100.00 #N/A 1233 06/02/09 100.00 #N/A "Ashish Mathur" wrote: Hi, One among many approaches. Use the formula in cell E15. B5:E9 carries the first three columns of data on the first list. B15, C15 and D15 carry the merchant, data and amount ref on the second list. =INDEX($E$5:$E$9,MATCH(1,INDEX(($B$5:$B$9=B15)*($C $5:$C$9=C15)*($D$5:$D$9=D15),,),0),1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I'm sorry its the other way around, abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 "Ashish Mathur" wrote: Hi, What should the reference number be for the second entry on the second list I.e. should it be 2,3,4 or 5. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Hi,
Try this =IF(D15=DSUM($B$4:$D$9,D$14,$B$14:C15)-SUM($D$14:D14),INDEX($E$5:$E$9,MATCH(1,INDEX(($B$5 :$B$9=B15)*($C$5:$C$9=C15),,),0),1),"") -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... So this is what i got , any suggestion? =INDEX($J$2:$J$9,MATCH(1,INDEX(($G$2:$G$9=A2)*($H$ 2:$H$9=B2)*($I$2:$I$9=C2),,),0),1) first list with reference. GHIJ merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 #N/A 1233 06/02/09 100.00 #N/A 1233 06/02/09 100.00 #N/A 1233 06/02/09 100.00 #N/A "Ashish Mathur" wrote: Hi, One among many approaches. Use the formula in cell E15. B5:E9 carries the first three columns of data on the first list. B15, C15 and D15 carry the merchant, data and amount ref on the second list. =INDEX($E$5:$E$9,MATCH(1,INDEX(($B$5:$B$9=B15)*($C $5:$C$9=C15)*($D$5:$D$9=D15),,),0),1) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I'm sorry its the other way around, abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 1233 06/02/09 100.00 2 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 400.00 2 "Ashish Mathur" wrote: Hi, What should the reference number be for the second entry on the second list I.e. should it be 2,3,4 or 5. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, I would like to link the amounts over two lists of totals, first list with reference. abcd merchant date amt ref. 1234 06/01/09 500.00 1 1233 06/02/09 100.00 2 1233 06/02/09 100.00 3 1233 06/02/09 100.00 4 1233 06/02/09 100.00 5 second list w/o reference abcd merchant date amt ref. 1234 06/01/09 500.00 ? 1233 06/02/09 400.00 ? I've been using ref. number manually to link them, is there a better way? Thanks at advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a link for particular information | Excel Discussion (Misc queries) | |||
| Excel Lists - Automatically Changing Information | Excel Discussion (Misc queries) | |||
| IF Function with the link to different lists | Excel Discussion (Misc queries) | |||
| Sorting information into lists | Excel Discussion (Misc queries) | |||
| For accounting, How to link two lists each in different drop down | Excel Discussion (Misc queries) | |||
Hybrid Mode
