Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain criteria
I have a main document made and need some cells to transfer to other
workbooks if certain criteria is met. Is this possible? i.e., If Cell A4 = January, & Cell C4 = 2009, & Cell G4 = Smith, then I need the data from cells A4, B4, C4, D4, E4, F4, S4, T4, U4, & V4 to transfer to Cells A4-J4 in the second workbook???? Thanks in advance!!! Breezy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain criteria
Here's one formulas play which will autocopy all lines satisfying the
criteria from the source sheet into another sheet. It might serve your underlying intents sufficiently. Assume source data in Sheet1, in row2 down In another sheet, Criteria Inputs in A1: January A2: 2009 A3: Smith In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A2=$A$1,S heet1!C2=$A$2,Sheet1!G2=$A$3),ROW(),"")) Copy C2 down to cover the max expected extent of data in Sheet1. This is the criteria col. Minimize/hide col C. Then place in D2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1)))) Copy D2 to I2. This extracts the source cols A to F. J2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1)))) Copy J2 to M2. This extracts the source cols S to V. Select D2:M2, copy down to return the required results. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: I have a main document made and need some cells to transfer to other workbooks if certain criteria is met. Is this possible? i.e., If Cell A4 = January, & Cell C4 = 2009, & Cell G4 = Smith, then I need the data from cells A4, B4, C4, D4, E4, F4, S4, T4, U4, & V4 to transfer to Cells A4-J4 in the second workbook???? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Thank you for your time Max! I must be doing something wrong. I'm not very
experienced at this.... I copied the functions to the corresponding cell in sheet3. Then went to sheet1 and input the data in A1:January, A2: 2009, and in cell A3: Smith. Nothing happens. Can you tell me what I'm doing wrong? "Max" wrote: Here's one formulas play which will autocopy all lines satisfying the criteria from the source sheet into another sheet. It might serve your underlying intents sufficiently. Assume source data in Sheet1, in row2 down In another sheet, Criteria Inputs in A1: January A2: 2009 A3: Smith In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A2=$A$1,S heet1!C2=$A$2,Sheet1!G2=$A$3),ROW(),"")) Copy C2 down to cover the max expected extent of data in Sheet1. This is the criteria col. Minimize/hide col C. Then place in D2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1)))) Copy D2 to I2. This extracts the source cols A to F. J2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1)))) Copy J2 to M2. This extracts the source cols S to V. Select D2:M2, copy down to return the required results. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: I have a main document made and need some cells to transfer to other workbooks if certain criteria is met. Is this possible? i.e., If Cell A4 = January, & Cell C4 = 2009, & Cell G4 = Smith, then I need the data from cells A4, B4, C4, D4, E4, F4, S4, T4, U4, & V4 to transfer to Cells A4-J4 in the second workbook???? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Best way to diagnose is to see exactly what's happening over there
Can you upload your sample file using a free filehost, then post a link to it here? You can use this "easy-to-use" free filehost to upload: http://www.freefilehosting.net/ Copy the "direct link" which is generated after you upload, then paste it here in your reply (desensitize the data in your sample as required) P/s: Pl keep discussions within the newsgroups. Better for all. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote in message ... Thank you for your time Max! I must be doing something wrong. I'm not very experienced at this.... I copied the functions to the corresponding cell in sheet3. Then went to sheet1 and input the data in A1:January, A2: 2009, and in cell A3: Smith. Nothing happens. Can you tell me what I'm doing wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Thanks again for your time!
The master file where I will enter all the information is he http://freefilehosting.net/download/448gg The file that I need information to transfer to is he http://freefilehosting.net/download/448gf "Max" wrote: Best way to diagnose is to see exactly what's happening over there Can you upload your sample file using a free filehost, then post a link to it here? You can use this "easy-to-use" free filehost to upload: http://www.freefilehosting.net/ Copy the "direct link" which is generated after you upload, then paste it here in your reply (desensitize the data in your sample as required) P/s: Pl keep discussions within the newsgroups. Better for all. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote in message ... Thank you for your time Max! I must be doing something wrong. I'm not very experienced at this.... I copied the functions to the corresponding cell in sheet3. Then went to sheet1 and input the data in A1:January, A2: 2009, and in cell A3: Smith. Nothing happens. Can you tell me what I'm doing wrong? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Here's the working sample. Some adjustments done
as your source data starts in row4 instead of row2: http://freefilehosting.net/download/44a6c In Enquiry, Inputs in A1:A3 as before In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A4=$A$1,S heet1!C4=$A$2,Sheet1!G4=$A$3),ROW(),"")) In D2, copied to I2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))+2)) In J2, copied to M2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1))+2)) Select C2:M2, copy down to cover max expected extent of source data in Sheet1. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. P/s: Keep things in the same book, much simpler. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: Thanks again for your time! The master file where I will enter all the information is he http://freefilehosting.net/download/448gg The file that I need information to transfer to is he http://freefilehosting.net/download/448gf |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Sorry it took me so long to get back to this. I've been gone. Thank you so
much for your help! "Max" wrote: Here's the working sample. Some adjustments done as your source data starts in row4 instead of row2: http://freefilehosting.net/download/44a6c In Enquiry, Inputs in A1:A3 as before In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A4=$A$1,S heet1!C4=$A$2,Sheet1!G4=$A$3),ROW(),"")) In D2, copied to I2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))+2)) In J2, copied to M2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1))+2)) Select C2:M2, copy down to cover max expected extent of source data in Sheet1. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. P/s: Keep things in the same book, much simpler. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: Thanks again for your time! The master file where I will enter all the information is he http://freefilehosting.net/download/448gg The file that I need information to transfer to is he http://freefilehosting.net/download/448gf |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Welcome
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote in message ... Sorry it took me so long to get back to this. I've been gone. Thank you so much for your help! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Hey Max,
I'm back to working on trying to make this template work the way i need it to. I need to add to the names on the enquiry sheet. I'm not sure where the data is stored for the drop down list. thanks in advance! Wendy "Max" wrote: Here's the working sample. Some adjustments done as your source data starts in row4 instead of row2: http://freefilehosting.net/download/44a6c In Enquiry, Inputs in A1:A3 as before In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A4=$A$1,S heet1!C4=$A$2,Sheet1!G4=$A$3),ROW(),"")) In D2, copied to I2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))+2)) In J2, copied to M2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1))+2)) Select C2:M2, copy down to cover max expected extent of source data in Sheet1. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. P/s: Keep things in the same book, much simpler. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: Thanks again for your time! The master file where I will enter all the information is he http://freefilehosting.net/download/448gg The file that I need information to transfer to is he http://freefilehosting.net/download/448gf |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
On Max's working sample I see only one column with dropdowns and that is for
Months in Column A. The source for that list range and other ranges is found on the hidden sheet "Lists". FormatSheetUnhide "Lists" to see ranges for all defined names. Which list of names do you want to add to? Gord Dibben MS Excel MVP On Fri, 20 Feb 2009 12:39:01 -0800, breezy wrote: Hey Max, I'm back to working on trying to make this template work the way i need it to. I need to add to the names on the enquiry sheet. I'm not sure where the data is stored for the drop down list. thanks in advance! Wendy "Max" wrote: Here's the working sample. Some adjustments done as your source data starts in row4 instead of row2: http://freefilehosting.net/download/44a6c In Enquiry, Inputs in A1:A3 as before In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A4=$A$1,S heet1!C4=$A$2,Sheet1!G4=$A$3),ROW(),"")) In D2, copied to I2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))+2)) In J2, copied to M2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1))+2)) Select C2:M2, copy down to cover max expected extent of source data in Sheet1. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. P/s: Keep things in the same book, much simpler. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: Thanks again for your time! The master file where I will enter all the information is he http://freefilehosting.net/download/448gg The file that I need information to transfer to is he http://freefilehosting.net/download/448gf |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
oooops!
Was looking at Sheet1, not Enquiry The list of names for names dropdown is simply a comma de-limited list of Smith,George You can add to those in the source list........comma de-limited. Smith,George,Breezy,Gord,Max Gord On Fri, 20 Feb 2009 14:12:37 -0800, Gord Dibben <gorddibbATshawDOTca wrote: On Max's working sample I see only one column with dropdowns and that is for Months in Column A. The source for that list range and other ranges is found on the hidden sheet "Lists". FormatSheetUnhide "Lists" to see ranges for all defined names. Which list of names do you want to add to? Gord Dibben MS Excel MVP On Fri, 20 Feb 2009 12:39:01 -0800, breezy wrote: Hey Max, I'm back to working on trying to make this template work the way i need it to. I need to add to the names on the enquiry sheet. I'm not sure where the data is stored for the drop down list. thanks in advance! Wendy "Max" wrote: Here's the working sample. Some adjustments done as your source data starts in row4 instead of row2: http://freefilehosting.net/download/44a6c In Enquiry, Inputs in A1:A3 as before In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A4=$A$1,S heet1!C4=$A$2,Sheet1!G4=$A$3),ROW(),"")) In D2, copied to I2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))+2)) In J2, copied to M2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1))+2)) Select C2:M2, copy down to cover max expected extent of source data in Sheet1. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. P/s: Keep things in the same book, much simpler. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: Thanks again for your time! The master file where I will enter all the information is he http://freefilehosting.net/download/448gg The file that I need information to transfer to is he http://freefilehosting.net/download/448gf |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
Hi Gord,
I don't understand how to get to the dropdown or how to open it. I'm trying to streamline a template for my boss and I don't have very much experience in this. Thanks in advance!!!! "Gord Dibben" wrote: oooops! Was looking at Sheet1, not Enquiry The list of names for names dropdown is simply a comma de-limited list of Smith,George You can add to those in the source list........comma de-limited. Smith,George,Breezy,Gord,Max Gord On Fri, 20 Feb 2009 14:12:37 -0800, Gord Dibben <gorddibbATshawDOTca wrote: On Max's working sample I see only one column with dropdowns and that is for Months in Column A. The source for that list range and other ranges is found on the hidden sheet "Lists". FormatSheetUnhide "Lists" to see ranges for all defined names. Which list of names do you want to add to? Gord Dibben MS Excel MVP On Fri, 20 Feb 2009 12:39:01 -0800, breezy wrote: Hey Max, I'm back to working on trying to make this template work the way i need it to. I need to add to the names on the enquiry sheet. I'm not sure where the data is stored for the drop down list. thanks in advance! Wendy "Max" wrote: Here's the working sample. Some adjustments done as your source data starts in row4 instead of row2: http://freefilehosting.net/download/44a6c In Enquiry, Inputs in A1:A3 as before In C2: =IF(COUNTA($A$1:$A$3)<3,"",IF(AND(Sheet1!A4=$A$1,S heet1!C4=$A$2,Sheet1!G4=$A$3),ROW(),"")) In D2, copied to I2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))+2)) In J2, copied to M2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!S:S,SM ALL($C:$C,ROWS($1:1))+2)) Select C2:M2, copy down to cover max expected extent of source data in Sheet1. Format cols to taste. All lines satisfying the criteria inputs in A1:A3 will appear neatly packed at the top. Try changing the criteria inputs to a different set, it'll return accordingly. P/s: Keep things in the same book, much simpler. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "breezy" wrote: Thanks again for your time! The master file where I will enter all the information is he http://freefilehosting.net/download/448gg The file that I need information to transfer to is he http://freefilehosting.net/download/448gf |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain crite
.. I don't understand how to get to the dropdown or how to open it.
Select that cell A3 in sheet: Enquiry Click Data Validation In the Settings tab: Edit/Add the names in the "Source" box (separated by a comma) Click OK P/s: Please start new threads for new queries in future. This thread is long closed. Fortunate that Gord picked it up earlier (thanks, Gord!). -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain criteria
Hi Max, I know I should start a new thread, but not sure how to get all the
information to a new thread. Is there a way to show more than one month on the enquiry results? for instance, if I wanted to know how Smith preformed for the year?? "breezy" wrote: I have a main document made and need some cells to transfer to other workbooks if certain criteria is met. Is this possible? i.e., If Cell A4 = January, & Cell C4 = 2009, & Cell G4 = Smith, then I need the data from cells A4, B4, C4, D4, E4, F4, S4, T4, U4, & V4 to transfer to Cells A4-J4 in the second workbook???? Thanks in advance!!! Breezy |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Transfer data from a cell to another workbook if certain criteria
.. but not sure how to get all the information to a new thread ..
Describe with specifics on what you have: sheetnames, data ranges, the desired calculation logics, etc. Support it by pasting some sample data and the expected results (in plain text in the post itself). Keep it to 1 specific query per thread. Make it attractive for responders to respond. Close off each thread by thanking all responders individually (reply to each responder), and don't forget to celebrate success, do rate all responses by clicking the YES buttons in MS' webpages, or by clicking the stars in google. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- |
#16
|
|||
|
|||
Hi, I tried your techniques and found working perfectly, but I can not understand the complete logic, will face probs if tried in future.., could you please give a brief explanation of the logic if possible.
Thanks Riz Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to transfer data from one workbook to another via VBA | Excel Discussion (Misc queries) | |||
Auto transfer data from 1 sheet to another within same workbook | Excel Worksheet Functions | |||
Data transfer from a template to a workbook | Excel Worksheet Functions | |||
Can data from one ws transfer to another IF cell has text? | Excel Worksheet Functions | |||
How do I transfer data in a pdf file into an excel workbook? | Excel Discussion (Misc queries) |