Use formula to name table columns

[sjrpdx]

The workbook is an annual budget prep file that has multiple sheets summarizing to Sheet1. Sheets are identical, except that all sheets, EXCEPT for Sheet1 (the summary) are tables. The row on Sheet1 that "looks" like a header consists of 12 columns containing the date of the end of each month in the budget year. Jan 31 is entered in the first cell and the other 11 are updated via = EOMONTH(cell-to-left, 1)... Resulting in Jan-24 Feb 24 Mar 24, etc.

As I said, the other sheets (which represent departmental budgets that summarize to Sheet1) are identical to Sheet1 EXCEPT they are tables (so I can unpivot the data and have departmental budget for import into Power BI).

Question: Is it possible to enter a formula that will grab the value of each "header cell" from Sheet1 to update the table column header's names in the departmental budget sheets? If the dept sheets weren't tables it would obviously be easy, but when I enter a normal formula (e.g., =Sheet1!C3), a File Explorer window opens entitled "Update Values: Sheet1".

Thanks,
Steve

 

[Sufiyan97]

Hi & Welcome to MrExcel.

Can you post the data along with some manually typed expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[sjrpdx]

Here are three sheets to demo the question...

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
3	Account	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
4	Gen Rev	100,000	15,000
5	Rental Rev	55,000	64,000
6	Misc Rev	4,000	600
Dept2

 

[sjrpdx]

Looks like it only kept the last copied range. Here's Sheet1

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
3	Account	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24
4	Gen Rev	200,000	30,000
5	Rental Rev	110,000	128,000
6	Misc Rev	8,000	1,200
7	Total	318,000	159,200
8
9
10	This sheet summarizes the data in matching cells from Dept1 & Dept2 sheets.
11	It is NOT a table, but the corresponding areas in the other 2 sheets ARE tables (for use in Power BI).
12	I've used formulas on this sheet for the cell values in D3-N3 so I can enter the Jan 31 date (in C3) for the year to be budgeted and the "header-like"cell values update automatically.
13	What I'd like to do is pull the date from C3 and have it automatically create the column names for the headers in the tables on the other two sheets.
14	That way the user doesn't have to recreate the table column names each year.
15
16	Normal cell referencing formulas (like I used on this page) don't work for the table columns.
Summary

Cell Formulas
Range	Formula
D3:N3	D3	=EOMONTH(C3,1)
C4:D6	C4	=SUM(Dept1!C4,Dept2!C4)
C7:D7	C7	=SUM(C4:C6)

 

[sjrpdx]

And the sheet for Dept1

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
4	Gen Rev	100,000	15,000
5	Rental Rev	55,000	64,000
6	Misc Rev	4,000	600
Dept1