Forum Discussion
Report only populated cells into another tab sheet
Whilst i've used Excel for many years, i'm struggling to get my head around this.
I have a materials costing spreadsheet with 4 tabs containing various sums and lookups between them.
It looks up a material code and reports info and pricing for the overall scheme.
We then use this to compare the original anticipated total, versus the actual end of project cost for reconciliation.
On one tab, I have a final column which reports 'overspend' and 'underspend' currency values.
Any 'correct spends' return a blank cell.
Is there a way to pull ONLY the populated values into another tab/sheet?
(There may only ever be 3 or 4, randomly located in a long list of 100+)
As well as report that cell value, I also need it to report 2 other cells to the left of it.
For this problem, I am unable to use pivot tables or macros.
I have saved a demo version with dummy figures, which I can share if anyone would be kind enough to take a look for me please?
Thank you so much, Charlie
=IFERROR(INDEX(VISITS!$I$6:$I$201,SMALL(IF(LEN(VISITS!$U$6:$U$201)>0,ROW(VISITS!$U$6:$U$201)-5),ROW('COST SUMMARY'!$A1))),"")
You can try this formula for column I. For column U the INDEX range is adapted accordingly Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
4 Replies
randomly located in a long list of 100+)
？
I guess if you share your workbooks and your expected result,more people can visualize what you need.peiyezhu
Thank you. I have attached the dummy version.
I need to report the populated cells of COLUMN U from the "VISITS" tab
into the "COST SUMMARY" tab (somewhere below the existing data)
I will also need it to then pull through the corresponding cell from COLUMN I
So kind of - If cell U is populated, report I and U=IFERROR(INDEX(VISITS!$I$6:$I$201,SMALL(IF(LEN(VISITS!$U$6:$U$201)>0,ROW(VISITS!$U$6:$U$201)-5),ROW('COST SUMMARY'!$A1))),"")
You can try this formula for column I. For column U the INDEX range is adapted accordingly Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
