Excel Help and Problems

[skyguy918]

If anyone can help me with a simple conditional formatting I'd really appreciate it. I'm trying to compare two lists and highlight cells in column 1 that also appear in the list on sheet 2. Tried google and for some reason it is not working.

Let's say list 1 is in column A of Sheet1 and list list 2 is in column A of Sheet2. Highlight list 1 > Conditional Formatting > New Rule > click the last option (Use a formula...), then enter the following into the Formula box:
=COUNTIF(Sheet2!$A:$A,A1)>=1
Then you can click format to choose your desired formatting.

Keep in mind that this version of the formula will be slow if you apply it to massive lists. There are ways to make it more efficient if necessary.

[hocker]

Let's say list 1 is in column A of Sheet1 and list list 2 is in column A of Sheet2. Highlight list 1 > Conditional Formatting > New Rule > click the last option (Use a formula...), then enter the following into the Formula box:
=COUNTIF(Sheet2!$A:$A,A1)>=1
Then you can click format to choose your desired formatting.

Keep in mind that this version of the formula will be slow if you apply it to massive lists. There are ways to make it more efficient if necessary.

TY

[Yo ssi]

I believe vlookup is what you want

[aygart]

Is there a simple way to sum a cell from each tab in the sheet without referencing them individually?

[etech0]

Is there a simple way to sum a cell from each tab in the sheet without referencing them individually?

Code: [Select]

=SUM(Sheet1:Sheet3!A1)

[aygart]

Code: [Select]

=SUM(Sheet1:Sheet3!A1)

Thanks

[yuneeq]

More accurately,
2-Jan

Why the most useless date format is the default is another question

For a program that was created to work with numbers, Excel is absolutely horrendous at formatting them. 164849398282 by default turns into scientific notation like 163748e+3, you convert it to a number and half the time it gets truncated and turned into zeroes at the end. So you get lucky one time and it converts over cleanly, you save it, and open the file again and it’s back in the old useless format. Or when your number starts with a zero. You try putting a zero before, convert to text and pray it stays.

Or the times there’s a number in a cell and it doesn’t recognize it as a number even if you try converting it to a number, currency, or anything else. So you can’t sum it or use it in any formula. But once you double click on the cell, the number magically awakens, aligns itself to the right, and is suddenly recognized as a valid number.

I’m sure there are ways to solve the issues mentioned above, but typically the solution is tedious, unintuitive hacks.

Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.

[bubbles]

The TEXT formula is very useful.

* 1 can force the text to be treated like a number. You can use paste special > multiply for that if you dont want to have to add in a column

[Alexsei]

🔥Bookmark this
https://excelformulabot.com/

[skyguy918]

Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.

Regarding cell formatting resetting, what file format are you using?

[aygart]

Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.

For long strings of digits to display as text add a ' before it or format the cell/column/row/sheet as text before entering the string. If you are then pasting paste only values.

Another option is to use text to columns and select delineated and hit finish right away without adding a delineator. I think the delineator needs to be added, but it is possible that I simply have it set on a delineator that is not found in the sequences. If that becomes a problem then just go to the next step and ensure there is either none at all or one that will not be found in the data set. I have this programmed into some macros for sheets that are exported from online data and have multiple unformatted columns. I think that the concept was given to me in a response on this thread.

[yuneeq]

Regarding cell formatting resetting, what file format are you using?

CSVs or XLSX
Many times it’s downloaded reports that come formatted improperly.

For long strings of digits to display as text add a ' before it or format the cell/column/row/sheet as text before entering the string. If you are then pasting paste only values.

Another option is to use text to columns and select delineated and hit finish right away without adding a delineator. I think the delineator needs to be added, but it is possible that I simply have it set on a delineator that is not found in the sequences. If that becomes a problem then just go to the next step and ensure there is either none at all or one that will not be found in the data set. I have this programmed into some macros for sheets that are exported from online data and have multiple unformatted columns. I think that the concept was given to me in a response on this thread.

Thanks!

[skyguy918]

CSVs or XLSX
Many times it’s downloaded reports that come formatted improperly.

Thanks!

