I have two sets of spreadsheets. Spreadsheet A has a bunch of data and calculations for storm-events (one sheet per storm). Spreadsheet B will be used to pull data from spreadsheet A so that the data can be summarized in one sheet.

The naming convention in spreadsheet A for each sheet is ABC.MM.DD.YY (text.month.day.year where text is the same text in each name, only the date differs).

In spreadsheet B, I need the data to be summarized with each storm having it's own row, then the columns are the summarized data. All I need is for the column data to be equal to a cell in Spreadsheet A (the s ame cell in each sheet in Speadsheet A, just a different sheet name).

With that said, is there a way for me to type the date in Column A of Spreadsheet B and then in that same row the formulas refer to the sheet in Spreadsheet A with that date?

or another, easier way? I don't know VB... no time to learn right now.

example:

Spreadsheet B:

Col A:  Col B:                                            Col C:
1/1/08       = [KBS.2008.LSW.xlsx]KBS.01.01.08!$Z$3          = [KBS.2008.LSW.xlsx]KBS.01.01.08!$Z$5
5/12/09    = [KBS.2008.LSW.xlsx]KBS.05.12.09!$Z$3           = [KBS.2008.LSW.xlsx]KBS.05.12.09!$Z$5
1/5/10      = [KBS.2008.LSW.xlsx]KBS.01.05.10!$Z$3          = [KBS.2008.LSW.xlsx]KBS.01.05.10!$Z$5 

1/19/2010 12:59:13 PM

Quote :

"With that said, is there a way for me to type the date in Column A of Spreadsheet B and then in that same row the formulas refer to the sheet in Spreadsheet A with that date?"

Can you explain this more clearly? In Worksheet B Cell 'A1', you type in a date. What do you want to happen automatically at this point?

1/19/2010 1:27:57 PM

I was hoping that there would be some way for me to specify the date and then in that respect row, each column's formula would use that same date to pull from spreadsheet A.

As in the example above, I specify the date in A1 (spreadsheet B) and B1 through M1 (spreadsheet B) use that date to pull information from Spreadsheet A, in the sheet with the date specified in A1.

it would be like a vlookup, only I need it to look up the title of different sheets... I'm thinking this is only VB possible...

the naming convention in the example:
Spreadsheet A = [KBS.2008.LSW.xlsx]
spreadsheet A, sheet 1 = KBS.01.01.08
spreadsheet A, sheet 2 = KBS.05.12.09
spreadsheet A, sheet 3 = KBS.01.05.10

in spreadsheet B, I type the date in A1 and B1-M1 use that date to go to the sheet in Spreadsheet A with those date values in the name and pull the proper cells. A2 is a different date, thus B2-M2 go to another sheet, named with those date values and pulls the cell info.


right now, in spreadsheet B, I am just dragging a row down, changing the date in Col A and then in the other columns I manually change the sheet name to match the date so that it pulls from the proper sheet in spreadsheet A.

I could also just specify the sheet name... KBS.01.01.08 and then in that row the formulas use that text string to go to that sheet in Spreadsheet A.

I suck at explaining this
In spreadsheet B, I would type in A1: KBS.01.01.08
B1 would then change to = [KBS.2008.LSW.xlsx]KBS.01.01.08!$Z$3

In A2, I type: KBS.05.12.09
B2 would then be = [KBS.2008.LSW.xlsx]KBS.05.12.08!$Z$3

thus, I just specify the sheetname in Col A, and drag down the rows from Col B-M, which updates the sheetname reference in Col B-M equations.

...I specify the sheet name in Col A... Col B-M equations then set the sheetname reference to A1 [KBS.2008.LSW.xlsx]sheetname!$Z$3


obviously Col B-M equations would have a =


[Edited on January 19, 2010 at 1:54 PM. Reason : sorry for rambling... just trying different ways of explaining it]

1/19/2010 1:30:20 PM

=INDIRECT(sheetname&"!A1")

replace sheet name w/ the cell reference that contains the sheet name and A1 with whatever cell in that sheet you want to reference.

[Edited on January 19, 2010 at 2:08 PM. Reason : ref]

1/19/2010 2:07:42 PM

^thanks, but the cell I'm referencing is in another spreadsheet...

1/19/2010 2:11:42 PM

Still doable, but because you have dashes in the sheet names you'll need to format it a little differently.

Wait, when you say "spreadsheet" do you mean "workbook" or "worksheet"?

1/19/2010 2:20:23 PM

workbook. sorry, my terminology: spreadsheet = workbook; sheet = worksheet

1/19/2010 2:22:27 PM

ah. figured out how to do it using indirect... kind of a pain, but I guess it's easier than manually changing 100s of cells.

set A1 = filename
set A2 = sheetname
set A3 = cell
=INDIRECT("'["&A2&".xlsx]"&A3&"'!"&A4)

this requires some background work since I have to set these references... any other way?

1/19/2010 2:52:39 PM

LOL, looks like you beat me to it.

[Edited on January 19, 2010 at 3:04 PM. Reason : .]

They don't all have to be cell references, btw, you can change the function so that only the sheet name is a reference and hardcode in the sheet and cell references.

[Edited on January 19, 2010 at 3:05 PM. Reason : .]

1/19/2010 2:55:20 PM

^thanks a bunch... I've never used indirect, but it's working well for this application. and I did exactly the second thing you said (after confirming that the method ^^ worked). I even have the sheetname automated based off the date I type in (and that's the only thing I have to type now).

thanks a bunch man. I owe you a beer.

1/19/2010 3:17:02 PM

I have a different excel question:


Is there a way to pull out a monotonic sequence from a list of numbers. In particular, I want the monotonic sequence that is generated by collecting the data points that are strictly larger than all data points before it.

IE.

1,2,4,6,5,7,4,70,10,13,15.

I want 1,2,4,6,7,70 (not, 1,2,4,5,7,10,13,15, even though its longer)

3/26/2010 2:15:45 PM

Column D tracks the largest number in the sequence so far: =IF(A3>D2,A3,D2)

Column C counts the number of largest numbers (increments when a new largest number is found): =IF(D3=D2,C2,C2+1)

Column F lists them neatly: =IF((ROW()-1)<=MAX(C:C),VLOOKUP((ROW()-1),C2: D12,2),"")

The column C and D formulas start on row 3. C2 is always 1 and D2 is always the first number in the sequence.

You could also implement this in a macro for a neater/cleaner solution.

[Edited on March 27, 2010 at 9:10 AM. Reason : ]

3/27/2010 9:07:11 AM