Is it possible to query data based on date if each sheet is different date?

[Ronderbecke]

I have a document that has many sheets with the columns being the same across all of them but the data changing slightly on each sheet. Each of the sheets is labeled (named) by date that the data got changed (first sheet is 8/1/2021 and the next one is 9/17/2021 and then 12/13/2021 etc. I don't know that its even possible, but if I am putting in another sheet that needs to query based on the date the product was sold, is it possible to create a function that queries from the correct sheet? Example:

Date of sale	Panel used
8/9/2021	?????

since that date is after 8/1/2021 but before 9/17/2021, I need a function that knows to query the 8/1/2021 sheet. Is that possible?

 

[Gimics]

Hey Ronderbecke - Excel doesn't allow you to have special characters like "/" in your sheet names, so your sheets wouldn't be called "8/1/2021".

Can you let us know what your sheets are actually named?

It sounds like you can use the =INDIRECT() formula to solve your problems, but it will depend on what your actual sheet names are.

 

[Ronderbecke]

Hey Ronderbecke - Excel doesn't allow you to have special characters like "/" in your sheet names, so your sheets wouldn't be called "8/1/2021".

Can you let us know what your sheets are actually named?

It sounds like you can use the =INDIRECT() formula to solve your problems, but it will depend on what your actual sheet names are.

Sorry

August 1 2021
September 17 2021
December 13 2021

 

[Gimics]

Ok great,

So we can use the INDIRECT formula, but we would first need to convert any date that you have in a cell to the same format as your tabs.

The easiest way to do this is just to use the TEXT formula - so if your first date "8/9/2021" is in cell A2, we could use this:

=TEXT(A2,"Mmmm d yyyy")

Which would return "August 9 2021"

Then we combine this with the INDIRECT formula to lookup a value on the other sheet:

=INDIRECT("'"&TEXT(A2,"Mmmm d yyyy")&"'!A1")

Which will return the value of cell "A1" from the tab "August 9 2021" by reformatting your cell references into this format:

=INDIRECT("'August 9 2021'!A1"), which behaves just like a normal link to the cell A1 in sheet "August 9 2021".

For this to pull the data you need, you would just need to replace the cell reference "A1" with the appropriate cell that you're looking for on another tab.

Hope that helps.

 

[Ronderbecke]

Ok great,

So we can use the INDIRECT formula, but we would first need to convert any date that you have in a cell to the same format as your tabs.

The easiest way to do this is just to use the TEXT formula - so if your first date "8/9/2021" is in cell A2, we could use this:

=TEXT(A2,"Mmmm d yyyy")

Which would return "August 9 2021"

Then we combine this with the INDIRECT formula to lookup a value on the other sheet:

=INDIRECT("'"&TEXT(A2,"Mmmm d yyyy")&"'!A1")

Which will return the value of cell "A1" from the tab "August 9 2021" by reformatting your cell references into this format:

=INDIRECT("'August 9 2021'!A1"), which behaves just like a normal link to the cell A1 in sheet "August 9 2021".

For this to pull the data you need, you would just need to replace the cell reference "A1" with the appropriate cell that you're looking for on another tab.

Hope that helps.

Thank you, I knew I was missing something. I really appreciate the help.