Help with copying a cell to a cell reference created by a formula

[Philrose81]

Good evening,

I have run an INDEX and MATCH formula to give me a resultant cell reference number. I now want to copy a cell to this specific cell reference using another formula.

For example the data I want to copy is in cell A1 and in cell A2 I have the result of the index formula (B1).

I know how to manually do a formula for this but wanting to know if there is a uniform formula that I can use that looks at the formula result of cell A2.

I hope this makes sense. Please ask questions if not.

Thanks

Phil

 

[mikerickson]

Excel formulas cannot put data into a cell. They calculate values and show the result of that calculation in the cell holding the formula.

Excel formulas "get", they don't "put".

If you are VBA conversant, with the layout you described (data in A1, cell address in A2), you could use

Range(Range("A2").Text).Value = Range("A1").Value

to achieve your end.

 

[Philrose81]

Thank you so much Mike. I know a little about VBA but generally record macros and pick up the VBA code from there. This has answered my question. Thanks again.

Phil

 

[Philrose81]

Hi Mike

Further to this can I use VBA to give me a new cell reference value? For example in A2 I have the resultant cell reference value (B1) from my original INDEX formula. I now want to run an IF formula which if True will OFFSET the value of cell A2 to the right giving me a result of C1. I have used OFFSET with cell references but never with the actual value held in a cell.

Thanks in advance

Phil

 

[mikerickson]

Yes you can.
What is the formula and what result do you want from what inputs?

Since your process (putting data into a cell determined by formula) requires the use of VBA, it would be easiest to eliminate the formula portion and code it all in VBA.

 

[Philrose81]

Hi Mike, apologies for the delayed reply. I have coded all of my formula into VBA (albeit rather messily). I have attached the spreadsheet which I thought would be easier than trying to explain. If you delete columns Z-AC and run the "main_macro" macro, you will see that I get a cell reference in column AB. What I would like to do now is to write an IF formula. Basically if my start time in column L is between 00:01 - 08:00 then the cell ref in column AB remains the same (I tried using 00:00 but my first formula attempt failed, so I deleted it!. If the start time is between 08:01 - 16:00 then the cell ref offsets to the right (in this case) F14. Lastly if the time in column L is between 16:00 - 23:59 then the cell ref offsets +2 to the right (G14).

From there I can use the VBA code you first provided to copy the information in cell Y to the new offset cell ref in AC on my machine schedule (Shift_Simplifier) worksheet.

Hope all makes sense and thank you again for your continued help.

Ah I just realised I cant attach a spreadsheet! Does the above explain in enough detail?

Phil