Using a single cell value to repopulate multiple cells

[Chris_NetworkRail]

Hi,

I am a relative newcomer to Excel, so forgive me if this is either
impossible or a completely backwards way of working.


My boss has given me a challenge to turn a huge spreadsheet he has of
projects into neat individual report style layouts which he can print
off and give to his bosses. Each row on the sheet has to have a
seperate report. Now I can create a simple report style template in
excel, with fields which reference each specific cell in the original
sheet. What I want to know if it is possible for me to change all the
cell references by altering the value in one cell.


For instance if i created a cell with a number in it, lets say '5'.
Is
there a way I can get the cell references to recognise this as a row
number (i.e. =Thefulllist!E5) so that if i changed the value to say
8,
all the row references would change as well (i.e. =Thefulllist!E8),
repopulating the data?


I cannot alter the structure of the original table as it is in use
company wide, nor do I have access to Access.


Thanks, Chris

 

[macropod]

Hi Chris,

Check out Excel's INDIRECT function

Cheers

 

[Dave Peterson]

=indirect("'ThefullList'!E" & a1)
if A1 contained the number.

If you put the sheetname, column letter and row number in 3 different cells, it
could look like:

=indirect("'" & a1 & "'!" & b1 & c1)

 

[RichardSchollar]

Hi Chris

Index would be another option, and depending on how many formulas
utilising Indirect you will have, could be more efficient:

=INDEX(TheFullList!$A$1:$IV$65536,A1,5)

would return the intersection of column 5 (ie "E") with the value in
A1 (eg 8) on sheet TheFullList.

Hope this helps!

Richard

 

[bond-jamesbond]

Hello,

Appreciate the help on this thread, as I have been puzzling with a
similar issue recently.

Mine, however, is that the cell that I want to be "changeable" would
actually hold a reference to another workbook entirely. I create
excel models on a regular basis that all have the same worksheet
names, but each time I update I save it as a new workbook. (so the
sheet names remain constant)

E.g.

On my current worksheet, cell B7 currently holds a network address to
another workbook - P:\FOLDER\oldsample.xls

I would like to be able to change whatever workbook address is in B7
and have the cells on my current sheet update with the new reference.

For instance, on my current worksheet I want cell D7 to display the
value of Row 1 Column 2 of the worksheet "TEST" found in any of the
workbooks I put into cell B7.

(e.g. if B7 references "\oldsample.xls", I want D7 to display the
value of 'TEST'!$A$2 from sample.xls , but if I change B7 to another
workbook like "\newsample.xls" I want D7 to update and display the
value of 'TEST'!$A$2 from newsample.xls)

(or, to simplify, how can I make it so when I change the workbook
address in B7 that the formulas in D7, E7, F7, G7, etc., all draw
their cell reference from that new workbook?)

Is it even possible to source this info from external workbooks? I
have tried fiddling with both the ADDRESS and INDIRECT functions but
am getting error messages.

Comments / fomulas appreciated. Apologies if the question seems
convoluted, explaining an excel issue in text is more complicated than
I expected.

 

[bond-jamesbond]

For instance, on my current worksheet I want cell D7 to display the
value of Row 1 Column 2 of the worksheet "TEST" found in any of the
workbooks I put into cell B7.

Pardon, that should read Row 2, Column 1 if I'm talking about $A$2
further down in the post!

 

[bond-jamesbond]

SUBJECT: There is now a way to fetch information on closed external
workbooks from "named" worksheets!!!
-------------------------------------------------------------------------------------

Ok, I managed to figure out how to do this (took an afternoon!) I
found my answer by combing old posts around the web, so in the
interest of helping a future 'me' seeking a similar solution, here is
what I found:
--------------------------------------------------
The main issue with Excel's built-in "Indirect" function as suggested
above is that it can't access closed workbooks - here is a blog post
on 3 methods to combat this: http://www.dicks-blog.com/archives/2004/12/01/indirect-and-closed-workbooks/

Of these methods that can access closed workbooks, I first tried out
the MOREFUNC method and it did not work for me because I have named
worksheets (e.g. instead of "Sheet1" I use "Summary")

Then I tried Harlan Grove's function, but again - an issue with named
worksheets.

Finally, I found a modification to Grove's method that works perfectly
for me, plenty of rejoicing and shouting in this office today!!!

1. Go here to view the post
http://groups.google.ca/group/micro...f28e7cb2715/2eb861364e0e852a#2eb861364e0e852a

(Or, a direct download of the Function coding:
http://www3.sympatico.ca/sstackho/LinkedRange.zip )

2. Then use the instructions on the following page to install
http://help.lockergnome.com/office/Harlan-Grove-PULL-Code-Help-ftopict945851.html

The page is for the Grove version of the code, but the instructions
are nearly identical - once the LinkedRange function is installed,
however, just be sure to use "LinkedRange" instead of "Pull" in your
formulas

So instead of using this sample code (from the Grove help page):
=PULL("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")

It would become:
=LINKEDRANGE("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")

Hope that helps! Thanks to Harlan Grove and Shawn Stackhouse for
their respective contributions to this joyous solution. (It has
certainly made my day, after hours of hunting I have a solution to
sort my data!) Combining their instructions and code, there is now a
way to fetch information on closed external workbooks from "named"
worksheets!!!

Sincerely,

Disneyandbond
------------------------------------------------------

P.S.
Here's the easiest way to install the LinkedRange function -

-to start: download the ZIP file from the site mentioned above (http://
www3.sympatico.ca/sstackho/LinkedRange.zip)

-Then, extract the .BAS file to somewhere easy to find (e.g. your
Desktop)
-Then fire up excel, press ALT + F11 to bring up the Visual Basic menu
-Go to File --> "Import File", select the .BAS file wherever you saved
it, then hit ok
-There should now be a module called "modLinkedRange" in your Modules
-Hit ALT + F11 again, you'll now be back in Excel and ready to use the
LINKEDRANGE command in your formulas!!!

 

[macropod]

Hi,

Linking to an external workbook using the INDIRECT function is quite feasible. The main problem you'll have with the INDIRECT
function (an a number of others too), however, is that it will only work correctly if the source workbook is open - otherwise it
returns an error condition.

Cheers

 

[Toppers]

If C1 contains your number:

=INDIRECT("'Thefulllist'!E" & C1)

HTH