-1

I have started to build a couple of workbooks that I would like to communicate with each other. My goal is to take entries from column1 @ midnight,6am,noon, 6pm, and 11pm in workbook1 and 'copy/paste' them to row1 with the same labels in workbook 2. My main question is do I need VBA for this? My research leads me to believe that I do. However, I know that if I am doing this between spreadsheets in the same workbook I don't. I just use code such as ='Sht1'!A1 on spreadsheet 2 and it will copy and paste that entry from spreadsheet 1, so is there a similar way to do so with workbooks? Thanks for any input!

Community
  • 1
  • 1
Jfwhyte8
  • 1
  • 4
  • If you want to automate this process, then VBA is the preferred method. It's possible to use `INDIRECT` or linked cells, but I'm not sure that would get you what you want. – PeterT May 03 '18 at 20:17
  • @pnuts, I tried to use this function ='[May 2018]4th'!$C$13:$D$13 and =[May 2018]'4th'!$C$13:$D$13, but I get a #Value! with both. It prompts me to open the file, I click on the file and the reference point, but then gives me that #Value! error message. Any thoughts? – Jfwhyte8 May 04 '18 at 14:35
  • @PeterT, thank you for the input. I will research INDIRECT and linked cells. – Jfwhyte8 May 04 '18 at 15:37
  • @PeterT, I have found some websites that give INDEX as the function to call for this reference, but I am having trouble with it. Now I am getting a #REF! error instead of the #VALUE! error. This is my code: =INDEX('[May 2018.xlsm]1st'!$C:$C,13,3) I have done slight variations that come to the same conclusion. – Jfwhyte8 May 04 '18 at 17:00
  • @PeterT, I have run into a reference problem, but I can not figure out the solution. I used the formula below to reference data and it works. Every time. However, when I try to move onto the next months spreadsheet and use the same formula, calling for the correct month I am getting 0.00 as my entry, so: =INDEX('[May2018.xlsm]1st'!A13:$C$13, 1, 3) works and references the entered number (say 150) BUT: =INDEX('[June 2018.xlsx]1st'!A19:D19,1,3) doesn't work. Do I have a syntax error that I am over looking? – Jfwhyte8 May 08 '18 at 18:56
  • What happens when you run the same `INDEX` formula from inside the "June 2018.xlsx" workbook? (delete filename part so it looks like `=INDEX(1st!A19:D19,1,3)`) If that works, then it suggests there's a problem with the filename. Other than that, this is difficult to figure without the actual workbooks. – PeterT May 08 '18 at 19:06
  • Ok, I ran it with no file name and it produced the same result. Fair enough on the tough to solve part. Any thoughts on where to look? – Jfwhyte8 May 08 '18 at 19:34
  • If you placed that formula in a cell within the "June" workbook and it had the same result, then I suggest it's a problem with the data itself. – PeterT May 08 '18 at 19:37
  • Figured out my issue with that one. Apparently, unless I am doing something wrong which is possible, INDEX will not update the cell once an entry is made/changed. So, I guess this is not the best function to use with a template and an unused spreadsheet. – Jfwhyte8 May 08 '18 at 20:07
  • @PeterT, I used a link via the Shift, Ctrl, and Enter method and now it is updating and I also switched to SUM function, which works for some reason. I'm basically self teaching, so IDK why or what the reasons for any of this stuff working are LOL – Jfwhyte8 May 09 '18 at 13:20

1 Answers1

0

I figured it out. INDEX function was correct, at least in this instance. The correct formula was not far off because I had my row and column messed up, so the answer was:

=INDEX('[May 2018.xlsm]1st'!$A$13:$D13,1,3)

To break it down for people that do not want to use VBA and struggle with coding like me :)

INDEX() is my function

'[May 2018.xlsm]1st'! is the name of the workbook and sheet that I want to reference and ! means that the workbook is macro-enabled

$A$13 is the beginning of my range (A,13)

$D$13 is the end of my range (D,13)

So my range is 1 row and 4 columns (Think of it like a table), yours could be whatever you want (2x4 or 6x6 or whatever size you need)

1 is the row in the range that I want to reference

3 is the column in the range that I want reference

Be sure to close the function with )

I turned out making this more complicated than it should have been, because I had merged cells and I was technically referencing the right row, but not the right column. I was using (1,2) as my row and column which was referencing my first cell.

Hope this helps.

EDIT: I found later that this only works for referencing static data, so if you use it for a template or another spreadsheet that gets updates, this is not the answer for that situation. I will update again if I figure it out.

EDIT 2: For this to work with a updating spreadsheet, do the following.

=('[June''18 7Q10.xlsm]1st'!$C$13:$D$13)

Its actually much simpler to perform than I originally made it out, but going back to my beginner programming classes I wanted to make sure I understand why it worked not only how to perform it.

The process via LINK is:

Open both workbooks (Source workbook = 1 and Updated = 2)

Find the cell you want to have the data 'Pasted' TO in (2) and edit it with =(.

Now go to (1) and click the cell the that you want to 'Copy' data FROM. This will generate a reference point for you.

Now go back to (2) and finish the function out by closing the argument with ).

Finally, simultaneously press Shift, Control, and Enter and the spreadsheets/workbooks will link, likely leaving you with your result or a 0.0 until data is updated.

Jfwhyte8
  • 1
  • 4