Bringing data from one spreadsheet to another in the same workbook in Excel 2007

Hello,

I am trying to help a colegue out with her workbook. She has one spreadsheet that is updated based on data entered on another spreadsheet and it works great but we are trying to add more columns and the formula isn't working for all of the cells. It is showing the formula and not the data. Some cells are working but not all of them and I can not figure out why....

Example:

Sheet1 is the spreadsheet that is updated and Sheet2 is the one that gets updated data.

The formula that is in the working cells in Sheet2 is as follows:
='Sheet1 '!$F$2581

I use the same formula updating cells but am getting just the formula in two of the rows....

I would appreciate any help with this.

Fran

francorona,
Take out the single quotes around the sheet name. The formula in that cell should look like this =Sheet1!F2581
That should make it work ok!

Ok, I did that but now it comes up with an error and askes if I want to accept suggested change and when I click yes it brings up this formula:
='[Grid]Grid '!N2687
and makes me select the workbook from my folder.....but it is bringing up the correct data. That is going to take me a long time to update the rest of the added rows though....is there anyway to get it to fill in the formula? I tried to drag it but it doesn't fill, just copies.....

Here is the spreadsheet now....

Test.xls

francorona, one thing throwing you off is the the sheet called Grid contains a space after it, that is why you are getting odd results. Right click on the word Grid and rename it without the space after it. In otherwords it is showing as "Grid " without the quotes. That will take care of one problem. But your spread sheet is very large and it would take a lot of editing to get it right. It's hard to do copy formulas becuase your formulas are not the normal consecutive runs. You are coping from bottom to top and placing the outcome in a vertical fastion. That makes it difficult. I don't thing I have a way to do it without doing each location. Good Luck.

Good Morning! Yes, I agree it's a nightmare! I don't know who created the spreadsheet but it is set up like that so they can print the Early Draw tab very large. It's almost like a map for the soldiers that come to draw equipment for training, to show them where their equipment is parked in the yard. They updade the grid tab and it is supposed to automatically go over to the Early Draw tab....but now we need to expand the yard to have more rows to park equipment. I haven't gone in yet to correct the grid tab, I just logged in at work now. I will do that, thank you so much for all of your input.

Fran

I took the space out of the tab name and it worked for the first cell that I did the formula in but not when I go to another cell....at this point I am not sure that this can be done in a timely manner if at all.....

Fran,
I think it can be completed. I should be able to have it ready tomorrow. Is that too late?
redsab

That would be awesome! You are the best! Thank you so very much!
Fran

Fran, here is your corrected file. You stopped entering formulas in cell R76 worksheet "Early Draw", you can continue from that point. I have no idea what goes in those blank cells, but at least you should be able to continue with your process. Good Luck!
redsab

Thank you so much for the help!