How pull data always from same location?

[xrbbaker]

I want worksheet AA to pull data from a set location of worksheet BB.
Worksheet BB is edited by a user who may add, delete and move the rows. If I
make 'AA'!A10 set to ='BB'!A10 that works fine until my user moves the data
on BB to row 50. At that point my 'AA'!A10 shows a value of ='BB'!A50.

How do I set things up such that even after my user moves data to row 50 my
'AA'!A10 continues to pull from ='BB'!A10?

Thanks

 

[Bernie Deitrick]

=INDIRECT("'BB'!A10")

HTH,
Bernie
MS Excel MVP

 

[Bill Ridgeway]

Naming the cell(s) <Insert><Name><Define> fixes the source cell.

Regards.

Bill Ridgeway
Computer Solutions

 

[xrbbaker]

Aaahhhhhhh. Beautiful! Thanks so much.

=INDIRECT("'BB'!A10")

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Bill,

No, it doesn't. The name moves with the cell if the cell is cut and pasted, or dragged around.

HTH,
Bernie
MS Excel MVP

 

[Bill Ridgeway]

Bernie,

My understanding of what the OP wanted is to have a formula hit a target
cell wherever it is moved (and not have it refer to an incorrect cell).
Naming a cell does just that!

Regards.

Bill Ridgeway
Computer Solutions

 

[Bernie Deitrick]

Bill,

Just the opposite, from my reading of the OP:

How do I set things up such that even after my user moves data to row 50 my 'AA'!A10 continues to
pull from ='BB'!A10?

Bernie
MS Excel MVP

 

[xrbbaker]

Actually Bernie was able to figure out my quandary right on the money. We
keep a sheet of all subcontractors working for us currently, or have ever
worked for us. Once one of them is off a project we cut them from the
"current" section and paste them to the "historic" section. On another sheet
I pull data for forecasting from all current workers - which in this case is
just flat easier to due by brute physical pulling vs a more elegant formulaic
selction. Hence I always want data from rows 1-100 and don't want to
"follow" the data if it is cut from those rows and pasted elsewhere.

Thank everyone for the excellent help!

Regards

 

[Bernie Deitrick]

xrb,

If you need more than one cell's worth of data, then a better option would
be to use the INDEX function in combination with the INDIRECT function: for
example -

=INDEX(INDIRECT("'BB'!A1:G100"),ROW(),COLUMN())

will create links to the first 100 rows of columns A to G - enter this into
cell A1, then copy to A1:G100, and it doesn't matter where things are moved
on sheet BB. Easier than the previous example that I gave you....

HTH,
Bernie
MS Excel MVP

 

[xrbbaker]

Bernie that is too cool! I've cut and pasted your suggestion into my
cheat-sheet document of good ideas. Thanks again!

 

[xrbbaker]

Bernie,

Taking this one level trickier...

Everything works as you suggested. I'm trying to modify an existing sheet
that today is quite manual. They go into the first column, first row, revise
the existing formula to change the data being pulled from the "Feb07" tab,
cell C34 to the "Mar07" tab cell C34, etc. They do that where needed in each
row in the first column, then copy/paste the updated column across all
columns to the right, which then moves C34 to D34 etc.

With your INDIRECT suggestion I can change the formula so that the cells
never need be edited again.

='Feb07 Rel'!C34 – THE FORMULA TODAY, UPDATED MANUALY EACH MONTH
e64=â€Feb07 Rel†– USING BERNIE SOLUTION, SET CELL E64, THEN
=INDIRECT("'"&e64&"'!c34") – USE INDIRECT FUNTION TO REFER TO THAT CELL

....or it can be done a little nicer using a NAME CONSTANT

=INDIRECT("'"&Month1&"'!c34") – BERNIE SOLUTION AFTER SETTING E64 TO "Month1"

Here's my follow on question. How can I "copy" this solution to the many
hundreds of cells on the worksheet? Because the object of the INDIRECT
function is encased in quotations I can't use the feature of Excel that lets
me copy and paste to the next column whereby it automatically "moves" the
value of c34 to d34, etc.

Thanks again.

Russ

 

[Bernie Deitrick]

=INDEX(INDIRECT("'"&Month1&"'!C34:Z1000"),ROW()-ROW(XXXX)+1,COLUMN()-COLUMN(XXXX)+1)

Replace both of the XXXX's with the absolute address of the first cell that this is placed into,
like $D$8


HTH,
Bernie
MS Excel MVP

 

[xrbbaker]

Holy cow. Thanks Bernie. Too easy? I hope to be able to stump you some day.

Russ

 

[Bernie Deitrick]

Russ,

Holy cow. Thanks Bernie. Too easy?

No, not too easy - just pretty much the same as my last post on the 15th of this month.

I hope to be able to stump you some day.

That's a lot easier than you think....

Bernie
MS Excel MVP

 

[xrbbaker]

Right. I'll dig deeper before the next question.

thanks

 

[xrbbaker]

Bernie,

Instead of just plopping in your code as I had before, I really tried to
understand how the formula works that you gave me. I tried everything but
just couldn't ultimately understand why the single quotes are included/needed
in the formula. I turned on the Formula Evaluator tool but couldn't
understand the leap to the final resolution.

Here is how Excel breaks it down...

1 INDIRECT("'"&N28&"'!b7")
2 INDIRECT("'"&March07&"'!b7")
3 INDIRECT("'"&March07&"'!b7")
4 INDIRECT('March07&"'!b7")
5 INDIRECT('March07'!b7)
6 pulls appropriate value

I can't understand why Excel would evaluate your formula and get the right
result, although it does. So, given what I learned from your approach I
tried to start from the beginning and build the formula from scratch. I came
up with a different result. The only thing that I can understand how it
works is this...

=INDIRECT(INDIRECT("n28")&"!b7")

If you are willing, can you explain why your formula requires the single
quotes? What do they do - besides the obvious make it work? I would never
have been able to arrive at your solution. I still can't. I want to know
what I don't understand.

Thanks for your help.

 

[Bernie Deitrick]

Russ,

The single quotes are required by Excel in case the sheet name has a space
in it. For example

=SheetName!A1

but

='Sheet Name with space'!A1

When the sheet name doesn't have a space, Excel ignores the single quotes.
I included the single quotes because I did not know if the sheet name had
one or not - if it does, and the formula lacks the single quotes, the
formula will fail.

HTH,
Bernie
MS Excel MVP

 

[xrbbaker]

Ahh. Got it. Thanks again.

Russ,

The single quotes are required by Excel in case the sheet name has a space
in it. For example

=SheetName!A1

but

='Sheet Name with space'!A1

When the sheet name doesn't have a space, Excel ignores the single quotes.
I included the single quotes because I did not know if the sheet name had
one or not - if it does, and the formula lacks the single quotes, the
formula will fail.

HTH,
Bernie
MS Excel MVP