match specific cell if another cell in same row is true, then paste in other worksheet

[Templemind]

If one cell is true in a certain range in sheet1, then I want the cell value in the 2 column in the same row to be copied over to sheet6 in the 1 column.

I believe I should use an if then statement, such as

Please Login or Register  to view this content.

I also want to insert the date beside the pasted name on Sheet6 in Column B, but I am not sure how to do that either.

[walruseggman]

You have some of the right ideas, and some of the right code, but you can't do statements like

Please Login or Register  to view this content.

Because you can't evaluate the value of a range, you need to instead evaluate the value of every cell in that range, with a For loop.

There's some other things that are syntactically wrong, and others that are logically wrong, but I'm going to assume that was more pseudo code than it was you trying to get that code to run.

Also, in inserting the date, you can change the date format to whatever you please.

Please Login or Register  to view this content.

[Templemind]

I tried it, but it does not seem to be copying to the other sheet. Everything looks to check out and there are no errors, it just is not showing up as anything

[walruseggman]

Post the workbook or a stripped down version of it

[Templemind]

Here it is. It's not stripped down any. It is a bit complicated. I want this formula to be done when I pressed the New Day button, which is coded on Sheet1

[walruseggman]

It works exactly as expected. I can post screenshots if you want.

My guess as to why it's not working for you:
In your originally posted code, you were looking for True in G6:G38. This was carried over into my code. This means that your column G values literally need to say TRUE in them if you want that guest carried over to the Overnight Checklist sheet. But the label on that column is "O/N". While I don't know what that stands for, I'm guessing each guest gets one of those two letters in that column, and not a TRUE/FALSE value.

So let's say you only want guests copied over to the Overnight sheet if they have an "O" in column G. Then simply replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Again this is all guessing on my part so you may need to tweak, but like I said, as the code stands now if you have TRUE values in column G then it works as expected.

[walruseggman]

It works exactly as expected. I can post screenshots if you want.

My guess as to why it's not working for you:
In your originally posted code, you were looking for True in G6:G38. This was carried over into my code. This means that your column G values literally need to say TRUE in them if you want that guest carried over to the Overnight Checklist sheet. But the label on that column is "O/N". While I don't know what that stands for, I'm guessing each guest gets one of those two letters in that column, and not a TRUE/FALSE value.

So let's say you only want guests copied over to the Overnight sheet if they have an "O" in column G. Then simply replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Again this is all guessing on my part so you may need to tweak, but like I said, as the code stands now if you have TRUE values in column G then it works as expected.

[Templemind]

Hello Walrus,

Sorry for the late reply. It works perfectly. It seemed like it should, but I forgot that I should change true after the equal sign. I need to make additions to it, but I believe I know how to do that. Thank you.

[Templemind]

Another related question, but I thought True in the if/then statement just checks to see if the cell is not empty and anything within quotations are a string or numbers that it specifically looks for. That was probably reason why I wasn't able to make changes suit my needs. So what would I have to change to make it so that it looks to see if the value is true as in not empty; such that it does not matter what is in the cell, as long as it it not empty.

[Templemind]

I was curious if you could help me with this. It is the same formula and I forgot to ask, and this is the part I had to add to, but I want to be able to match a person's name in ws1 column 2 range 6-38 to a name in ws2 column 1 so I do not have duplicates. Here is the code I have:

Please Login or Register  to view this content.

I might as well tell you everything I am planning on doing with that formula. I have 5 dates added to ws2 after a particular name (the O/N is overnights, so guests can stay only for 5 total nights in a three month period, which is shown on ws2 and what the dates show) and I have a formula placed in the 7 column (G) which shows 3 months after the initial date in Column 2. What I then want is that name and the date in column 7 to be copied over to ws1 in column O and P, with the name first, when column 6 is has anything in it on ws2.

What I will do on my own, as I think I have it, is to clear the rows of ws2 when the date of column 7 is older than the current date.

[walruseggman]

For checking if a cell has a value in it, i.e. that it is anything other than blank:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

And I'm sure there are 3 or 4 other ways to do it.

[walruseggman]

What I then want is that name and the date in column 7 to be copied over to ws1 in column O and P, with the name first, when column 6 is has anything in it on ws2.

---

Put this in Your Overnight Checklist sheet.

Please Login or Register  to view this content.

I think this is all you were asking help on, but if not let me know.

[Templemind]

Thank you for that. That works perfectly. Thank you for everything.