Transfer data from a cell to another workbook if certain criteria

[breezy]

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

 

[Max]

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,Sheet1!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,SMALL($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,SMALL($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]

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]

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]

Thanks again for your time!

The master file where I will enter all the information is here:
http://freefilehosting.net/download/448gg


The file that I need information to transfer to is here:
http://freefilehosting.net/download/448gf

 

[Max]

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,Sheet1!C4=$A$2,Sheet1!G4=$A$3),ROW(),""))

In D2, copied to I2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(Sheet1!A:A,SMALL($C:$C,ROWS($1:1))+2))

In J2, copied to M2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(Sheet1!S:S,SMALL($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]

Sorry it took me so long to get back to this. I've been gone. Thank you so
much for your help!

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[breezy]

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

 

[Gord Dibben]

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".

Format>Sheet>Unhide "Lists" to see ranges for all defined names.

Which list of names do you want to add to?



Gord Dibben MS Excel MVP

 

[Gord Dibben]

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

 

[breezy]

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!!!!

 

[Max]

.. 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
---

 

[breezy]

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??

 

[Max]

.. 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
---