PDA

View Full Version : Solved: Referencing a cell plus its formatting



pglufkin
03-14-2013, 04:56 PM
Attached is a simple spreadsheet referencing a cell through a + operator. I need a way not only to bring the value over with the + operator, but also bring the color over from the cell referenced. Can anyone help?

SamT
03-14-2013, 06:29 PM
Not with a cell formula, can do with VBA.

BTW, you don't nee =+A1; =A1 will do the same. Unless you are adding some value to A1.

pglufkin
03-14-2013, 06:51 PM
Ok, thanks, can you point me to a simple script that will do the trick? Thx.

SamT
03-15-2013, 01:17 AM
The Script is easy.

PseudoCode
Set CellB = CellA Well, a bit more complex...

How do you intend to call it? A button? A macro that you run using The menu Tools >> Macros?

You can do the same thing by Copy and paste.

pglufkin
03-15-2013, 09:09 AM
Copy and paste? not for 10,000 rows per hour. Thx for your response, here's the deal, if you look at my spreadsheet, I need to bring over whatever column has an entry to the combined column, with the corresponding color, no row will have more than one entry, but the row's entry can be in any column. Thank you again.

pglufkin
03-15-2013, 01:14 PM
Sorry, I am not being very clear. I have a better spreadsheet attached showing what I need.

I need to bring over the corresponding color of whatever column has an entry that will be added to the column at the end. The column at the end has an equation that will bring over whatever value is in the corresponding column that has a value. No row will have more than one entry, but the row's entry can be in any column.

Each column will be a specific color.

The code can execute until it gets to the end marker.

Thanks again.

SamT
03-15-2013, 05:20 PM
If I understand your formula
If A is not empty and C,E, and G are empty, then use A, Else leave blank.
If A is Empty, then Left To Right, use the first Non Empty cell in C,E, ang G.

BTW, I rewrote your formula to
=IF(COUNTA(G3,E3,C3,A3)=1,IF(A3="",IF(C3="",IF(E3="",IF(G3="","",G3),E3),C3),A3),"NA") And it works fine. Do you have a reason to Add the Cell's value rather than reference it.

pglufkin
03-16-2013, 12:18 PM
Thank you, but what I need is a script, we'll set that aside for a moment.

The function below was a little bit crude, was not really as concerned about the function being right as I was painting the result with the format of what I brought over.

I am now honing in on my function a little bit more and since you are engaging with me, thought you might take a look at it. Please see my example file.

It works for some situations.

Thank you.


If I understand your formula
If A is not empty and C,E, and G are empty, then use A, Else leave blank.
If A is Empty, then Left To Right, use the first Non Empty cell in C,E, ang G.

BTW, I rewrote your formula to
=IF(COUNTA(G3,E3,C3,A3)=1,IF(A3="",IF(C3="",IF(E3="",IF(G3="","",G3),E3),C3),A3),"NA") And it works fine. Do you have a reason to Add the Cell's value rather than reference it.

SamT
03-16-2013, 01:31 PM
I need to know what your formula is supposed to in order to replace it with a Procedure (Macro, or Script.)

This is what I think you want:
If A is not empty and C,E, and D are empty, then use A, Else leave blank.
If A is Empty, then Left To Right, use the first Non Empty cell in C,E, ang D.

Is It? What you want?

sassora
03-17-2013, 09:14 AM
How about something on these lines:

Sub Transfer_text_with_formatting()

Dim NumberofRows As Long, NumberofColumns As Long, RowCount As Long, ColumnCount As Long

NumberofRows = ActiveSheet.UsedRange.Rows.Count
NumberofColumns = 7

For RowCount = 1 To NumberofRows

Range("H3").Cells(RowCount, 1) = ""

For ColumnCount = 1 To NumberofColumns
If Len(Range("A3").Cells(RowCount, ColumnCount)) > 0 Then
Range("H3").Cells(RowCount, 1) = Range("A3").Cells(RowCount, ColumnCount)
Range("H3").Cells(RowCount, 1).Font.Color = Range("A3").Cells(RowCount, ColumnCount).Font.Color
End If
Next ColumnCount

