Update formulas to new sheet at once without Control Center

Peppey

Hi everyone. I am hoping you can help me. I have a large sheet and with the new year coming up I have to recreate the sheet. I have a reporting roll-up sheet with lots of formulas and would like to replace all the old sheet reference names to the new sheet. I have tried Find and Replace but thats not working. I manually tried changing the name but keep getting Invalid Reference. I do not have access to Control Center. Can you anyone assist me on how I can update the formulas to a new sheet? Thanks Brian

Andrée Starå

@Peppey

Try something like this.

=IFERROR(IF(VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false) = "", VLOOKUP([eTracker Number]@row, {CY23 eAPP Station (C) - Background Investi Range 1}, 2, false), VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false)), "")

Did that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

You will have to do them all manually, one at a time, by editing each cross sheet reference.

Another option would be to "save as new" the current year's sheet so that you have the data in the copy and then delete/update the data from the original sheet to be the next years data.

Peppey

Thanks sir.

Paul Newcome

Happy to help. 👍️

Peppey

Sir, I am sorry to post this "duplicate" but you have always been so helpful so I am wondering if you can assist me.

So my journey in taking over a project continues, and lets just say the sheets that were put together is a big challenge. I have two sheets, Sheet 1 is titled "CY23 eAPP Station - Background Investigati ) and Sheet 2 is titled CY23 eAPP Station (C) - Background Investigati". On the third sheet there is a cell that has to look for information in Sheet 1 and Sheet 2 and place the date value from either sheet 1 or sheet 2 in a column thats in Sheet 3.

I am using two Vlookups in one cell to do this, but it is not working. When I used just one Vlookup it returns a value but when trying to combine both VLookups into the cell the data from Sheet 2 is not populating in sheet 3. I am currently using the below formula:

=IFERROR(VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false), VLOOKUP([eTracker Number]@row, {CY23 eAPP Station (C) - Background Investi Range 1}, 2, false))

and have tried this formula as well

=JOIN(VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false), VLOOKUP([eTracker Number]@row, {CY23 eAPP Station (C) - Background Investi Range 1}, 2, false))

I have been working on this problem for about 3 hours and really need to get it fixed quickly. Again accept my apologies for sending this to you, but any help you can provide would be appreciated.

Paul Newcome

Do both VLOOKUPs work individually?

Peppey

Hi sir. First I am so sorry for the delay in responding. Yes sir it appears like both VLOOKUPS are working individually.

Andrée Starå

Hi @Peppey

I hope you're well and safe!

It can get tricky.

Will it always just be one of the sheets that will have information?

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

Are you able to provide some screenshots for context?

Peppey

Yes sir sorry I put in the other post. Below is what I submitted

This screen shot is from the roll-up sheet where I am placing the VLookup formula:

The below is from the sheet titled "CY23 eAPP Station - Background Investigati" 

Finally the one below is the exact same screen shot from sheet titled "CY23 eAPP Station (C) - Background Investigati" because both sheets are the same except different date ranges

Thank you again for all of your assistance - I appreciate it.

Paul Newcome

So you are getting the NO MATCH error when you have the double VLOOKUP in place?

Peppey

Yes sir, but also when there is information in one of the sheets, its not being recognized and not populating. The NO MATCH error can occur because some of the actions havent been completed and there would be no date in the Action Completed column. In a nutshell, if the eTracker number is on the CY23 Background sheets and there is a date in the Action Completed column, the date should be populate in the Date Action recevied in the roll-up sheet. Does this help sir?

Paul Newcome

That particular error comes from not finding a match on the unique identifier. If it finds a match on the identifier and the date cell is blank, then the formula would just output a blank.

Have you tried copy/pasting a value from one of the sheets to the rollup sheet to ensure there are no typos? Is the NO MATCH error present in any cell being referenced by the formula?

Peppey

Yes sir I have tried copying and pasting. No sir the NO MATCH error does not appear when there is a value, but what does happen is when there is a value it returns a blank cell when there should be a date. For instance, yesterday John Doe, eTracker Number 333333, had a completion date of 12/07/23. The date should have rolled over but it didn't it was just a blank cell.

Andrée Starå

@Peppey

Try something like this.

=IFERROR(IF(VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false) = "", VLOOKUP([eTracker Number]@row, {CY23 eAPP Station (C) - Background Investi Range 1}, 2, false), VLOOKUP([eTracker Number]@row, {CY23 eAPP Station - Background Investigati Range 1}, 2, false)), "")

Did that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Peppey

Sir you are awesome! You both rock! Thank you so much for this. I truly appreciate it and hope everyone has a wonderful weekend!!