• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy data from multiple cells based on a value in another?

salmonchild

salmonchild

Member
Hi everyone, have been soaking up the excel awesomeness on this site for a few weeks now but am stumped on something that I can't find a pre-existing answer for.


Running 2010 on Windows 7


Problem-


I have a workbook with a few sheets, it is used to track the vacancies of my client and their progression through to offer stage. The master data sheet contains all the info i.e location hours open date, interview date etc.


I have a column for the reworked date (when candidates are interviewed but noone is offered) and I would like it if when that cell is populated that some of the other information is dragged over into the 'Reworked' sheet in the same workbook.


I have tried using the IF function to do this similar to - =IF(MasterA1>0,MasterB1,"N/A") however this requires a similar formula to be put in all of the target cells and I have 4000 rows and growing. There is the additional problem that as reworks are quite rare I then have to filter the secondary data to make it useable.


Solution?


Is there a way to have a single column running a formula, which when a certain reference cell has a value a selection of the associated data in the same row can be put onto the secondary sheet?


Any help would be greatfuly received.


Oli
 
Good day salmonchild


You say "a selection of the associated data in the same row can be put onto the secondary sheet?" what selection? what part of one cell/row do you want pasted into the Reworked sheet, where in the sheet do you want it pasted?
 
Hi, salmonchild!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


I don't fully understand your question but solutions might come by the hand of Excel formulas like VLOOKUP or INDEX or VBA code for the worksheet change event.


Regards!
 
Hi Ninjas, I have indeed run multiple searches, of chandoo.org and the not so awesome rest of the web. I have not found anything that has helped yet but appreciate that I may not be using the correct search terms which is why I posted here.


For better explanation, please simplified version of my data see below


Master data is a little like this:

[pre]
Code: 
A          B          C          D
Stage      Open      Reworked?   Interview date
Live       25/05/13              05/06/13
Live       27/05/13  10/06/13    08/06/13
[/pre]
And what I am trying to do is say- if column C has a value (date) then copy the contents of column A, B and D in the same row to their corresponding cell references on the next sheet in the same workbook.


The workbook is shared which I know is frowned upon, but because of that I am trying to avoid the use of VBA if that is possible. Also I have experience of using VLOOKUP to combine data and to produce elements of a dashboard but am under the impression that similar to using the IF function as I have already tried, I will have to populate all of the cells dwn to about 10000 with the formula which would leave a very bloated workbook.


Hope this extension to the explanation clarifies things a little- let me know if it makes any sense to you!
 
Hi, salmonchild!


Please a few clarifications:

a) Your workbook is shared and opened concurrently?

b) It has a master worksheet where the data is entered and many other worksheets, how are they populated today?

c) How many rows approximately does the main worksheet has?

d) When a date value is entered in column C, how do you identify to which other worksheet the related data should go?

e) Once you have data in other worksheets, what happens if column C in master is updated? and what if cleared?


Regards!
 
if column C has a value (date) then copy the contents of column A, B and D in the same row to their corresponding cell references on the next sheet in the same workbook.

And if column C doesn't have a value? i.e. on that destination sheet, this will leave blank rows where on the source sheet there was nothing in column C?


I will have to populate all of the cells dwn to about 10000 with the formula which would leave a very bloated workbook.

Note that you can array enter a suitable formula across the entire array, meaning the one formula is involved, although still a fairly resource intensive formula.


Uploading a blank workbook sure would help, because one we see your intent we may in fact make another suggestion regarding your approach entirely.
 
Thank you for the responses, I will look into array functions and see if I can work something out.


Will return with an example workbook if I can't work it out, watch this space...
 
Right, I am struggling. If anyone could take a look at the example book and make any suggestions that would be fantastic.


The five example lines on the retail master are user inputs and I have included the reworked 1 sheet with the attempt I made to 'drag' the information across.


In terms of scale, this will by the end of year extend to about 6000 rows and the reworked and undelivered % is well below 10%.


https://www.dropbox.com/s/ftznt5xylec6u4o/example%20workbook%20chandooforum.xlsx


Oli
 
So you only want to a) pull things into the 'Rework 1' tab if they have the text 'Reworked' in column B of the Retail Master tab?


Or did you also want to b) pull 'Undelivered' through? Because that's what your file currently shows, and it's not clear whether this is what you want i.e. b) or this is an attempt to do a) that isn't working.


EDIT: Oh, I get it. You want to pull any lines with a date in 'Retail Master' through to the Rework 1 tab. Simple enough. Give me ten minutes.
 
Thinking some more, this would be a good job for the Advanced Filter. And you could automate this with code if you want. Better than some resource-intensive array formula entered over 10,000 rows.


Have a read of this:

http://www.contextures.com/xladvfilter01.html


or google advanced filter.
 
Advanced filtering is a great tool, unfortunately that functionality is not available on a shared workbook. It is not an option for me to change to a non-shared workbook.


Is there some code that could be added which would replicate that functionality?
 
Greetings - I happened upon salmonchild's dilemma while researching how to copy rows that matched criteria to another worksheet *without* code/macros. Was anyone able to resolve this?

I was able to accomplish this same functionality using an Advanced Filter, however the Advanced Filter must be manually re-applied when the source data changes. Without the ability (in my environment) to use code/macros, automating the same results as a manual Advanced Filter has proven to be a real challenge for me.
 
I think that I have it mostly solved; using Luke M.'s Formula Forensics # 003 model,
1. I added a new worksheet "Sheet 2" and added two column titles - "Copied Type" and "Copied Name"
2. "Copied Type" contains formula: =IF(Sheet1!$A:$A=Sheet1!$D$2, Sheet1!$A:$A, "")
3. "Copied Name" contains formula: =IF(Sheet1!$A:$A=Sheet1!$D$2, Sheet1!$B:$B, "")
4. I then copied those downward far enough to accommodate the source data (only 7 rows on sheet 1)

I tested it by changing a Fruit title to Vegetable, and the data on Sheet 2 reflected the change. The only shortcoming are the blank rows that are included when the first piece of the IF is false. Applying a filter to exclude blanks will remedy that, however, it is a manual step where I am shooting for automation.

I will work on making an addition to the formula that will exclude the non-matching/false rows.

(my revised version of Luke M.'s file is attached in .XLSX)
 

Attachments

  • Luke-1a_revised.xlsx
    11.1 KB · Views: 1
Hi, psynce!
Thanks for sharing your solution with the community. Hope that salmonchild comes back to this topic to read your contribution.
Regards!
 
You must log in or register to reply here.
Back
Top