CSV won't save your formatting. If you have a CSV and make formatting changes, save it as XLSX or similar.

[yuneeq]

CSV won't save your formatting. If you have a CSV and make formatting changes, save it as XLSX or similar.

Yeh I never save as CSV. Only use it when it’s downloaded like that and change it right away.

[Euclid]

https://arstechnica.com/gaming/2022/08/excel-esports-on-espn-show-world-the-pain-of-format-errors/

[yoruel]

Anyone here write/code in VBA? I have a job opening, monsey area if you have experience( prefer SNF experience)

[aygart]

I have an excel tab that has monthly numbers for a number of different metrics with ROW1 listing the metric that the data is for. COL A is the year and COL B is the month.  On a different tab I have set up to do an HLOOKUP by the metric from a pulldown menu with the rows representing months and the columns representing years (each year is it's own column) I currently have each cell do an HLOOKUP using a formula like this =HLOOKUP(metric,sheet,115,FALSE) and each cell has the row corresponding to that month and year. The issue I am running into is that when there is no value it returns $0.00 which skews the conditional formatting of a color scale of highest to lowest values. I have been replacing the HLOOKUP with a formula like this one which places a -- in the cell instead of a zero value =IF((HLOOKUP(metric,sheet,38,FALSE))=0,"--",HLOOKUP(metric,sheet,38,FALSE)). The issue is that when I copy/paste it keeps the row number from the source even when dragging down and I have needed to change it manually.
1) Is there an overall better way to do this?2) Is there a way to paste (or replace) with it keeping the row number of the hlookup from the formula currently in the cell or to have it adjust the row when dragging or copy/pasting?

[Definitions]

I have an excel tab that has monthly numbers for a number of different metrics with ROW1 listing the metric that the data is for. COL A is the year and COL B is the month.  On a different tab I have set up to do an HLOOKUP by the metric from a pulldown menu with the rows representing months and the columns representing years (each year is it's own column) I currently have each cell do an HLOOKUP using a formula like this =HLOOKUP(metric,sheet,115,FALSE) and each cell has the row corresponding to that month and year. The issue I am running into is that when there is no value it returns $0.00 which skews the conditional formatting of a color scale of highest to lowest values. I have been replacing the HLOOKUP with a formula like this one which places a -- in the cell instead of a zero value =IF((HLOOKUP(metric,sheet,38,FALSE))=0,"--",HLOOKUP(metric,sheet,38,FALSE)). The issue is that when I copy/paste it keeps the row number from the source even when dragging down and I have needed to change it manually.
1) Is there an overall better way to do this?2) Is there a way to paste (or replace) with it keeping the row number of the hlookup from the formula currently in the cell or to have it adjust the row when dragging or copy/pasting?

Not sure if im picturing the sheet correctly. Is screenshot correct? Why is this a problem only when you have the IF formula?

Either way would adding an id column which is a concat of the year and month be an option? if yes then for the row number you can do a MATCH on the ID column using the year and month concatenated as a value. (you can keep the row number absolutely referenced.

Likely not an elegant solution but its the easiest I can think of

[aygart]

Not sure if im picturing the sheet correctly. Is screenshot correct? Why is this a problem only when you have the IF formula?

Either way would adding an id column which is a concat of the year and month be an option? if yes then for the row number you can do a MATCH on the ID column using the year and month concatenated as a value. (you can keep the row number absolutely referenced.

Likely not an elegant solution but its the easiest I can think of

It isn't only with the if formula but that is what I am trying to get into a couple hundred cells right now and each one has the row number twice.

To use your solution would require index matching for both the top col and the left column. Also, I would need to have a formula in the lookup (twice) to concatenate the month from the left and the year from the top to have the data to match to. This seems like it would slow down the sheet immensely if there are many cells with such a formula.

[skyguy918]

It isn't only with the if formula but that is what I am trying to get into a couple hundred cells right now and each one has the row number twice.

To use your solution would require index matching for both the top col and the left column. Also, I would need to have a formula in the lookup (twice) to concatenate the month from the left and the year from the top to have the data to match to. This seems like it would slow down the sheet immensely if there are many cells with such a formula.

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").