I need help with this challenging problem

%3CLINGO-SUB%20id%3D%22lingo-sub-2153247%22%20slang%3D%22de-DE%22%3EI%20need%20help%20with%20this%20challenging%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153247%22%20slang%3D%22de-DE%22%3E%3CP%3EDear%20Excel%20Community.%20I%20want%20to%20do%20myself%20a%20trainsplan.%20Therefore%20I%20created%20%22templates%22%20which%20are%20all%20tables%2C%20where%20the%20last%20column%20is%20a%20product%20of%20the%20previous%20ones.%20Since%20I%20am%20never%20training%20the%20same%2C%20my%20idea%20was%20to%20create%20those%20templates%20and%20then%20pasting%20them%20with%20a%20shortcut%20on%20a%20new%20sheet%2C%20so%20I%20kinda%20can%20build%20my%20own%20plan%20out%20of%20modular%20blocks%20but%20I%20want.%20the%20problem%20is%20that%20I%20can't%20paste%20any%20tables%20without%20the%20headline%20being%20lost.%20in%20addition%20to%20that%20if%20I%20type%20for%20example%20%3Dtable1%20all%20the%20lines%20building%20the%20table%20dissapaer%20and%20if%20I%20want%20to%20track%20my%20progress%20by%20changing%20the%20reps%20the%20whole%20copied%20table%20dissapears.%20I%20would%20kindly%20appreciate%20any%20help.%3C%2FP%3E%3CP%3Ethank%20you%20in%20advance%2C%3C%2FP%3E%3CP%3EMark%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bildschirmfoto%202021-02-21%20um%2012.28.34.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256380i65363B01C7BBEE11%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Bildschirmfoto%202021-02-21%20um%2012.28.34.png%22%20alt%3D%22Screenshot%202021-02-21%20at%2012.28.34.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2153247%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153335%22%20slang%3D%22de-DE%22%3ESubject%3A%20I%20need%20help%20with%20this%20challenging%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153335%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973661%22%20target%3D%22_blank%22%3E%40markiraiser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20explain%20your%20problem%20in%20detail.%20So%20you%20can%20get%20a%20solution%20proposal%20to%20your%20problem%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20understand%20the%20subject.%3C%2FP%3E%3CP%3EA%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153360%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%20with%20this%20challenging%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153360%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973661%22%20target%3D%22_blank%22%3E%40markiraiser%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20%22Format%20as%20Table%22%20and%20referencing%20the%20defined%20name%2C%20use%20Name%20Manager%20to%20define%20the%20table%20name%20and%20the%20range%20of%20cells%2C%20which%20means%20you%20can%20include%20the%20header%20range%20of%20cells.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22shieldwulf_0-1613912034478.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256385i0233B3387F235765%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22shieldwulf_0-1613912034478.png%22%20alt%3D%22shieldwulf_0-1613912034478.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22shieldwulf_1-1613912122184.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256386i84C027503CEDE77B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22shieldwulf_1-1613912122184.png%22%20alt%3D%22shieldwulf_1-1613912122184.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22shieldwulf_2-1613912298220.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256387iF7F71A43DA98E883%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22shieldwulf_2-1613912298220.png%22%20alt%3D%22shieldwulf_2-1613912298220.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20will%20at%20least%20include%20the%20header%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153365%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20help%20with%20this%20challenging%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973697%22%20target%3D%22_blank%22%3E%40shieldwulf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EHere's%20another%20method%20if%20you%20insist%20to%20retain%20the%20template%20table%20formatted%20as%20a%20table.%3CBR%20%2F%3EUse%20Name%20Manager%20to%20create%20another%20name%2C%20below%20example%20is%20%22TableHeader%22%20with%20reference%20to%20the%20original%20table%20using%20hash%20%23%20to%20set%20the%20all%20range%20of%20cells.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22shieldwulf_0-1613912820443.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256388i5EBCEFFB377CAA96%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22shieldwulf_0-1613912820443.png%22%20alt%3D%22shieldwulf_0-1613912820443.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20will%20also%20include%20the%20header%20range%20of%20cells.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22shieldwulf_1-1613912968319.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256389iC76A1566608CAFBF%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22shieldwulf_1-1613912968319.png%22%20alt%3D%22shieldwulf_1-1613912968319.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153389%22%20slang%3D%22de-DE%22%3ERe%3A%20I%20need%20help%20with%20this%20challenging%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153389%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973697%22%20target%3D%22_blank%22%3E%40shieldwulf%3C%2FA%3E%20thank%20you%20for%20your%20answer.%20in%20order%20to%20track%20my%20progress%20I%20want%20to%20improve%20the%20repetitions%20or%20the%20weight.%20if%20I%20do%20that%20in%20the%20copied%20table%20as%20u%20suggested%208for%20example%20changing%2010%20to%2015)%20the%20whole%20table%20disappears.%20do%20you%20have%20any%20idea%20how%20to%20solve%20this%20issue%20too%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear Excel Community. I want to do myself a trainsplan. therefore I created "templates" which are all tables, where the last column is a product of the previous ones. since I am never training the same, my idea was to create those templates and then pasting them with a shortcut on a new sheet, so I kinda can build my own plan out of modular blocks however I want. the problem is that I can not paste any tables without the headline being lost. in addition to that if I type for example =table1 all the lines building the table dissapaer and if I want to track my progress by changing the reps the whole copied table dissapears. I would kindly appreciate any help.

