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

Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!