I have an excel document with lots of sheets named city1, city2 etc. The data for each city has the same cell. Population is in city1!B3, city2!B3 etc. I have 10 other variables on each sheet that I need to copy and they are not next each other.

How can I copy this data onto one sheet easily?

put on hold as too broad by bertieb, VL-80, fixer1234, music2myear, n8te 11 hours ago

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    If their name is strictly city<number> with consecutive numbers, you can use row and indirect. – user202729 Dec 6 at 16:33
  • No unfortunately not they aren't they have different names. Is there another way? – Robin Lindström Dec 6 at 16:55
  • If I make a column with the names of the cities, would that help? – Robin Lindström Dec 6 at 17:01
  • yes, it will... – p._phidot_ Dec 6 at 17:01
  • Please provide some sample data, upload a screenshot to a picture sharing site and edit your question with the hyperlink. Someone here will be able to edit it to include your examples (instead of hosting them externally) and you will get an answer much quicker. – Kevin Anthony Oppegaard Rose Dec 6 at 17:10

In a new sheet, put "city1" in A2, "city2" in A3, "city3" in A4, "city4" in A5.

Assuming you need values from B3 , B7 , C2 and F3 of each Sheet.

type "B3" in B1 ,"B7" in C1 ,"C2" in D1 ,"F3" in D1 .

then put =INDIRECT($A2&"!"&B$1,TRUE) in B2, drag till E5.

I think you get all the values you need by now.. hope it helps.

  • I tried doing this but I got this error: imgur.com/a/W3iOjwM. Do you know what I did wrong? – Robin Lindström Dec 6 at 20:22
  • IMHO, (I don't have a mac to try this..) you may try to use =INDIRECT($A2&'!'&B$1,TRUE) or =INDIRECT($A2&CHAR(33)&B$1,TRUE) . ( : – p._phidot_ Dec 7 at 2:15
  • I have tried all of these on both mac and PC and they don't work. Are you sure this is correct and works on your computer? – Robin Lindström Dec 7 at 7:50
  • works on mine ... – p._phidot_ Dec 7 at 8:06
  • or.. lets troubleshoot this.. 1. is "Algiers point" is the exact spelling of the sheet name in you file? 2. In your excel, what will the link formula look like if you directly refers to the A1 cell of 'city1' sheet from let say.. A3 of city2 sheet ? ( mine is =city1!A1 ) – p._phidot_ Dec 7 at 8:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.