=unique(vstack in a table

[L8NightKebab]

Hi, I'm encountering an issue where I use =unique(vstack to consolidate data from other tables onto 1 table. However when I convert that range of data with the vstack function the table gives me a #SPILL error. This is despite there being enough space for the data being imported as I already have the range I just turn it into a table.

Here is the formula for importing the data:
=UNIQUE(VSTACK(
FILTER(SUNDAY!D5:D7, SUNDAY!D5:D7 <> ""),
FILTER(MONDAY!D5:D7, MONDAY!D5:D7 <> ""),
FILTER(TUESDAY!D5:D7, TUESDAY!D5:D7 <> ""),
FILTER(WEDNESDAY!D5:D7, WEDNESDAY!D5:D7 <> ""),
FILTER(THURSDAY!D5:D7, THURSDAY!D5:D7 <> ""),
FILTER(FRIDAY!D5:D7, FRIDAY!D5:D7 <> ""),
FILTER(SATURDAY!D5:D7, SATURDAY!D5:D7 <> "")
))

 

[Vogateer]

You can't use a spill formula in a table. We either need to get rid of the table or find a different solution.

 

[Vogateer]

One Possibility for keeping the table, assuming your table starts on the first row (that is, the table headers are on the first row):

=CHOOSEROWS(UNIQUE(VSTACK(
FILTER(SUNDAY!D5:D7, SUNDAY!D5:D7 <> ""),
FILTER(MONDAY!D5:D7, MONDAY!D5:D7 <> ""),
FILTER(TUESDAY!D5:D7, TUESDAY!D5:D7 <> ""),
FILTER(WEDNESDAY!D5:D7, WEDNESDAY!D5:D7 <> ""),
FILTER(THURSDAY!D5:D7, THURSDAY!D5:D7 <> ""),
FILTER(FRIDAY!D5:D7, FRIDAY!D5:D7 <> ""),
FILTER(SATURDAY!D5:D7, SATURDAY!D5:D7 <> ""))),
ROW([@[Name]])-1)

 

[L8NightKebab]

You can't use a spill formula in a table. We either need to get rid of the table or find a different solution.

The other tables are across different sheets are in different places as they show different shifts (each shift having tables with each department having its table and the employees being moved around departments on a daily basis), what my original goal is to consolidate the data onto 1 table so even when the employees are across different departments their hourly pay for each day is tallied up in 1 table. I tried using the excel consolidate feature on the data tab but i cant select such a broad range of tables. Any chance you could recommend a a better way to do this?

 

[Vogateer]

If all the different departments had the exact same structure in their spreadsheets, and they were all in the same folder, you could using Power Query (Get Data) to bring all of them in to one table.

Did you try the formula I mentioned above and find that it wasn't the right formula for you?

 

[L8NightKebab]

If all the different departments had the exact same structure in their spreadsheets, and they were all in the same folder, you could using Power Query (Get Data) to bring all of them in to one table.

Did you try the formula I mentioned above and find that it wasn't the right formula for you?

Yeah it's not each day has the tables in many different places, I'm working on a spreadsheet designed by someone else. I've never used power query and I thought that you can only use it when having data in tables? the consolidation register has all data dynamically imported and will always be the same can I use power query on that even though I can turn each individual set of data into a table since there is an issue with the formula I'm using?

 

[Vogateer]

So all those initial spreadsheets (the ones that you're importing data from) are set up in a print layout instead of a tabular layout? If so, Power Query probably isn't going to help you. You can use power query with CSVs and other sheets that don't have tables, but in my experience the data does need to be in a tabular format to use it properly. I guess it's easier to stick with the consolidation feature you're using.

You can't use a spill formula within a table, but the formula I posted above is not a spill formula so it can be used in a table.