0

I have an issue with using data from a source table: Lets say we have Sheet 2, where in column A is the formula =IF(Sheet1!A1 = 0;"";Sheet1!A1)

I am taking only the newest info form Sheet1 to keep another type of data in Sheet2 about the given value. In columns B,C & D in Sheet2 I have manually input data, relating to the result value from Column A.

The issue is that the information in Sheet1 needs to be kept chronologically, so if the last(newest) row is with an earlier date, Sheet1 needs to be sorted by date. This of course automatically sorts the values in column A of Sheet 2, based on the formula. However, all data in B, C & D is still based on the values, before the sort, as it is manually input.

Is there a way to move around the whole rows in Sheet2, based on the value in column A, when sorting Sheet1?

The only resolution I could think of is to paste the formula results as values, but the table would not be as dynamic anymore.

Thank you in advance!

Petya Hadzhiyska
  • 1
  • You could do this with a VBA routine but not with normal Excel commands. My suggestion: add another column in Sheet 2 in which you manually enter the value from column A when you enter B, C and D, and sort on that column. You could add conditional formatting to mark data in the new column that doesn't match column A to be red so you immediately see if the sheet needs sorting. – Christopher Hamkins Feb 16 '22 at 16:27
  • I wanted to go without VBA routines, as it makes the file heavier and Excel for Mac is not the fastest and friendliest of applications. My initial idea was to use VBA forms to enter all new data, so a routine can place the new data row wherever it should be placed chronologically in Sheet1 and to rearrange Sheet2, as well. Unfortunately, forms are not an existing option for Mac. Probably it is worth to add a VBA code after all - do you have any specific idea of a code, that would help? Thanks!! – Petya Hadzhiyska Feb 17 '22 at 07:25
  • To do this in a VBA routine, I would retrieve all the values in column A from Sheet1 and from Sheet2 and determine which of the values are present in Sheet1 but missing in Sheet2 and add them (as constant value, not formula) at the end of the other data in column A in Sheet2, then sort the used range of Sheet2 on column A. You would then see which lines need to get data in columns B, C and D. The routine could also check if there are values in column A in Sheet2 which are not in Sheet1, and color them red or something to indicate a problem. – Christopher Hamkins Feb 17 '22 at 08:35

0 Answers0