| 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
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 |
|
#4
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
I only get a "#Value!" Please advise
many Thanks "Ashish Mathur" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Hi,
Did you Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I only get a "#Value!" Please advise many Thanks "Ashish Mathur" wrote: 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Hi,
Thanks for following up, yes i have Ctrl+Shift+Enter. Note it start at column B 1A BCDE 2 3 merchant date amt ref. 4 1234 6/1/2009 500 1 5 1233 6/2/2009 400 2 6 7 8 9 10 11 12 13 merchant date amt ref. 14 1234 6/1/2009 500 #VALUE! 15 1233 6/2/2009 100 #VALUE! 16 1233 6/2/2009 100 #VALUE! 17 1233 6/2/2009 100 #VALUE! 18 1233 6/2/2009 100 #VALUE! =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),"") Please advise Thank You "Ashish Mathur" wrote: Hi, Did you Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I only get a "#Value!" Please advise many Thanks "Ashish Mathur" wrote: 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Hi,
Sorry - the Ctrl+Shift+Enter is not required 1. B4:D4 should contain Merchant, Data and Amt 2. D14 should have Amt 3. B14:C14 should have Merchant, Date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, Thanks for following up, yes i have Ctrl+Shift+Enter. Note it start at column B 1A BCDE 2 3 merchant date amt ref. 4 1234 6/1/2009 500 1 5 1233 6/2/2009 400 2 6 7 8 9 10 11 12 13 merchant date amt ref. 14 1234 6/1/2009 500 #VALUE! 15 1233 6/2/2009 100 #VALUE! 16 1233 6/2/2009 100 #VALUE! 17 1233 6/2/2009 100 #VALUE! 18 1233 6/2/2009 100 #VALUE! =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),"") Please advise Thank You "Ashish Mathur" wrote: Hi, Did you Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I only get a "#Value!" Please advise many Thanks "Ashish Mathur" wrote: 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 |
|
#13
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 . |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Great so it work But one problem 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 (2) 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 "Ashish Mathur" wrote: Hi, Sorry - the Ctrl+Shift+Enter is not required 1. B4:D4 should contain Merchant, Data and Amt 2. D14 should have Amt 3. B14:C14 should have Merchant, Date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, Thanks for following up, yes i have Ctrl+Shift+Enter. Note it start at column B 1A BCDE 2 3 merchant date amt ref. 4 1234 6/1/2009 500 1 5 1233 6/2/2009 400 2 6 7 8 9 10 11 12 13 merchant date amt ref. 14 1234 6/1/2009 500 #VALUE! 15 1233 6/2/2009 100 #VALUE! 16 1233 6/2/2009 100 #VALUE! 17 1233 6/2/2009 100 #VALUE! 18 1233 6/2/2009 100 #VALUE! =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),"") Please advise Thank You "Ashish Mathur" wrote: Hi, Did you Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I only get a "#Value!" Please advise many Thanks "Ashish Mathur" wrote: 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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Check once again - it should fill all the cells
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Great so it work But one problem 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 (2) 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 "Ashish Mathur" wrote: Hi, Sorry - the Ctrl+Shift+Enter is not required 1. B4:D4 should contain Merchant, Data and Amt 2. D14 should have Amt 3. B14:C14 should have Merchant, Date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, Thanks for following up, yes i have Ctrl+Shift+Enter. Note it start at column B 1A BCDE 2 3 merchant date amt ref. 4 1234 6/1/2009 500 1 5 1233 6/2/2009 400 2 6 7 8 9 10 11 12 13 merchant date amt ref. 14 1234 6/1/2009 500 #VALUE! 15 1233 6/2/2009 100 #VALUE! 16 1233 6/2/2009 100 #VALUE! 17 1233 6/2/2009 100 #VALUE! 18 1233 6/2/2009 100 #VALUE! =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),"") Please advise Thank You "Ashish Mathur" wrote: Hi, Did you Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I only get a "#Value!" Please advise many Thanks "Ashish Mathur" wrote: 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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
I try to add other line to see but same situation, any suggestion
merchant date amt ref. 1234 6/1/2009 500.00 1 1233 6/2/2009 400.00 2 1122 6/3/2009 100.00 3 merchant date amt ref. 1234 6/1/2009 500.00 1 1233 6/2/2009 100.00 1233 6/2/2009 100.00 1233 6/2/2009 100.00 1233 6/2/2009 100.00 2 1122 6/3/2009 1122 6/3/2009 50.00 1122 6/3/2009 0.50 1122 6/3/2009 24.50 1122 6/3/2009 25.00 3 Thank You "Ashish Mathur" wrote: Check once again - it should fill all the cells -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Great so it work But one problem 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 (2) 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 "Ashish Mathur" wrote: Hi, Sorry - the Ctrl+Shift+Enter is not required 1. B4:D4 should contain Merchant, Data and Amt 2. D14 should have Amt 3. B14:C14 should have Merchant, Date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, Thanks for following up, yes i have Ctrl+Shift+Enter. Note it start at column B 1A BCDE 2 3 merchant date amt ref. 4 1234 6/1/2009 500 1 5 1233 6/2/2009 400 2 6 7 8 9 10 11 12 13 merchant date amt ref. 14 1234 6/1/2009 500 #VALUE! 15 1233 6/2/2009 100 #VALUE! 16 1233 6/2/2009 100 #VALUE! 17 1233 6/2/2009 100 #VALUE! 18 1233 6/2/2009 100 #VALUE! =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),"") Please advise Thank You "Ashish Mathur" wrote: Hi, Did you Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I only get a "#Value!" Please advise many Thanks "Ashish Mathur" wrote: 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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
to link information between two lists
Please mail the workbook to me as ask(at)ashishmathur(dot)com. Pleas ensure
that the file size is small - mail only the relevant portion -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I try to add other line to see but same situation, any suggestion merchant date amt ref. 1234 6/1/2009 500.00 1 1233 6/2/2009 400.00 2 1122 6/3/2009 100.00 3 merchant date amt ref. 1234 6/1/2009 500.00 1 1233 6/2/2009 100.00 1233 6/2/2009 100.00 1233 6/2/2009 100.00 1233 6/2/2009 100.00 2 1122 6/3/2009 1122 6/3/2009 50.00 1122 6/3/2009 0.50 1122 6/3/2009 24.50 1122 6/3/2009 25.00 3 Thank You "Ashish Mathur" wrote: Check once again - it should fill all the cells -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Great so it work But one problem 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 (2) 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 "Ashish Mathur" wrote: Hi, Sorry - the Ctrl+Shift+Enter is not required 1. B4:D4 should contain Merchant, Data and Amt 2. D14 should have Amt 3. B14:C14 should have Merchant, Date -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... Hi, Thanks for following up, yes i have Ctrl+Shift+Enter. Note it start at column B 1A BCDE 2 3 merchant date amt ref. 4 1234 6/1/2009 500 1 5 1233 6/2/2009 400 2 6 7 8 9 10 11 12 13 merchant date amt ref. 14 1234 6/1/2009 500 #VALUE! 15 1233 6/2/2009 100 #VALUE! 16 1233 6/2/2009 100 #VALUE! 17 1233 6/2/2009 100 #VALUE! 18 1233 6/2/2009 100 #VALUE! =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),"") Please advise Thank You "Ashish Mathur" wrote: Hi, Did you Ctrl+Shift+Enter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Bklynhyc" wrote in message ... I only get a "#Value!" Please advise many Thanks "Ashish Mathur" wrote: 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) | |||
Linear Mode
