Small Excel Question Not Worth Its Own Thread

Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

Fuoco: Jan 3, 2009

There should be a way of doing this without the need for VBA. It's a bit inelegant though. Try the following...

In cell B2 put the following formula:

code:

="='C:\Users\Jim\Desktop\3rd Class Cities\County Budgets\" & B$1+1 & "_budgets\[" & LOWER($A2) & "]GR R'!$H$104"

This will return text to the cell of the full reference that we need. Fill the formula across and down to cover all the space required.

Save your spreadsheet at this point (but don't close it). Open up Notepad.

In your spreadsheet, select B2 down to the end, and copy the cells. Paste these into Notepad.

Then, copy the full text back from Notepad and paste into the same place in Excel. You'll notice at this point, rather than pasting back the reference as text, it's now actually reading from the reference instead.

Do the same with the remaining columns to get the remaining data.

Fuoco fucked around with this message at 23:57 on Jan 9, 2011

# ¿ Jan 9, 2011 23:52

Adbot: ADBOT LOVES YOU

# ¿ May 8, 2024 11:49

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread