If one cell has a value, copy adjacent cell's value and paste to different worksheet
So, for example, I have a list of statements in Column B. Column A is basically a checklist. If someone puts an X in column A (to "select" the statement next to it), I'd like the statement next to it (in B) to be copied to the next worksheet. Can someone advise how to do this?
in the other sheet, enter a formula say in B1 to give a count of "X" marked.
e.g.
=COUNTIF('Sheet1'!$A$1:$A$100,"X")
Adjust sheetname and range to suit.
Then in A2 to extract the values from B.
adjust ranges and sheetname to suit, then confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.=IF(ROWS($A$1:$A1)>$B$1,INDEX('Sheet1'!$B$1:$B$100,SMALL(IF('Sheet1'!$A$1:$A$100="X",ROW('Sheet1'!$A$1:$A$100)-ROW('Sheet1'!$A$1)+1),ROWS($A$1:$A$1))))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
This doesnt exactly answer my question, unless I am misunderstanding the answer.
Let's imagine a scenario. I am so thrilled with discovering this new forum, that I am throwing a party. Imagine the attendee checklist for this party.
TAB ONE: Column A are boxes that a "x" can be marked in. Let's pretend the names of 10 people are in Column B, and we are going to select in Column A (by marking an "x") who is to be invited to our party. Its an excel party, so wildness is guaranteed. ;-)
However, being a woman, I am mad at almost everyone on the list, so just the first 3 names are checked. (I had to include everyone else anyway just to be clear who WASNT invited to my awesome party.) So, now we have a list of 10 names in column B (B1:B10), and a few "x"s (A1:A10 is our range, but only X's in A1:A3).
TAB TWO: On the next worksheet in cell A1, I have typed: "Party-of-Excel-Frenzy Invitees:" I would like the names of the lucky invitees to appear as a list in cell A2 in this second tab.
FIRST MARGARITA OF THE PARTY GOES TO THE FIRST PERSON TO POST THE FORMULA!
DISCLAIMER: No women and/or excel enthusiasts were harmed in the making of this scenario. Please drink responsibly. Booze is not a toy.
I neglected to enter the value-if-true part of the first IF..
Try:
confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down=IF(ROWS($A$1:$A1)>$B$1,"",INDEX(Sheet1!$B$1:$B$100,SMALL(IF(Sheet1!$A$1:$A$100="X",ROW(Sheet1!$A$1:$A$100)-ROW(Sheet1!$A$1)+1),ROWS($A$1:$A1))))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
This formula works well for me! However, in my scenario, I would like all the names, with commas as a separator, in one cell. Example: Mary, Mark, Jessica, Rita (all in A2)
Thanks in advance!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks