how to distribute data to multiple tabs in the sane workbook

**mufan** · 03-27-2020, 03:59 PM

Hi,
I have attached a sheet where i am looking to add a formula that will pull over the results from the "Raw Data" and distribute it to the appropriate tab based on sample name and on date. I have manually copied and pasted the data into the tabs the way i would like it to appear.

So the way i see it is I would paste the new set of raw data into the raw data tab. Then manually insert the date on each tab (ex. unit 1 circ water) and then have the formula search for that date and sample name in the "raw data" and pull over the correct results? I should also mention that the order of tests listed under the "parameter name" and corresponding results will never change.

your assistance is greatly appreciated.

thanks!

**RChad** · 03-27-2020, 04:34 PM

Hi,

I restructured so the tab names are consistent with the Sample Names. That way someone can use indirect for your data. I will continue to try.

**MrShorty** · 03-27-2020, 05:10 PM

[joke]My first question is -- how do you know which tabs/sheets/workbooks are the sane ones and which are insane? What are the consequences of sending data to an insane tab/sheet/book?[/joke] (sorry, needed some levity today)

What I see is something that looks an awful lot like a filtered table or a pivot table. You don't say what version of Excel you are using, but, it appears that, if you have a new enough version to have Power Query/Get and Transform, this is fairly straightforward: https://www.myexcelonline.com/blog/f...get-transform/

A pivot table can do this, but it will have trouble with the <xx text strings. Insert a pivot table and tell it to set sample name to the filter/slicer, sampled date to the column labels, parameter name to the row labels, and sum of result to the values field. Again, it is going to have trouble with the text entries that aren't numbers.

Of course, there are also the Autofilter and Advanced filter tools available, but those approaches will require a manual copy/paste step to move the filtered list into a separate tab.

If you have access to it, then I would be leaning towards using Power Query/Get and Transform for this.