Need an easy way to copy cells with formulas and not have the formula change when pasted

I am working on a spreadsheet to create 3-wide strips of paper that populates the fields based on pulling data from a "Data" worksheet.  Basically, on the data tab, a patient name will be entered on each line and the proper values filled in by the user.  The result on the "Tray Cards" worksheet will be a section for Breakfast, Lunch and Dinner for each patient.  There will be up to 25 patients to manage on this spreadsheet so I am looking for an easy way to copy what is on the "Tray Cards" worksheet so that the final result will be up to 25 sets of the 3 wide strips.  That will make sense when you look at the attached spreadsheet.   I need to take what is already in there on the Tray Cards sheet and duplicate it 25 times. I know Excel tries to be smart when pasting cells with formulas and I don't want to have to manually fix all of the formulas that look to the Data worksheet to get values as I paste new rows but if this is the only way to do it, that is fine.

Tray instructions and Feed instructions will be hand typed in each cell as needed.

Also, when printing, if they have only five patients and they hit Print, is there a way to only have it print five pages of the possible 25 with no intervention?  I don't want people to have to only print pages 1-5 because 9/10 people won't do that.

I am not a master of Excel and I am sure there is an easier way to accomplish my objective so I am open to any ideas.
Dietary-Tray-Cards.xlsx
LVL 1
Steve BantzIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
Prefix your cell row and column with $.

For example, in your excel sheet, change the formula for B3 from =Data!C2 to =Data!$C$2 then copy it.
Ejgil HedegaardCommented:
In attached workbook the values are found on the Data sheet using Index/Match.
The row to use is set by counting the number of "NAME:" before that row, in column A.
So the first block starting in row 2, look at row 2 on Data, and the next block, starting in row 19, look at row 3 on Data, etc.
The values in columns E and H are the same as in column B, so the formulas point to column B.

I have inserted 4 blocks.
The last 2 have no data, since there are only 2 rows with data on the Data sheet.
To make the next block, copy the previous, and insert below.
Then insert a manual page break at the end of the block, in the cell below "Feed instructions", and adjust the row hight for Tray and Feed instructions.

To automatically adjust the range to print require VBA (macro), so you must enable macros.
You can find the code in the Workbook module in VBA.
It look for the text "NAME:" in column A, and if there is a name in column B, then the print area is set to that row + 15 (a block is 16 rows).
Dietary-Tray-Cards.xlsm
Rob HensonFinance AnalystCommented:
Just locking the cell reference won't necessarily help.

For populating each tray card you could use the VLOOKUP function. The syntax is:

=VLOOKUP(LookUP Value, LookUp Range, Offset, LookUp Type)

LookUp Value - in your case you could use the Name, although it might be better to have a patient ID for when you have more than one patient with same name
LookUp Range - the data on the Data sheet, if using Name as the lookup value this would be columns B to L and as many rows as populated. This can be a named range or a Table to allow for the number of rows changing. The lookup value must be in the leftmost column of the defined range.
Offset - the number of the column in the lookup range from which you want the data, for example if you wanted to lookup the Room/Bed the offset would be 3 as it is the third column in the range (Name being column 1).
LookUp Type - there are two options TRUE or FALSE. True will look for a close match with constraints on sort order of the lookup range. False will look for an exact match with no constraints on sort order.

Initial set-up would require use of the $ to lock the reference to the lookup value cell (Name) so that the lookup formula can be copied down the tray card but once the card is completed the $ can be removed from each of the lookup value references so that when copied as a block all of the formulas then reference the new Name in the block.

Alternatively, depending on how much user input you want; just have one diet card set. Enter a name, print that set; change the name, print that set.

if you are happy with going down the VBA route, rather than adjusting the print area, keep the set area and set the routine to automatically populate the Name, print the card set and repeat as many times as required for names on the Data sheet, ie what I suggest in the above paragraph but automated rather than manually entering each name.
Rob HensonFinance AnalystCommented:
Further to above, if the headers on the Tray Cards match the column headers on the Data sheet, the Offset value for the lookup can be formula driven rather than hard-coded. This means that you have the exact same formula in each of the info boxes.

The following can be copied for all of the 30 (3 lots of 10) info cells:

=VLOOKUP($B$2,Data!$B$1:$L$26,MATCH($A3,Data!$B$1:$L$1,0),FALSE)

With the exception of the meal date, the header doesn't match the Data sheet for all 3 cards so would be:
=VLOOKUP($B$2,Data!$B$1:$L$26,7,FALSE)

Some of the other headers don't match exactly but wouldn't take much to adjust, particularly because of the : following the header on the card and not on the data header column.

See attached with the VLOOKUP formulas populated.

if you want to use this and go down the copy and paste for as many cards as required, changing the Lookup value is easy to do.

Select the range B3 to H15 and use the Edit and Replace dialogue to do the following
Search for:           VLOOKUP($B$2
Replace with:       VLOOKUP(B2
Click Replace All

That will replace the absolute references to $B$2 (the Name) with dynamic reference to B2 which when copied and pasted will adjust.
Dietary-Tray-Cards.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve BantzIT ManagerAuthor Commented:
Thanks a lot to everyone.  I don't use Excel for many advanced spreadsheets and this was a very helpful learning experience.  I would had had no idea where to start
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.