Excel taking data from multiple sheets

1
votes

I have a spreadsheet with a "master sheet" and 7 other sheets.

Each team member are to input data into their own sheet. The master sheet will look at the other 7 sheets and take the data within the cells. Some members will not need to put data into the cells. In the "MASTER SHEET" it has spaces for the team members who haven't typed anything.

How do I collect all the data from each sheet and put it into the "Master Sheet" but I do not want any gaps from the cells that have no data in them.

I was thinking: =Member1!Q14&CHAR(10)&Member2!Q14&CHAR(10) etc.. However, this leaves gaps if a member does not enter data into the cell.

1
excelexcel-formula
I have edited the post. - Daniel Reverent
Before we can proceed to assist on how "without gaps" solution.. You need to share how you did "collect all the data from each sheet and put it into the "Master Sheet" with the gap 1st... It will be good to know more info on where u r stuck after trying hard to solve it.. (eg. sample file/screenshot/research/method you had tried&failed).. Include those info in edit. | Those info really helps others to assist .. - p._phidot_
I was thinking: =Member1!Q14&CHAR(10)&Member2!Q14&CHAR(10) etc.. However this leaves gaps if a member does not enter data into the cell. This is why I have edited the post to ask for the best way to collect the data from the sheets. - Daniel Reverent
That is not how I thought you had it set up. But that should be easy to fix with TRIM(). =Trim(Member1!Q14)&CHAR(10)&TRIM(Member2!Q14)&CHAR(10) or perhaps you need to make a if statement if(member... <> "", And so on . - Andreas
You can query and Append all of the tables into one single table, removing any blanks rows that way. - Mark S.

1 Answers

0
votes

Just trim() it and put an inside an if() .

=IF(TRIM(MEMBER1!Q14&CHAR(10))="","",MEMBER1!Q14&CHAR(10))&IF(TRIM(MEMBER2!Q14&CHAR(10))="","",MEMBER2!Q14&CHAR(10))

Please share if it works/understandable/not.