Transpose then stack columns

First question of many to come I'm sure.

I'm not sure if this is possible with just equations/formulas or if it enters the realm of macros/VBA so I'm putting it in general.

short of it: I have a table of data (rows are days, columns are rooms, data is usage of the room on that day) that I need to transpose then stack one above the other so they're all in a column. The goal is to have room and day in column A, usage in column B. Transposing via copy/paste transpose is easy but I'd like to get it to where I can add a line of data (a day from the computer logger) and have it all update, which is where I'm stuck.

The data's usable by itself for comparison to itself, but it does need to be in one big column to add to another table of data from another logger that records various types of usage. (ultimately, I'm comparing the data from the two loggers). I don't want to manually build a table of references because the tables in question are going to end up several hundred rows and 70+columns.

Brief, totally made up example: trnsps and stack.xlsx

Hi and welcome to the forum

I have a feeling this is way too simplistic If you already have the data in G, then you couls use this, copied down...
=INDEX($A$3:$E$7,MATCH(RIGHT(G21,4),$A$3:$A$7,0),MATCH(LEFT(G21,3),$A$3:$E$3,0))

Tentatively, yes. looks like it works in the sample. now to get it to work across pages in the actual sheet. Will let you know.

Works in the sample fine.
Got it to work in the first cell. (tried putting another value in the cell it looks at and it works)
Try to fill it down and no dice. Dollar signs are present to hold cell addresses everywhere but what the match formulas are looking for. The cells give "#N/A" and evaluating the formula says the match() formulas are giving the error.

I actually have the date and time separate, the match() formulas are looking at the cells they're in rather than figuring out the left and right stuff. I got this working in the sample but not the actual.

I wouldn't think it matters but the sheet I'm working on is looking for the data on another sheet in the same workbook. (yes, sheet references are present)

Edit: using the function wizard, the match(...) for the row is giving the #N/A. putting the whole match(...) in a row() formula isn't helping. I also tried having it target the room and day before and it works. I dunno.

Edit 2: ah HA, figured it out. the day was displayed as 2 digit day of the month but only the first row in the bigger table had the day() formula used so only it matched up with where I used the day() formula on the logger data page. (for whatever reason, the rest of the column was still displayed as 2 digits but the formula wasn't there)

THANKS A BUNCH for a kick in the right direction. OK more like giving me the answer More to come tomorrow I'm sure.

Glad you were ablke to get it to work for you, and thanks for the feedback

It always looks so simple and obvious in hindsight lol