Formula Help

Allison Bishop

I have a sheet with three columns (see image). I need the Project Name column and Call Meeting Date column to auto-populate with data from another sheet, using the Row ID as the matching information. I know that I can link cells across two different sheets, but it's time consuming since they must be linked one by one. Does anyone know of a formula that will work? I believe that VLOOKUP should work, but I have not been able to come up with the correct formula. I am not trying to summarize any data, I am simply need to link the data from one sheet to the other. Thank you for your help.

her sheet (without having to link the information one-by-one).

Paul Newcome

As long as the Row IDs match on both sheets, try this...

=INDEX({Other Sheet Project Name Column}, VALUE([Row ID]@row))

Is there a reason you are pulling the data this way instead of using a report?

Kirt Tassmer

If I understand correctly what you are looking for. This is the formula I used to get the information to come over from the other sheet. My other sheet is "New Sheet2" and my project name was in column 4.

=VLOOKUP([Row ID]1, {New Sheet2 Range 2}, 4, false)

I did notice I was getting an error message on pulling a date over with VLOOKUP when the cell the formula was in was not formatted as a date. Check that as well if you're still having problems.

Allison Bishop

Thank you. @Paul Newcome I am trying your INDEX formula first. Looks like I am getting closer to what I need. Yet, it's not capturing the correct Project Name from the matching ID number. Instead, it is pulling the Project Name that is located on a specific row. I have attached a screen shot. Also, I changed the name of the ID column from Row ID to Project Request Number. The project request number is an autogenerated number.

The reason I am not creating a report because I can't see the report in Calendar view, and publish the calendar. The sheet I am pulling from has many date fields which need to be separated out because they only apply to specific people in our office.

Thank you very much for your help. It's greatly appreciated.

Formula Help.pdf

Jen Lange

@Allison Bishop , you should be able to use a VLOOKUP formula. I use it successfully from one sheet to another to look up a category attribute for a task. Based on the above and your screen shares, I think the following maybe be close. Adjust the references if/as needed.

=IFERROR(VLOOKUP([Job Request Number]@row, {Project Name Reference}, 4, false), "try again")

Paul Newcome

I was under the impression that your Row ID matched the actual row number on both sheets. My apologies. Try this...

=INDEX({Other Sheet Project Name Column}, MATCH([Project Request Number]@row, {Other Sheet Project Request Number Column}, 0))

Allison Bishop

@Paul Newcome , great thank you. Yes, I see how it was read that way. Both the your formulas are very useful. Thank you for your help.

Paul Newcome

As long as the Row IDs match on both sheets, try this...

=INDEX({Other Sheet Project Name Column}, VALUE([Row ID]@row))

Is there a reason you are pulling the data this way instead of using a report?

Kirt Tassmer

If I understand correctly what you are looking for. This is the formula I used to get the information to come over from the other sheet. My other sheet is "New Sheet2" and my project name was in column 4.

=VLOOKUP([Row ID]1, {New Sheet2 Range 2}, 4, false)

I did notice I was getting an error message on pulling a date over with VLOOKUP when the cell the formula was in was not formatted as a date. Check that as well if you're still having problems.

Allison Bishop

Thank you. @Paul Newcome I am trying your INDEX formula first. Looks like I am getting closer to what I need. Yet, it's not capturing the correct Project Name from the matching ID number. Instead, it is pulling the Project Name that is located on a specific row. I have attached a screen shot. Also, I changed the name of the ID column from Row ID to Project Request Number. The project request number is an autogenerated number.

The reason I am not creating a report because I can't see the report in Calendar view, and publish the calendar. The sheet I am pulling from has many date fields which need to be separated out because they only apply to specific people in our office.

Thank you very much for your help. It's greatly appreciated.

Formula Help.pdf

Jen Lange

@Allison Bishop , you should be able to use a VLOOKUP formula. I use it successfully from one sheet to another to look up a category attribute for a task. Based on the above and your screen shares, I think the following maybe be close. Adjust the references if/as needed.

=IFERROR(VLOOKUP([Job Request Number]@row, {Project Name Reference}, 4, false), "try again")

Paul Newcome

I was under the impression that your Row ID matched the actual row number on both sheets. My apologies. Try this...

=INDEX({Other Sheet Project Name Column}, MATCH([Project Request Number]@row, {Other Sheet Project Request Number Column}, 0))

Allison Bishop

@Paul Newcome , great thank you. Yes, I see how it was read that way. Both the your formulas are very useful. Thank you for your help.

Paul Newcome

Happy to help. 👍️

Nick Embry

@Paul Newcome Hey Paul, I have been scouring these posts looking for an answer for a formula. I am trying to calculate how many times a criteria appears by week.

Basically, I want to know weekly how many times the initial status was Category change and submitted to the clinician in a week. I need to track this data in a dashboard weekly, going back to 2/19/2020.

I hope you can help me!

Nick

Paul Newcome

@Nick Embry The exact formula is going to depend on a few things. How are you going to have this data laid out? Will it be on a separate sheet?

Nick Embry

@Paul Newcome Yes it will go to a separate sheet. It's called "MRO SHEET."

Paul Newcome

Are you able to provide a screenshot of how you anticipate this data to be laid out?

Are all of the dates in the Submitted column going to be the same day of the week, or can the day of the week vary? Will you have a column in the metrics sheet that has date values in it? If so, will they always be the same day of the week?

Nick Embry

@Paul Newcome I am looking for the number of category change that happened within the week, by week number is fine. Something like below is how I will display it on a Dashboard. With the instead of months would be week number.