thank you in advance, 

markBildschirmfoto 2021-02-21 um 12.28.34.png

8 Replies

@markiraiser 

With your permission, if I can recommend you, explain your problem in detail. So you can get a solution proposal to your problem much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

@markiraiser 

Instead of "Format as Table" and referencing the defined name, use Name Manager to define the table name and the range of cells, which means you can include the header range of cells.

shieldwulf_0-1613912034478.png

shieldwulf_1-1613912122184.png

 

shieldwulf_2-1613912298220.png

This will at least include the header range.

@shieldwulf 


Here's another method if you insist to retain the template table formatted as a table.
Use Name Manager to create another name, below example is "TableHeader" with reference to the original table using hash # to set the all range of cells.

shieldwulf_0-1613912820443.png

This will also include the header range of cells.

shieldwulf_1-1613912968319.png

 

 

@shieldwulf thank you for your answer. in order to track my progress I want to  improve the repetitions or the weight. if I do that in the copied table as u suggested 8for example changing 10 to 15) the whole table disappears. do you have any idea how to solve this issue too? 

@NikolinoDE no need to ask, you are absolutely right, I will try better next time. the problem I have is that I want the created tables (seen below) to be pasted via =name (so I don't always have to go back to sheet1 poppy the thing and paste it). however the problem I encounter is, that if I do it by =name, the headline and all the colours disappear. in addition to that if I try to change from 10kg to 15kg (in the pasted =name table) for example the whole table disappears

@markiraiser 

I missed a bit what is the goal of this exercise. If we use =range (range could be data part of the table as =Table1, or with headers =Table1[#All], whatever),  we only create reference on source data, nothing more. You do not create new table.

 

More simple case - we have any formatted value (text, number, etc) in cell A1. In B1 let use =A1. What we have now in B1 is value from A1 without it's formatting. =Table1[#All] works exactly the same way, the only you have as result are referenced values from Table1.

@Sergei Baklan Dear Sergei, thank you for your answer. I did not know that the =table1 only creates a reference so thanks for the clarification. My goal is to have a dynamic traingsplan. therefore I created the "templates" (as seen in the first picture down below, the numbers here are random and are kind of my weight atm) which I want to string together based on how I am training that day (on picture 2 you can see that I don't do the same traing on day 3 as on day 1 and 2). in order to make my life easier I created those templates on page 1 which I want to be able to paste via a short cut (ideally =squad) onto page 2 or another spread sheet thus creating the traingsplan the day I train, but I am not able to figure out how.

The goal of this is to 

1. archive my traing

2. track progress (with the last row)Bildschirmfoto 2021-02-21 um 12.28.34.pngBildschirmfoto 2021-02-21 um 19.22.12.png

@markiraiser 

Unfortunately I don't know how to do that such way, if only to create with VBA function which takes table name as parameter and copy/paste it into another place.