Excel Help and Problems

[aygart]

What is the hardcoded number you're using for row? Why can't that be replaced with a formula?

Also, switching from HLOOKUP to INDEX, even with MATCH for both column and row, would not slow it down. If anything it would speed it up. Concatenate is a non-issue (you could also just do "month&year").

I think I need to explain the question better.

[WAM]

I think I need to explain the question better.

I don't know if understand your original question, but would the ROW formula help you at all?

Not sure if the following would help either, but when I have no patience to figure out how to deal with dragging down not ascending a formula as I'd like, I break down the formula into separate cells (with a leading ' for the parts I want to remain the same in) and then drag down the numbers I want to ascend. Finally, I put it all back together using CONCAT and then c/p to wherever I want the formulas to be.
Hopefully that made some sense.

[Yo ssi]

Sometimes switching to Google sheets temporarily helps

[avadah]

I'm creating a template and I'm looking for a way to pull the unique values from a range but I don't know what the range is bec it will change every time. I know I don't want it to start before B50 but I don't know how far down it will go. Is there a way to select a whole column as a range minus the first 50 cells?

[Yisroel Tech]

I'm creating a template and I'm looking for a way to pull the unique values from a range but I don't know what the range is bec it will change every time. I know I don't want it to start before B50 but I don't know how far down it will go. Is there a way to select a whole column as a range minus the first 50 cells?

Why not just do B50:B10000000?

Or you can do something like:

Code: [Select]

INDEX(B:B,50):INDEX(B:B,ROWS(B:B))

[etech0]

this is likely not helpful but in google sheets you can do B51:B to get the populated cells in column B starting from row 51

[avadah]

this is likely not helpful but in google sheets you can do B51:B to get the populated cells in column B starting from row 51

I was wondering if excel has something like this.

Why not just do B50:B10000000?

Wouldn't that slow down the computer bec it's so much info?

Or you can do something like:

Code: [Select]

INDEX(B:B,50):INDEX(B:B,ROWS(B:B))

I don't understand this.
Also is there a way to pull unique values if my version of excel doesn't have the =unique function?

[Yo ssi]

I'm creating a template and I'm looking for a way to pull the unique values from a range but I don't know what the range is bec it will change every time. I know I don't want it to start before B50 but I don't know how far down it will go. Is there a way to select a whole column as a range minus the first 50 cells?

You can use either OFFSET, either INDIRECT to refer the custom range:

Code: [Select]

OFFSET(A5,0,0,ROWS(A:A)-ROW(A5)+1)
INDIRECT("A5:A"&ROWS(A:A))Both result in a range from A5 to the bottom of the column.

Reference

[etech0]

I wonder if setting up a Named Range would help? Then you define it once and can use it anywhere in the spreadsheet

[avadah]

Reference

Thanks. Will try it out.

How do I do the following. I want a cell to give me the total amount of a specific color. For example a cell should tell me that there's 33 reds.

[Yo ssi]

Thanks. Will try it out.

How do I do the following. I want a cell to give me the total amount of a specific color. For example a cell should tell me that there's 33 reds.

SUMIF

[avadah]

What formula should I use:
if cell j2 is within 3 months of today then return a2 if not blank.
I know i need an if statement, I just don't know how to make the logic test within 3 months of today
Thanks

[MarkS]

What formula should I use:
if cell j2 is within 3 months of today then return a2 if not blank.
I know i need an if statement, I just don't know how to make the logic test within 3 months of today
Thanks

Elsewhere in the sheet (say Z1) put =today()
Then =if(J2+90>Z1,A2,"")

[JuryDuty]

I asked chatgpt that question and this is the response:

=AND(A1 >= TODAY() - 90, A1 < TODAY() + 90)
Where A1 is the cell containing the date you want to check. The AND function returns TRUE if both conditions are met (i.e., the date is greater than or equal to today minus 90 days, and less than today plus 90 days).

[avadah]

I asked chatgpt that question and this is the response:

=AND(A1 >= TODAY() - 90, A1 < TODAY() + 90)
Where A1 is the cell containing the date you want to check. The AND function returns TRUE if both conditions are met (i.e., the date is greater than or equal to today minus 90 days, and less than today plus 90 days).

So let's say i want it to return a certain cell if within 3 months, not true or false

[JuryDuty]

Elsewhere in the sheet (say Z1) put =today()
Then =if(J2+90>Z1,A2,"")

He said it best (although you can ask chatgpt). Main difference I had was the AND for before or after, but you can incorporate that into his just as well

[Definitions]

So let's say i want it to return a certain cell if within 3 months, not true or false

=if(AND(J2 >= TODAY() - 90, J2 <= TODAY() + 90,isblank(A2)=false),A2,"")

I think that should work. Note this is a range of 90 days for before and after. I get confused if the 90th day is included.

Also on searching it seems like the isblank function returns false even if the cell contains a formula. Keep that in mind

[avadah]

Thanks

[CountValentine]

This is the formula I am using in one of the cells. If it returns a positive number I would like it to be in green and a negative number to be in red. Can it be done?

=SUM(M2:M7)-SUM(M8:M10)-M16

[Yo ssi]

This is the formula I am using in one of the cells. If it returns a positive number I would like it to be in green and a negative number to be in red. Can it be done?

=SUM(M2:M7)-SUM(M8:M10)-M16

Conditional formatting on the cell