Formla: change sheets not cells

[ThinkTankTop]

I have a workbook that has many sheets labeled by the date at such "7-1-19".

Each day has a set of cells J3, K3, L3 with values that I need to graph per day. I'd like place them in columns with the date.
For example
7-1-19 J3 K3 L3
7-2-19 J3 K3 L3
7-3-19 J3 K3 L3 ...and so on.

I'd like to have them all in one sheet so I can graph the data and easily reference the table.

I found this post, but I could not get the formula to work for me:
https://www.mrexcel.com/forum/excel...l-reference-when-dragging-down-formula-2.html

Thanks in advance. I appreciate your help.

 

[Toadstool]

Hi ThinkTankTop,

I'd just go INDIRECTly to the cells based on a list of dates.

My test data just has 3 tabs for 1st, 2nd and 3rd July 2019

	B	C	D	E

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Sheets[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]7-1-19[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]72[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]7-2-19[/TD]
[TD="align: center"]90[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]108[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]7-3-19[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]132[/TD]
[TD="align: center"]144[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]7-4-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]7-5-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IFERROR(INDIRECT("'"&TEXT($B2,"m-d-yy")&"'!"&C$1&"3"),"")[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=IFERROR(INDIRECT("'"&TEXT($B2,"m-d-yy")&"'!"&D$1&"3"),"")[/TD]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=IFERROR(INDIRECT("'"&TEXT($B2,"m-d-yy")&"'!"&E$1&"3"),"")[/TD]
[/TR]
[TR]
[TH]B3
[/TH]
[TD="align: left"]=B2+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ThinkTankTop]

Sorry for the late reply. I appreciate you taking the time to assist. I'm not quite understanding how the "indirect" formula works, so there is some definite user error here. I've tried it, but I can't get the correct cells to reference.

I managed to come up with the following so far: ='7-1-19!J3

I repeated that formula for the next two columns referencing K3 and L3 which returns the correct values from those sheets. However, I cannot just drag this formula down rows. the next sheets are "7-2-19", "7-3-19", etc.

Is there a way to have excel change the sheet reference down rows quickly? Alternatively, I could just be having trouble with your formula.

Thanks again!

 

[ThinkTankTop]

Well, I Got it working! I was just pasting in the wrong cell. Wow, Thanks This is fantastic!

 

[Toadstool]

The TEXT statement

="'"&TEXT($B2,"m-d-yy")&"'!"&F$1&"3"

is putting a single quote followed by the column B date, then an exclamation mark and a closing single quote, then the contents of F1 (the letter J), then the text "3". That gives us the cell reference for sheet 7-1-19 and cell J3.

I can copy across because F is relative but 1 is absolute for the F$1 so it works for G1 and H1.
I can copy that row 2 set of formulae down because $B is absolute but the 2 in $B2 is relative so it will pick up the next date in B3.

If I take away the IFERROR (there in case there's no sheet named that date) and the INDIRECT I end up with just that TEXT statement which gives me:

[TABLE="class: grid, width: 329"]
<tbody>[TR]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]'7-1-19'!J3[/TD]
[TD]'7-1-19'!K3[/TD]
[TD]'7-1-19'!L3[/TD]
[/TR]
[TR]
[TD]'7-2-19'!J3[/TD]
[TD]'7-2-19'!K3[/TD]
[TD]'7-2-19'!L3[/TD]
[/TR]
[TR]
[TD]'7-3-19'!J3[/TD]
[TD]'7-3-19'!K3[/TD]
[TD]'7-3-19'!L3[/TD]
[/TR]
[TR]
[TD]'7-4-19'!J3[/TD]
[TD]'7-4-19'!K3[/TD]
[TD]'7-4-19'!L3[/TD]
[/TR]
</tbody>[/TABLE]

If your dates are sequential for the sheet names then Excel holds date as number of days since 1st January 1900 (or 1904 for Apple) so I can type my first date in B2 then in B3 put =B2+1 to get the next day. Copy that down as far as needed. The IFERROR will gives null results if no such sheet is found.

The INDIRECT is taking the result we get from the TEXT, such as '7-1-19'!J3 and saying this is a reference to the cell we want so go to sheet '7-1-19'! and retrieve the contents of J3 to pull the contents back into the cell with the INDIRECT statement.

Clearer or muddier?

 

[Toadstool]

Well, I Got it working! I was just pasting in the wrong cell. Wow, Thanks This is fantastic!

Ah! I was too slow with my response.

Enjoy!

 

[ThinkTankTop]

This really helps, Thanks! I was trying to understand how it works.