Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-19-2016, 08:44 AM
Normie Normie is offline formula to send data to 2nd sheet Windows 7 32bit formula to send data to 2nd sheet Office 2007
Novice
formula to send data to 2nd sheet
 
Join Date: Nov 2015
Posts: 14
Normie is on a distinguished road
Default formula to send data to 2nd sheet


I have an excel file with two sheets Sheet 1 is a dashboard for information Sheet 2 is and data entry page what I want to be able to do is when the data in column H shows NO I want to be able to see A:G of that line on Sheet 1 (grey area on attachment) I have tried an "IF" statement to no success can someone help with the formula Thank you in advance
Attached Files
File Type: xlsx Book 2.xlsx (10.7 KB, 24 views)
Reply With Quote
  #2  
Old 01-19-2016, 08:28 PM
macropod's Avatar
macropod macropod is online now formula to send data to 2nd sheet Windows 7 64bit formula to send data to 2nd sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could use a formula like:
=IF(Sheet2!$H2="NO",Sheet2!A2,"")
in B3 and copied to B3:H9 to conditionally show/hide the data, but a formula can't show/hide rows.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-20-2016, 07:21 AM
Normie Normie is offline formula to send data to 2nd sheet Windows 7 32bit formula to send data to 2nd sheet Office 2007
Novice
formula to send data to 2nd sheet
 
Join Date: Nov 2015
Posts: 14
Normie is on a distinguished road
Default

Success!! what I forgot to mention is that I need the data from H2 to H300
I have tried this =IF(Sheet2!$H2:H300="NO",Sheet2!A2:A300,"") but get a blank
Reply With Quote
  #4  
Old 01-20-2016, 07:38 AM
macropod's Avatar
macropod macropod is online now formula to send data to 2nd sheet Windows 7 64bit formula to send data to 2nd sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

All you needed to do was to copy the formula I gave you - as I gave it - paste it into B3 and copy from there to B3:H300. Why you'd think you needed to change it beats me...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 01-20-2016, 07:45 AM
Normie Normie is offline formula to send data to 2nd sheet Windows 7 32bit formula to send data to 2nd sheet Office 2007
Novice
formula to send data to 2nd sheet
 
Join Date: Nov 2015
Posts: 14
Normie is on a distinguished road
Default

I dont have the room on Sheet one to use the 300 lines just want to pull anything that shows "NO" on Sheet 2 to show in the grey box on Sheet 1 sorry for any confusion
Reply With Quote
  #6  
Old 01-20-2016, 08:37 AM
macropod's Avatar
macropod macropod is online now formula to send data to 2nd sheet Windows 7 64bit formula to send data to 2nd sheet Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

As I said in post #2, a formula can't show/hide rows. A formula to fill 'YES' gaps would be considerably more complex and you'd never know whether all the data were captured unless you made sure to have amply more rows with formulae than you have with 'NO' data to display.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
formula to send data to 2nd sheet Can a Data Source be one sheet in a multi-sheet Workbook nfotx Mail Merge 1 07-01-2015 12:55 AM
Move data from one sheet to the next available row or cell of another sheet bwwalker08 Excel 0 04-10-2015 11:51 AM
Populate sheet 3 with data from sheet 1 and sheet 2 speck Excel Programming 0 01-14-2015 07:54 AM
formula to send data to 2nd sheet Help Coloring a cell in Sheet one if data is missing from another sheet Aeducan Excel 1 06-22-2014 04:49 PM
sheet 2 data highlight in sheet 1 gsrikanth Excel 1 04-21-2012 06:25 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:40 AM.


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