## Forum Discussion

# Macro that concatenates two cells, references a Date and then copy pastes value into first

thanks mathetes

the main reason for not using pivot tables here is because it wouldn't match the aesthetics i laid out.

however, it doesn't looks like i can accomplish what i'm trying for without using them in place of (at least to my knowledge)

i tried writing out what the formulas would do, but i can't get around it

is it possible to have a formula that does this:

if column N from 'Data' sheet equals the same month as today, then concatenate columns N & O from 'Data' sheet

but with that, it would have to do this search through all of column N without picking the same row twice

i think i might be able to manage from there

Deleted

I added a formula to column L of the data tab to concatenate the month/day for today's month as an example of one way I believe what you're asking can be done.

- mathetesJan 23, 2021Silver Contributor
I took the formula that JMB17 had written and modified it so that the result of the formula is actually a full date in Excel's date format. As you'll see, the condition in the first part of the IF function remains the same, but the rest, in my version, no longer uses concatenation; rather it uses the DATE function to take the numbers of day, month, and year, to produce the standard numeric value that the date format then turns into a date. This is then consistent with your dates for holidays, paydays, etc.

Here's the new formula, as it appears in cell L2.

**=IF(OR(N2=MONTH(TODAY()),N2="xx"),DATE(YEAR(TODAY()),MONTH(TODAY()),O2),"")**I still have to say, however, that it's not clear to me what you're doing with the rest of the workbook. But if you can make this work, go for it.

- DeletedJan 23, 2021
wow, thanks JMB17 . beautiful formula.

thanks as well mathetes. for this column i'll probably end up keeping the format as '00 - 00' but i can use the knowledge from your formula as well.

now, i wanted to see if it was possible to have a formula that would list out all the dates that fall in the same month as Today. i figure from there i should be able to finish up this section with index/match.

i'm trying to toy around with the below formulas, but i'm getting either #Value, #Calc or #Spill errors.

is this possible, or am i spinning my wheels?

=FILTER(Data!P:P,VALUE(LEFT(Data!M:M,2)=MONTH(TODAY())))

=FILTER(Data!P:P,LEFT(Data!M:M,2)=MONTH(TODAY()))

=FILTER(Data!P:P,MONTH(Data!M:M=MONTH(TODAY())))

- mathetesJan 23, 2021Silver Contributor
Deleted

As I've said before, I'm mystified by how you're approaching this, unable to figure out the goal.

I can, nevertheless, tell you that the SPILL error with FILTER means something is blocking the function from delivering all the results, not that there's something wrong with the formula. After trying to delete rows and columns adjacent to your own formula, I took your formula and copied it over to a new sheet--you can see the result here.

Clearly some more refinement is needed to the criteria to be applied in the FILTER function.

- DeletedJan 23, 2021
i've attached an updated file.

in the Data sheet, range )32:V33 is what i'm trying to accomplish. it looks like the formulas work on the Data sheet, but they don't work when i try to transfer over onto the Money sheet.

On the Money sheet, i can't get column L to transfer over. the text doesn't appear even those the formulas should be correct