Vlookup

Chaim T

Hello, 

We have here 5 different sheets containing records for Medical service details, including columns for patient name, ID, DOB, charge amount, payment status, etc.

Now i want to get a sixth sheet that will look up from the above 5 sheets only the rows that's stated as Denied, so i can view all denied claims in one sheet, then, once a claim is being updated in the 5 sheets as paid, it should automatically be updated in the sixth sheet as well,

I would appreciate if anyone can help me?

Thank You

I did the bellow formula in Excel, but will not work in smartsheet , and even in excel this formula will not update the return cell properly when one of the lookup cells is being updated,

=VLOOKUP(H3,B4:F28,{2,3,4,5},FALSE) 

Brian W

Hi. It sounds like you want to use a report:

https://help.smartsheet.com/articles/522214-creating-reports?_ga=2.191383978.1441499970.1540829842-15152906.1534868962

Use Where? to select the sheets you are using, and What? to select the column with Denied in it and it should give you exactly what you are looking for. You can also select which columns you want to view on your report.

Mike Wilday

I agree with Brian. Reports will allow you to pull specific rows from each of your sheets. You can set it up to grab only denied rows. When a row is updated in one of the main sheets - OR - from the report itself, it will write that data back to the main sheet(s). 

Here is a link with a video to see it in action. https://help.smartsheet.com/videos/smartsheet-reports

 

Chaim T

Thank you Mr. Brian and Mike, for your valuable time replying, it works well, but i have now 1258 rows without the ability to filter, anything i can do?

 

Mike Wilday

Hi Chaim, 

The report itself is a filter. You can use the report builder to filter data based on who - or what -- or when - which will provide you just the right data from all the sheets that you want. You can duplicate the report and save versions filtered however you'd like. 

Does that make sense? Let me know if you have any questions.

Chaim T

Thank you, i share the main report with other team members as "Editor-can share" only, then they copy it for them self, so they can also modify the report builder, and all main sheets and all copied reports are being updated accordingly. Amazing.

I have here some columns in the report that are not editable, even when the source columns are unlocked,  what can i do?

 

Mike Wilday

Do those columns contain formulas? If so, you can't edit columns with formulas in a report. I always provide the Sheet Column originally generated in a report so that if there are formulas in my sheet that need to be replaced with actual data, then I can easily jump to that sheet by using the link. 

Hope that helps.