I'm trying to make printable labels from a large data set ("2024 Profiles MASTER") I've been tracking. I've made a separate tab ("Printing") and organized the cells into squares. Within each square, the cells are organized in such a way that displays specific cell data from a single row from 2024 Profiles MASTER.
In my first square, all of the cells referenced are pulling from row 8. I want my next square (moving right), to display data from the next cell in the series, that is in row 9; then the next square row 10, and so on.
I tried putting a "$" in front of the column letter and then filling to the right, but I get the "all merged cells need to be same size" error. As best I can tell they are the same size.
I would like it if I could tell excel to reference the next cell past the cell I just referenced. I know this won't work, but something like this:
If I wanted to reference cell E9 I could type =E8+1
If I wanted to reference cell E11 I could type = E8+3
Is there anyway to do something like that? Alternatively, I'm interested in alternate solutions if anyone has them. The main goal is that I can fill forward data that I'll be able to cut and paste onto another document.
Hi @cribbage
Don't use merged cells - ever!
Unmerge the cells and attach a copy of your file, much easier to help when we have the file.
Regards
Phil
Hi @cribbage
I'm really confused about what you are trying to do.
You want the whole of Row 8 from the 2024 NQ H1 MASTER sheet in cell A1 on the Printing sheet?
And all of Row 9 in cell B1?
With the cell sizes so small on the Printing sheet you won't see all the data?
Please provide a clear example of what end result you want.
Regards
Phil
@philipt Sure, let me try to explain better.
I have charts that are static that I use to backtest data, I want to create labels that I can cut and paste onto the charts (electronically and/or physically). Here's a diagram of how I want data pulled. All data is getting pulled from the "2024...MASTER" tab. I know you said don't use merged cells, so this diagram is just for demonstration purposes, to help you see what I was hoping for. What I'm hoping you can see in the diagram is that as you move right, its pulling from the same column, but just moving down a row.
It doesn't have to look pretty, I'm happy to remove the merged cells. I just want to make sure that I'll be able to cut/paste it easily onto my chart images.
Hi @cribbage
That image doesn't seem to match what you describe or what your initial description was.
If you are taking data from Row 8 of the master sheet then the first cell, A1, in the Printing sheet would contain the date 8/09/2023, the next cell, B1 would contain Monday, then Screamer in C1 etc.
If you want to show the values from an entire row you can reference that row in A1 of the Printing sheet like so
='2024 NQ H1 Profiles MASTER'!A8:AH8
and the values will all be copied over and 'spill' into adjacent cells.
Alternatively, though lots more work, you can fix the row reference but let the column reference increase using the $ like this in A1, then copy it across the row
='2024 NQ H1 Profiles MASTER'!A$8
giving this this in B1
='2024 NQ H1 Profiles MASTER'!B$8
etc
Regards
Phil
The attached file contains a solution that may work for you, though it requires a modern Excel version. I changed column B on the MASTER to display only the first three letters of the weekday. On the printing sheet you'll see two named ranges. One for the start row and one for the layout of the 'label'.
You can't drag the label formula across as it creates a dynamic array. Copy an entire label an paste directly next to it. Then just auto fit the column widths and all labels should have the same dimensions.
Edit: I didn't see Phil's post until after I posted mine.
@Riny @PhilipT - Just wanted to say thank you both - I've been away on a project for a few days and am just getting back to this. Will follow up with any questions.
