Need a draggable formula to fill in 53 weeks

[Twiggy199]

I'm very new to this so bare with me, I need to use numbers so double bare with me...

Week #	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44	Week 45	Week 46	Week 47	Week 48	Week 49	Week 50	Week 51	Week 52	Week 53
Store Weekly Revenue
Orders placed
Over/Underspend	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

So what I need is a draggable formula that I can put in B2 from the table above that will pull the data from B16 in the table below, I have 53 sheets all with tables named the same as the sheet and i just need a formula that wont require me to manually input x=x for every cell.

Task​	Monday​	Tuesday ​	Wednesday​	Thursday ​	Friday​	Saturday​	Sunday​	Manager Reviewed​
Store Revenue​	£1000.00​							FALSE​
Shop Front Clean, Tidy & Restocked​								FALSE​
Backroom(s)/Toilets Clean & Tidy​								FALSE​
Trello Jobs Completed​								FALSE​
Cashing Up/Banking Tracker​								FALSE​
Cleaning Rota Signed​								FALSE​
Stock Checks​								FALSE​
Stock Ordered​								FALSE​
Stock Received via courier​								FALSE​
Stock Received On VEND​								FALSE​
Store: External Clean​								FALSE​
Cash Banked​								FALSE​
Bin Collection Day​								FALSE​
Miscellaneous​								FALSE​
Store Weekly Revenue​	£1000.00​		Average Sale​			Transaction Count​
Top-up Order Value​
IQOS Order Value​
Main Order Value​	£500.00​					Items Sold​
Spent on Main Order​

Sorry, this didn't appear how I thought it would so just for ease of use, B16 is the cross section of Monday & Store Weekly Revenue.

If I'm unclear in what I'm saying please just scald me.

 

[Fluff]

Hi & welcome to MrExcel.
Assuming that B1 is the sheet name, try

=INDIRECT(B1&"!b16")

 

[Twiggy199]

Hi & welcome to MrExcel.
Assuming that B1 is the sheet name, try

=INDIRECT(B1&"!b16")

Hi thank you for the extremely fast response, but i just get an error message saying "the formula contains an invalid reference"

 

[Fluff]

Ok, maybe

=INDIRECT("'"&B1&"'!b16")

 

[Twiggy199]

Ok, maybe

=INDIRECT("'"&B1&"'!b16")

Still no dice, I get the same response as before. Thanks for the help, I guess I'm going to just have to input it all manually.

 

[Fluff]

Are the values in row 1 exactly the same as the sheet names?

 

[Twiggy199]

Are the values in row 1 exactly the same as the sheet names?

Yes, the Sheet name and table name are both "Week 1"

 

[Fluff]

In B1 of the data you posted there is a space after the 1, try deleting that & see if it works.

 

[Twiggy199]

Yeah we did notice that as soon as i posted it, we removed it but it still just won't work.

INDIRECT("week 1::"&"B16")

seems to be the only way I can get it to at least copy the data over, but it's not draggable.

 

[Fluff]

Ok, how about

=INDIRECT(B1&"::B16")