Thread: Putting Text in a referenced Cell

I'm not sure exactly what you are asking for but it sounds like you want sheet 1 to have the way they are organized now, Sheet 2 to have the original positions listed in different cells (e.g. A1 in C5) and Sheet 3 to have what that reorg will look like.

I called the first sheet "Original" sheet 2 "Reorg" and sheet 3 as "New Pattern". In New pattern, I used this formula.

=INDIRECT("Original!"&Reorg!A1)

Take a look at the attached example and let me know if that's what you want.

ChemistB

I think that's a really cool script, and might almost work if I have to restructure everything.

I think I attached a smaller version of my excel sheet.hm swatches-trying.xls

The thing is, it is not in chart format already. I would like to make it so that what is in J4 goes to the position in "Existing Wall" sheet that is marked by H4...

So in other words:

| Finish - Finish Color - Brownstone - 3G | would be placed in A2 of the Existing Wall chart.

Then I would make a new column called rearranged cells and is the new rearranged wall.

Make any more sense?

Okay, now we're talking.

First, I made a table which ranged from your Cell ID's in H to your values in J and called it "Finishes". You'll need to expand this to include all your rows (Look in Insert>Names>Define).

Then on the Existing Wall Sheet, In A1, I put this formula

=IF(ISNA(VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Finishes,3,FALSE)),"",VLOOKUP(ADDRESS(ROW(),COLUMN(),4),Finishes,3,FALSE))

and drag that across and/or down as far as needed.

The first part of that formula (the ISNA part) just makes the cell blank if there is no corresponding Cell ID in column H of Sheet1.

The second part, looks in H of Sheet1 for a corresponding cell address and brings back the 3rd column of the table ("finishes").

Is this what you are looking for?

ChemistB

Hmm. This is a lot closer. But I probably should've put in the second row in the xl sheet I attached. The first row works perfectly (thanks) but I need to be able to get Rows B through AQ to work to. Is there an easy add to this?

Also, thanks for all the help you've given me so far. I really do appreciate the help.

Hmm, where would the references to B:AQ be? I had assumed they would be in Sheet1 column H just like the references for A? If so, this will work for all the rows.

ChemistB

I posted half of the entire original file this time with the new script (the whole thing was to large file size) put in the existing wall sheet. You should be able to see what I mean by putting in the other columns. Just as the cell that says A1 refers to A1, the one that refers to B1 should be B1 on the chart. I have copied and pasted the code in the other boxes, and for some reason it does not work.

wallswatches.xls

Also, looking at the code... I'm wondering does it matter if later on in the list, they're not all of type Finish? DO I just need to change that part of the code to whatever it says (i.e. Systems)?

Doh,
I just realized what I didn't do. I didn't name the rest of H - J, although I'm confused on exactly how.

Well, I got the naming thing. I changed Finishes to "=Sheet1!$H$3:$J$26" but is there something else I need to do, because it still won't work?

Hmmmm.

You're almost there. The Defined name should cover your entire range (e.g. $H$3:$J$60). Also check to make sure it's not referring to a different workbook (ie. you copied it from my workbook and it's still referring to my workbook). The defined name should simply refer to

=Sheet1!$H$3:$J$60

Here's my updated example

Questions?

ChemistB

YAY, it worked that time!

Thank you sooo much for your help Chemist!

Spike Lumens

Also, I want to make a new chart. Would I just use the same code, but with a different defined name for the different part of the chart?

Thanks again!

You want to make a new chart in the same workbook? If so, yes, the defined name just tells Excel where to get the data from. If you want a second chart defined in M3:O100, just define a new name there and put your data there. Glad it's working.

ChemistB