Vlookup

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)

Find more posts tagged with

Healthcare

Formulas

Services

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…