Gathering information from Worksheet labels

[Kerry Soloway]

I suspect that this group is way past my Excel abilities and I hope you can
help with an ongoing problem.

In many of the workbooks that I have created, one of my worksheets is a
tally of data from all of the individual worksheets.

For example, if I have a workbook labeled Invoices, worksheets 1 through 50
would be labeled Invoice 1, Invoice 2 .... Invoice 50. Following Invoice 50,
I might have a worksheet labeled, Tally. The first column of which would be
Invoice Number, and the other columns would be extracted from a variety of
cells from each invoice such as Client, Date, and Total.

In the past, I have had to manually enter all of that data because I haven't
figured out a way to retrieve that information automatically. What I would
like to be able to do is to enter the name of the Invoice number by using
the = function and simply pointing to it. Then I would like the other
columns to refer to the appropriate cells in only that worksheet.

Can this be done? Easily?

FYI, I am using Microsoft Excel:mac which came bundled with Microsoft Office
X (circa 2002).

Thanks in advance.

 

[aidan.heritage]

Not clear from what you have typed, but I THINK you will find the
INDIRECT function may be what you want

for example

=INDIRECT("Sheet2!"&B3)

with B3 containing C28 as text would translate to sheet2!C28

so if you entered invoice number 1 in the column then
=indirect("'Invoice " & range("A1").value & "'!C2")

would translate to

='invoice 1'!C2

 

[Kerry Soloway]

Close but no cigar. Perhaps I misunderstand how to enter it. However, let me
try to be more succinct with an actual case.

I have a workbook of Invoices #s 1-50 (Sheet1 through Sheet50).

Sheet 51 would be an index of all of the Invoices. The Sheet names are INV
01 through INV 50. My index would look like this:

Column A: Invoice # (taken from the name of a worksheet, i.e, INV 01)

Column B: Client Name (='INV 01'!A7)

Column C: Project Name (='INV 01'!D11)

Column D: Date (='INV 01'!C4)

Column E: Amount (='INV 01'!E38)

Now what I would like to do is simply increment the number of the invoice
and have the other columns follow suit. In this way I could automatically
populate my workbook without having to paste the information sheet by sheet.

Does that make it more clear?

 

[timwight]

Aidans solution is still the one, just apply it differently:

Column A: Invoice # (taken from the name of a worksheet, i.e, INV 01)

Column B: Client Name =INDIRECT(A1&"!A7")

Column C: Project Name =INDIRECT(A1&"!D11")

Column D: Date =INDIRECT(A1&"!C4")

Column E: Amount =INDIRECT(A1&"!E38")


Twi

 

[Kerry Soloway]

Thanks, Twig.

That is absolutely the solution that I was seeking. Now the only thing that
hasn't been dealt with is incrementing the name of the worksheet.

Ideally, instead of having to type in the name of each worksheet in column
A, I would simply type in a formula that I could paste into the other cells
in the column.

In other words, if A1 is INV 01. I would like A2 to =A1+1. Pasting this
formula into cell A3 would yield A3=A2+1, etc. I know that this isn't
possible because the contents of A1 is a text string and not a number. Is
there some way to accomplish this short of renaming every worksheet with a
number only? Some function that would strip the letters from the text and
then allow me to increment the number?

Thanks again.

Kerry

 

[aidan.heritage]

If you type INV 01 in A2 and INV 02 in column 3, you can auto fill down

alternatively, as my original suggestion was supposed to suggest, put
1, 2, 3 in the cells in column A and let the indirect function do the
work

INDIRECT("'INV " & text(A1,"00")&"'!E38")

 

[ksoloway]

Now I get it. Thanks.

Kerry