0

I would like to assign values to cells in one sheet based on the values of cells in another sheet. I have followed this answer, but it appears to work only if both the reference cell and the target cell are in the same sheet.

For example, in Sheet1 Cell B1, I have the formula:

=IF(ISBLANK(A1),"",SetCellValue("C1",A1))

This results in Cell C1 being assigned whatever value is in A1. However, if I attempt to place the target cell in a different sheet:

=IF(ISBLANK(A1),"",SetCellValue("Sheet2!C1",A1))

the SetCellValue function returns a zero (meaning, the value was not written). I'm not particularly adept at VBA, so I'm not certain how to alter the code to get the desired results. Any thoughts? Thanks in advance!

  • Do you need to have Sheet1 set the values in Sheet2? Would it be good enough to have an expression like `=IF(NOT(ISBLANK(Sheet1!A1)),Sheet1!A1)` in Sheet2? – Ann L. Aug 19 '19 at 16:46
  • Unfortunately, yes. Sheet2 should not contain any formulas. –  Aug 19 '19 at 16:47
  • By the way, the first problem with the code in the answer you reference is that the function `IsCellAddress` can only handle cell addresses in the same spreadsheet. It doesn't allow for the possibility that a single-cell address could have a Sheet prefix. That can be fixed, but would require a bit of fiddling, and I can't guarantee that that's the ONLY problem with using this code with multiple sheets. – Ann L. Aug 19 '19 at 16:47
  • Okay. Could you create Sheet2 with the formulas in the cells, and then do a Copy of everything in Sheet2, followed by a Paste As Values into either Sheet2, or a new Sheet3? (So that all your formula cells now contain only values.) – Ann L. Aug 19 '19 at 16:49
  • I could, but the end goal is to have as little copy/paste as possible. This workbook will be used as part of a workflow, and I'd prefer that my users not have to do any manual steps other than importing data to Sheet1. That data is evaulated by locked formulas on Sheet1, and the results should show up on Sheet2. –  Aug 19 '19 at 16:52
  • Okay. If you can wait 'til this evening (US Eastern Time) I can see if I can hack that macro code to take expressions with sheet prefixes. – Ann L. Aug 19 '19 at 16:54
  • No rush! Any help is much appreciated. :) –  Aug 19 '19 at 16:55
  • Hi, I'm back! I'm sorry it's taken me so long to return to this! – Ann L. Aug 27 '19 at 00:44
  • I'm not sure the solution you found is going to work with different sheets. The technique it's using doesn't scale to multiple sheets very well. – Ann L. Aug 27 '19 at 00:45
  • But, it should be possible to write a macro that would simply copy everything in `Sheet1!A1:A100` and copy it to `Sheet2!C1:C100`. (Row numbers are just examples.) Would that work for you? – Ann L. Aug 27 '19 at 00:46
  • Or, more specifically, everything in `Sheet1!A1:A100` that meets certain criteria, and copies it to `Sheet2!C1:C100`? – Ann L. Aug 27 '19 at 00:48

0 Answers0