VLOOKUP referencing the 3rd sheet in the workbook

I have looked for days and have not been able to find my solution.

I am attempting to write a VLOOKUP that references a material on the current sheet (A2) and compares that to the table on the 3rd sheet. The 3rd sheet's name will change daily so I cant use a sheet name. Please help or let me know if you need any additional info. Below is the formula...that doesn't work. It tries to save sheet 3 as its own workbook.

=VLOOKUP($A2, 'SHEET(3)'!A:G, 6, FALSE)

If the table is a structured table, then you can refer to it directly using table name.
Like
=VLOOKUP($A2, Table1, 6, FALSE)

No files attached. Please see yellow banner at the top of the page for instructions on how to attach a file.

Yea, noticed after I posted.... file has been attached now.

If you convert the data into a table you can use the formula in post#3

Would the table number not change each time a table is created? How would I reference the most recent table?

Do you always want to lookup values on the 12-3 sheet regardless of name?

No. On Monday, a new tab will be created (12-07) and I will then want to reference tab 12-4. Attached is a preview of what the sheet will look like on Monday.

I am controlling this all with VBA and I can include my script if that will help determine the overall actions taking place and what I would like to accomplish.

Sorry but I don't understand how adding a new sheet affects the formulae in existing sheets.

I am using a macro to enter the formula into the new sheet. If I have a defined sheet in the VLOOKUP, it will not pull accurate data.

Summary of the macro is to pull a list of materials and import it into my workbook (RAW tab). From there I create a new tab with today's date as the 2nd sheet in the workbook and paste the relative information into the new tab. I then am using the VLOPOKUP to look at the previous day's information to see if an item is repeated from the previous day and what the current status of that item is. If it does not find it on the previous tab, it leaves it blank and I know it is a new action item. Because the status changes in those cells, there is user input that will overwrite the formula on the current day. I guess, my core issue, is getting a VLOOKUP to work in the macro to always look at the 3rd sheet so I do not need to manually enter the formula each day.

Sorry if I explained it poorly. Also, please let me know if this belongs in a different part of the forum. I do greatly appreciate your help through this.

This should really have been posted in the VBA/Macros section, also you should have mentioned that you were trying to insert the formula via code.
Can you post the code you are using, when you do so please ensure that you use code tags.

Please Login or Register  to view this content.

I tried to remove the formatting, but may have missed some. Please let me know if I should start a new thread in the VBA/Macro forum. Otherwise, thank you for any help you can provide.

Thanks for that, how about

Please Login or Register  to view this content.

Good Day Fluff. This worked like a champ. I greatly appreciate your help on this matter.

Quick follow-up if you don't mind. I am copying the same formula into column G. The difference is if it is an error (#N/A) I would like to replace that with the word NEW. I tried to put it between the VLOOKUPS, but that added it to blank spots.
Do you happen to have a quick fix on where to add the text I want added to replace the errors?

Try

Please Login or Register  to view this content.

Thank you very much Fluff. Your help is very appreciated.

You're welcome & thanks for the feedback.