Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-27-2016, 12:21 PM
friesjh friesjh is offline Linking Range of Cells in a Row Windows 10 Linking Range of Cells in a Row Office 2013
Novice
Linking Range of Cells in a Row
 
Join Date: Jun 2016
Posts: 2
friesjh is on a distinguished road
Default Linking Range of Cells in a Row


I have two spreadsheets in a workbook. One is "Master Sheet", the other is "Short Sheet". Like it implies the master sheet has all the pertinent information . The short sheet has information I need at a glance. As I populate the master sheet I would like it to automatically populate the short sheet. The problem being, I want cells A-L in every fifth row to be linked to the short sheet from the master sheet. I had it setup to link just those columns to the short sheet, but that is way too much information. That included the 4 rows of information I really don't want on my short sheet. I tried setting up the first few rows by hand, then dragging down to continue the formulas to following cells, but Excel did not recognize the pattern of every 5th row. Any suggestions?
Reply With Quote
  #2  
Old 06-27-2016, 11:11 PM
xor xor is offline Linking Range of Cells in a Row Windows 10 Linking Range of Cells in a Row Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

I am not quite sure if I have understood you correct but take a look at the attached file. Sheet Short1 or Short2 may be what you want.

Please note that I have named Master!A3:L30 d
Attached Files
File Type: xlsx EveryFifth.xlsx (20.4 KB, 14 views)
Reply With Quote
  #3  
Old 06-27-2016, 11:23 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Linking Range of Cells in a Row Windows 7 64bit Linking Range of Cells in a Row Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Hi
if by every fifth row you mean rows 5,10, 15 etc.. and starting in A1 on your " Short sheet" try =IF('Master Sheet'!A$1,INDEX('Master Sheet'!A$1:A$100,ROW()*5,0),"")
Drag right to col L then down as needed

If the cols have headers a slight adjustment will be needed

You can also use INDIRECT, but I do not recommend it, as it is a volatile function
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 06-28-2016, 08:54 AM
friesjh friesjh is offline Linking Range of Cells in a Row Windows 10 Linking Range of Cells in a Row Office 2013
Novice
Linking Range of Cells in a Row
 
Join Date: Jun 2016
Posts: 2
friesjh is on a distinguished road
Default

Does not self populate the short sheet when I enter data on the master sheet. I entered data in row 33 of the master sheet. It should automatically populate the cells on the short sheet. Like when you link cells. Thanks for trying!
Reply With Quote
  #5  
Old 06-28-2016, 09:20 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Linking Range of Cells in a Row Windows 7 64bit Linking Range of Cells in a Row Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Please post a sample sheet
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 06-28-2016, 10:22 AM
xor xor is offline Linking Range of Cells in a Row Windows 10 Linking Range of Cells in a Row Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Not sure if your message 05:54 PM is to me, but if it is I would just say:

Open Name Manager, in the "Refers to" field change =Master!$A$3:$L$30 to so big an area as needed, for example:

=Master!$A$3:$L$1000
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Range of Cells in a Row Format a range of cells Dave T Excel Programming 1 06-01-2016 08:58 PM
Linking Range of Cells in a Row Linking cells: can sub-cells move with their master-cell when sorting? kw01 Excel 1 06-30-2015 05:02 PM
Linking Range of Cells in a Row Linking cells that also contain text jhamblin Excel 1 02-20-2015 07:43 AM
Copying text range of cells to different cells adds an extra line jpb103 Word VBA 2 07-23-2014 12:22 PM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft