How to create a dynamically updated floating table

Question

In Excel I'm able to create a floating image which references a set of data and updates dynamically, which effectively creates a dynamically updating floating table. (See https://superuser.com/questions/547564/how-can-i-create-a-floating-text-box-in-excel-that-contains-data-from-a-workshee)

However, I can't seem to figure out how to do this in Google Sheets. Is there a way that I'm just missing?

Second totally unrelated question: In Google Sheets, is there a way to create one sheet, and then duplicate it but have all of the duplicates update with any changes made to the original sheet?

Ultimately, I need to have multiple sheets all formatted identically but with different data entered into each. I'd like to make it so that, should I need to make any changes, like adding a column or something, I can do it to the original sheet and all of the ones referencing it follow suit.

Answer 1

I am not sure about a dynamic image, however you could have a dynamically updating data set by using the Query function. As you update data, it will automatically update based on how you'd like to display the information. I suppose you could always just have one browser window that was open with this information in it.

Google Query Function Documentation

The importrange function would allow you to keep one sheet as your source of truth, and then import that sheet into multiple others.

Import Range Documentation

You can even take it one step further and imbed a importrange function within a query function. I'll provide you an example:

=query(importrange("spreadsheetGUID","range!A:Z"),"select * where Col1 is not null")

If this doesn't work for you right out of the gate:
A) Make sure you have edit permissions for both sheets you're referencing.
B) Try to put just the importrange function into the cell first, then grant permission for the sheet you're working in to access the sheet you're trying to reference.
C) Make sure you have the right GUID in your import range call. It's located in the URL between ...spreadsheets/d/ and .../edit#gid=...

The above query would import from the sheet data referenced in the importrange function, and then you can perform your filtering using where clauses. NOTE: You must interpret the column letters into column numbers. If your range went from B:D in your sheet, the mapping would be like so:

B => Col1
C => Col2
D => Col3

Note that Col1 is also case sensitive, so col1 or COL1 would not work.

Another REALLY cool thing, is that you can even nest query functions in query functions using these column references. Here is an example of that (PS - this is a real example I harvested from a sheet I use):

=query(query(AA:AZ,"select AQ,count(AM) group by AQ order by count(AM) DESC limit 1"),"select Col2 label Col2 ''"))

Here, I'm getting the name (AQ) with the highest record count (count(AM)) in my data set (EG: John, 5). I only needed the count, but I had to group it with a name so I could get an accurate breakdown by name to get the top result. To slice the name off, I simply queried my query to get only the second column from my data set, which was the count of the name (AM). Then I labeled it using label Col2 '', so that the result only took one cell rather than 2 (1 for the cell label, one for the data result).

There is more, but I'll stop here. Seriously, if you learn the query function + importrange your spreadsheet game will jump TREMENDOUSLY! Have fun! :)