If Range("H3").Cells(RowCount, 1) = "" Then Range("H3").Cells(RowCount, 1) = "NA"

Next RowCount

End Sub

pglufkin
03-17-2013, 10:40 AM
I've been playing around with my equation, but it still has some bugs. Please see the attached spreadsheet to see how far I've gotten. I'm getting close and thanks for your help. The equation works fine in the example until I get to row 124, and then it is inconsistent.

Here is what I need:

I need to produce text from the function that meets the following criteria. The text, when it appears, is always the same depending on the column its in, i.e. A is One, B is Two, C is Three, and D is Four. Sometimes the text does not appear in the original set . . . this is what makes the function somewhat complicated. If the text is not in the original set it needs to produce the corresponding text that has the lowest digit. The digits, 1 through 4, can be mixed up in the columns. The function needs to give the corresponding text to the lowest digit that has text associated with it. As I said, the function I have now works fine until I get to row 124, afterwhich it increasingly gives me the wrong result. I can't figure out why it is giving me the wrong results.

P.S. Thanks for the script, I will be checking it out.

sassora
03-17-2013, 01:02 PM
The VBA code I posted solves the problem you originally posted (with tweaking). Does your last post show information that aims to solve that or is this something new?

SamT
03-17-2013, 01:05 PM
In this attachment, the yellow column holds a formula that uses a UDF.

Putting the formula in any column, it still uses Columns A to D to get the value

sassora
03-17-2013, 01:29 PM
=IF(MOD(ROW(),2)=0,"",INDEX(A2:D2,1,MATCH(MIN(IF(A2:D2<>"",A1:D1)),A1:D1,0)))

Paste into cell and press Ctrl-Shift-Enter - works without VBA

pglufkin
03-17-2013, 01:51 PM
Ok, interesting, I have two solutions:


In this attachment, the yellow column holds a formula that uses a UDF.

Putting the formula in any column, it still uses Columns A to D to get the value

and sassora

VBA:

=If(MOD(ROW(),2)=0,"",INDEX(A2:D2,1,MATCH(MIN(If(A2:D2<>"",A1:D1)),A1:D1,0)))




Paste into cell and press Ctrl-Shift-Enter - works without VBA

SamT's works, but i need the UDF to reference off a column the solution will reside in . . . can be any number of columns but will always reference the four columns of data to the left in the same positions. Also, the four columns of data will reside a little differently with the possibility of some irrelevant data between the columns, please see attachment. Sorry, I would have made this clear in the beginning but I didn't think this would lead to a UDF, which leads me to sassora . . .

I can't get it to work when I entered it into the original spreadsheet as an array function, perhaps you can send the original spreadsheet with the function in it so i can make sure i am doing it correctly? Also, how do you mass copy an array function down 10,000 rows? If that is too hard, wouldn't a UDF be the best approach?

Thanks, most helpful.

sassora
03-17-2013, 02:19 PM
I've attached the formulas, the cells are in green.

You can copy them as usual, by autofill or drag copy.

I should mention that there were some entire blank lines in the data, I removed these before applying the formulas

SamT
03-17-2013, 02:35 PM
but i need the UDF to reference off a column the solution will reside in Do you mean that you want the result in a column other than the one the UDF is in? That can be arranged. Will the result always be in the same respective column or do you want to pass a column for it to use?

If a passed reference is wanted, The UDF can use an Offset value, (3), a Column Reference ("H"), or a Cell Reference that has the column to use, ("H1"). Assuming the UDF is in E3, all those example would put the result in H3. It could also use absolute Offsets, eg; (-1, 3) would put the result in H2, with UDF in E3.

In this example you have "irrelevent data." Does that mean there might be something other than a blank or "one" to "four" in them?

In this example there were blank rows. Will the real thing have some blank rows.? if yes, do you want the UDF to ignore them (more coding) or will you not put the UDF in blank rows?

