0

I have a log file which is recording the staff productivity, each tab sheet is for one staff, e.g. Tom, Oscar, Susan.

Each sheet has a Range A:I with identical headers, and some of cells contain a formula which is used to lookup the value when user selected the task types in particular cell. User input range is A2:I8000 (with formula behind)

[enter image description here][1]

How to consolidate all the sheets together from cell A2 without pasting the formula and ignore the rows with formula but nil value (i.e. the blank row)?

Now that the problem is all rows copying into single sheet with the blank rows (e.g. from A2000 to I8000 have no value, just formula)

VBasic2008
  • 44,888
  • 5
  • 17
  • 28
Ivan Wong
  • 1
  • 1
  • Try using a power query - it will pick up the values only. You can easily filter the blank rows out of the results. I posted a full solution at https://stackoverflow.com/questions/76423419/how-can-i-copy-data-from-multiple-sheets-into-a-consolidated-sheet-in-a-single-w/76458757#76458757 – CHill60 Jun 15 '23 at 07:29
  • @CHill60 May I know how could I post into the power query? let Source = Excel.CurrentWorkbook(), // List here any tables you do not want to include in the Consolidate results #"Filtered Rows" = Table.SelectRows(Source, each ................ – Ivan Wong Jun 15 '23 at 08:00
  • Please share the code you have tried (at least the one that loops through all the staff worksheets). You certainly don't have to check if an entire table row is blank. You can just check the columns that don't contain formulas, possibly a single column. Which one(s)? – VBasic2008 Jun 15 '23 at 09:33
  • If you want to paste that code into a blank query you can go to the 'Data' tab on the ribbon, drop down 'Get Data', Select 'From Other Sources', then 'Blank Query'. Once the Power Query editor is open you can click on 'Advanced Editor' on the 'Home' tab of the ribbon – CHill60 Jun 15 '23 at 12:48

0 Answers0