pglufkin
03-17-2013, 02:51 PM
Thx sassora, I think this will work . . . the only variation for my application is there is interspersed irrelevant text between the columns as you will see attached. It renders the formula #N/A. Also, the column priority will be variable and it will be one row at the top. That is fixed by just making priority range above absolute as you will see. Thank you, I hope the irrelevant text issue can be overcome, and then I will be set.

pglufkin
03-17-2013, 02:55 PM
Hi SamT, thx for your help, responses below.


Do you mean that you want the result in a column other than the one the UDF is in? That can be arranged. Will the result always be in the same respective column or do you want to pass a column for it to use?

Yes, the results will always be in the same respective column (one to the right) as the other interspaced columns.

If a passed reference is wanted, The UDF can use an Offset value, (3), a Column Reference ("H"), or a Cell Reference that has the column to use, ("H1"). Assuming the UDF is in E3, all those example would put the result in H3. It could also use absolute Offsets, eg; (-1, 3) would put the result in H2, with UDF in E3.

Good

In this example you have "irrelevent data." Does that mean there might be something other than a blank or "one" to "four" in them?

Always one to four in them. Whenever there is text, there will be irrelevant data just off to the left. My example was not a good example of irrelevant data . . . so if there were four fields populated, four fields of irrelevant data, three fields, three corresponding ID, so on.


In this example there were blank rows. Will the real thing have some blank rows.? if yes, do you want the UDF to ignore them (more coding) or will you not put the UDF in blank rows?

There will be some blank fields . . . . thank you. If the array formula that sassora came up with doesn't work, I guess the only one to work would be a UDF?

Also, take note that the variable priorities (the 1 to 4 numbers in the top row) will be absolute for all data below. In sassora's solution it is easy just to make that range absolute in the formula. I don't know how it would affect the UDF.

pglufkin
03-17-2013, 03:06 PM
Ok Sassora, I solved that issue. I just but the number 5 in the absolute row above in the columns of the irrelevant data (always more than 4, so therefore would never be the minimum) however your formula only works for every other row because of the first condition. Please see attachment.

pglufkin
03-17-2013, 03:18 PM
Sassora, I fixed that by taking out the conditional statement.

sassora
03-17-2013, 04:01 PM
This formula focuses on the ones, twos, threes and fours, so the additional characters included in the last workbook will be dealt with.


=IF(MOD(ROW(),2)=1,"",INDEX(A2:H2,1,MATCH(MIN(IF((A2:H2="One")+(A2:H2="Two")+(A2:H2="Three")+(A2:H2="Four"),A1:H1)),A1:H1,0)))

sassora
03-17-2013, 04:03 PM
Thought I uploaded the file...

I'm off for the night now, good luck

pglufkin
03-17-2013, 04:17 PM
Thank you guys I'm all set. Thank you very much Sassora.

SamT
03-17-2013, 06:04 PM
PG Lufkin,

Now that Sassora's got you fixed up, we're all good. If you ever decide to use the UDF, anyone can help you refine it, now that it works on clean data.
All it needs for dirty data is a Select Case and a "Cell.Offset() = " to put the result where you want it.

sassora
03-18-2013, 02:15 AM
A slightly better solution using a formula is:
=IFERROR(INDEX(A2:H2,1,MATCH(MIN(IF((A2:H2="One")+(A2:H2="Two")+(A2:H2="Three")+(A2:H2="Four"),A1:H1)),A1:H1,0)),"")

The function before would only return a result on an odd row number. This function removes that limitation.

Again Ctrl-Shift-Enter applies (when the cell is in edit mode)

pglufkin
03-18-2013, 07:15 AM
Thank you all.

sassora
03-18-2013, 11:48 AM
No problem, could you mark this post as solved, using thread tools?

pglufkin
03-29-2013, 12:40 PM
Ok, I marked as solved. Thank you very much, did not know you were supposed to do that.